VB6/Access query - criteria by user input

[Ruled_by_Saturn]

hiya...

i'm new to vb6, and i'm creating my first real application and I'm beginning 2 think i'm going mad bcoz of this...

I'm creating a 'simple' app in vb6 that requires a connection to Access. I'm using Data Controls for this (ADO). So far so good, except I can't create a search in vb6 that allows the end-user of the app to type in the specific criteria(on) he wants to search for.

E.g. there's a table with a list of students and their details (tblStudentDetails) and another one with all the marks they receive for specific assignments (tblMarks). The search that the user wants to carry out requires him to input the name of the specific assignment, and the date it was given, from the table tblATDetails. The app should then be able to find all the information related to this specified criteria, from the appropriate tables in Access, and display it in a screen object in vb6.

How do I do this?

Any help on this will be MASSIVELY appreciated...

Thanx in advance...
Alice

[Ahara]

Hello and don't go MAD!! (I've been there)...If you really want to use the data controls, from the example you gave, you would probably have to bind a control to each table, and then create a procedure that cycles through each one grabbing the records you need (very messy)...The best way would be to use SQL in tandem with ADO programmatically. I'm not really up on tutorials...maybe someone else out there can recommend some.

Just my opinion, but I really don't like those data controls. They have limited use compared to what you can do programmatically, and as far as a navigation control goes, they're really only useful for small recordsets. Cheers,

DA

[JeffB]

Ok, let's assume you want an ADO DataControl, take a look at the following code, I've tried to comment it a bit. To make it works, create a new project, add a ADO DataControl and set it's connection to a database. Next, add two command button and a textbox. I used a sample table COUNTRY (COUNTRY_ID and COUNTRY_NAME). The first button change the recordsource of the Datacontrol and update the textbox. The second one do the same thing but ask for the user input. It's really simple. Note that the second button ask for the TABLE name, FILE name and VALUE, so you can use it with ANY table and ANY database

'This button only work with a sample table named COUNTRY containing
'two fields: COUNTRY_ID and COUNTRY_NAME
Private Sub Command1_Click()
    With Me.Adodc1
        'Let's change the recordsource of the ADO DataControl
        .CommandType = adCmdText
        .RecordSource = "SELECT * FROM COUNTRY"
        .Refresh
         
        'We'll use a filter
        .Recordset.Filter = "COUNTRY_ID <= 2"

         
        'Set the DateSource and DataField of a textbox
        Set Me.Text1.DataSource = Adodc1
        Me.Text1.DataField = "COUNTRY_NAME"
    End With

End Sub

Private Sub Command2_Click()
    Dim strField As String     'The field to use
    Dim strValue As String     'The value to search (note: string value only)
    Dim strFilter As String    'The filter (criteria)
    Dim strTable As String     'The table to use
     
    With Me.Adodc1
        'Now, we'll use the user input
        strTable = InputBox("Please enter a valid table name:", , "COUNTRY")
         
        'Let's change the recordsource of the ADO DataControl
        'to the selected table
        .CommandType = adCmdTable
        .RecordSource = strTable
        .Refresh
         
        'We'll use a filter (and the user input)
        strField = InputBox("Please enter a FIELD name:", , "COUNTRY_NAME")
         
        'Now ask for the value, note that in this example, only
        'a STRING value (and a TEXT field) will work
        strValue = InputBox("Please enter the searched value:", , "USA")
         
        'Build the filter, a string "C*" will return each
        'field starting by "C"
        strFilter = strField & " LIKE '" & strValue & "'"
         
        'This will update the recordset with the new filter
        .Recordset.Filter = strFilter
         
        'Set the DateSource and DataField of a textbox
        Set Me.Text1.DataSource = Adodc1
        Me.Text1.DataField = strField

    End With
End Sub

JeffB