How do I search a database with 4 tables from VB 6

Szimmer9 · March 22nd, 2006, 12:08 PM

I have been trying to figure this out for awhile now and cannot get it to work. I need to be able to enter in a phone number and click find and then have the find code search for that phone number through all four tables in the database. If it finds it in table 2 then it needs to open that file in the proper form. I have forms specific to all 4 tables.

Can anyone help me please?

~Sandy

gbianchi · March 22nd, 2006, 02:34 PM

hi there.. can you post some code or be more specific?? do you have code problems or just how to do it??

HTH

Gonzalo

Szimmer9 · March 22nd, 2006, 02:57 PM

I am wanting to know how to do it. I have had a hard time researching how to go about it, so I don't even really know where to begin. Here is the last code I tried, but it didn't work.

Private Sub cmdfind_Click()
Dim dbsClients As Database
Dim rsClients As Recordset
Dim rsSenior As Recordset
Dim rsMFY As Recordset
Dim rsNewborn As Recordset
Dim strNewSearch As String
Dim strPhone As String
Dim intReply As Integer
    strNewSearch = txtsearch.Text
    strPhone = "Phone 1 = " & "'" & strNewSearch & "'"
    Set dbsClients = OpenDatabase("ClientInfoList97.mdb")
    Set rsClients = dbsClients.OpenRecordset("Client Info", dbOpenDynaset)
    Set rsSenior = dbsClients.OpenRecordset("Seniors", dbOpenDynaset)
    Set rsMFY = dbsClients.OpenRecordset("My First Year", dbOpenDynaset)
    Set rsNewborn = dbsClients.OpenRecordset("Newborn Memories", dbOpenDynaset)

     rsClients.FindFirst strNewSearch
       If rsClients.NoMatch = False Then frmclient.Show
       If rsClients.NoMatch = True Then
         rsSenior.FindFirst strNewSearch
           If rsSenior.NoMatch = False Then frmsenior.Show
           If rsSenior.NoMatch = True Then
               rsMFY.FindFirst strNewSearch
                If rsMFY.NoMatch = False Then frmmfy.Show
                If rsMFY.NoMatch = True Then
                     rsNewborn.FindFirst strNewSearch
                    If rsNewborn.NoMatch = False Then frmnewborn.Show
                        If rsNewborn.NoMatch = True Then
intReply = MsgBox("Sorry there was no record with that Phone number.", vbOKCancel, "Client Not Found")
                            If intReply = vbOK Then
                            frmsearch.Refresh
                            Else: intReply = vbCancel
                            End If
                        End If
End If
   End If
    End If

End Sub

~Sandy

gbianchi · March 23rd, 2006, 10:16 AM

ok.. let's see your code...

some notes: always comment it.. maybe you will find the error doing it...

Code:

Private Sub cmdfind_Click()
    Dim dbsClients As Database
    Dim rsClients As Recordset
    Dim rsSenior As Recordset
    Dim rsMFY As Recordset
    Dim rsNewborn As Recordset
    Dim strNewSearch As String
    Dim strPhone As String
    Dim intReply As Integer
    'you have your search string in strNewSearch 
    strNewSearch = txtsearch.Text
    'another search string??, but this one looks better  something like  phone 1 = 'phonenumber'
    but if phone 1 is a field is bad formated!!! should be between [] and never use field names with spaces!
    strPhone = "Phone 1 = " & "'" & strNewSearch & "'"
    Set dbsClients = OpenDatabase("ClientInfoList97.mdb")
    Set rsClients = dbsClients.OpenRecordset("Client Info", dbOpenDynaset)
    Set rsSenior = dbsClients.OpenRecordset("Seniors", dbOpenDynaset)
    Set rsMFY = dbsClients.OpenRecordset("My First Year", dbOpenDynaset)
    Set rsNewborn = dbsClients.OpenRecordset("Newborn Memories", dbOpenDynaset)
    'but strnewsearch is not a well formated search string!
    rsClients.FindFirst strNewSearch
    'always put the if in a good format (not a single line)
    If rsClients.NoMatch = False Then frmclient.Show
    'upper line should be
    'If rsClients.NoMatch = False Then 
        'frmclient.Show (no modal??)
    'end if
    'the below code will never execute!! the string search looks wrong
    If rsClients.NoMatch = True Then
        rsSenior.FindFirst strNewSearch
        If rsSenior.NoMatch = False Then frmsenior.Show
        If rsSenior.NoMatch = True Then
            rsMFY.FindFirst strNewSearch
            If rsMFY.NoMatch = False Then frmmfy.Show
            If rsMFY.NoMatch = True Then
            'above line could be If rsMFY.NoMatch Then b/c it faster to execute
                rsNewborn.FindFirst strNewSearch
                If rsNewborn.NoMatch = False Then frmnewborn.Show
                If rsNewborn.NoMatch = True Then
                    intReply = MsgBox("Sorry there was no record with that Phone number.", vbOKCancel, "Client Not Found")
                    If intReply = vbOK Then
                        frmsearch.Refresh
                    Else: intReply = vbCancel
                    End If
                End If
            End If
        End If
    End If 
End Sub

HTH

Gonzalo

Szimmer9 · March 23rd, 2006, 10:39 AM

Thanks for the help, but I still don't know how to make it work. I am fairly new at VB with databases. I found this code online, but I can't get it to apply to what I need it to do.

Does anyone have an idea of HOW to get my find button to search through 4 tables in one database for the phone number and then produce the results in a specific form?

~Sandy

gbianchi · March 23rd, 2006, 10:48 AM

mmm.. i point to you that you are using the wrong stringsearch!

what happend if you use the ritgh one???

HTH

Gonzalo

Szimmer9 · March 23rd, 2006, 10:56 AM

I see what you meant now. It works! Thank you so much!

There is one problem. It finds the record in a certain table and brings up the proper form, but it doesn't bring up the right record. Instead it brings up the right form with the first record in it and you have to click next until you come to the record you were searching for.

Any ideas?

also, if it doesn't find any match at all, my pop up window comes up and says there is no match. Instead of it opening up the search window again, it closes all windows.

~Sandy