How do you open a specific record from a search?
The search part of my code works. You type in a phone number and it searches through all 4 tables and finds a match. When it finds the match it brings up the right form based on the table it was found in, but it brings up the very first record within that table.
How do I get it to open up the specific record that I was searching for in the right form?
Here is my code:
Private Sub cmdfind_Click()
txtsearch.Text = Format(txtsearch.Text, "(###) ###-####")
Dim dbsClients As Database
Dim rsClients As Recordset
Dim rsSenior As Recordset
Dim rsMFY As Recordset
Dim rsNewborn As Recordset
Dim strPhone As String
Dim intReply As Integer
Dim intCcount, intScount, intMcount, intNcount As Integer
strPhone = "[Phone 1] = " & "'" & txtsearch.Text & "'"
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)
intCcount = rsClients.RecordCount
intScount = rsSenior.RecordCount
intMcount = rsMFY.RecordCount
intNcount = rsNewborn.RecordCount
rsClients.FindFirst strPhone
If rsClients.NoMatch = False Then
rsClients.OpenRecordset
frmclient.Show
Unload frmsearch
End If
If rsClients.NoMatch = True Then
rsSenior.FindFirst strPhone
If rsSenior.NoMatch = False Then
datseniors.Recordset.OpenRecordset
frmsenior.Show
Unload frmsearch
If rsSenior.NoMatch = True Then
rsMFY.FindFirst strPhone
If rsMFY.NoMatch = False Then
datmfy.Recordset.OpenRecordset
frmmfy.Show
Unload frmsearch
If rsMFY.NoMatch = True Then
rsNewborn.FindFirst strPhone
If rsNewborn.NoMatch = False Then
datnewborn.Recordset.OpenRecordset
frmnewborn.Show
Unload frmsearch
If rsNewborn.NoMatch = True Then
intReply = MsgBox("Sorry there was no record with that Phone number.", vbOKOnly, "Client Not Found")
If intReply = vbOK Then Unload frmsearch
frmsearch.Show
End If
End If
End If
End If
End If
End If
End If
End Sub
~Sandy
|
