Search Form

[simonjackson79]

Hello again!!! I know I'm being a real pain today, but I'm sure this is the last thing I need to know.

I have setup a form with the fields [Unique ID], [Surname], [First Name] and [Date Of Birth]. What I need to know is what code to add to a button so that it searches on the data entered and opens the client form at the relevant record. ie. Sometimes the user won't know the Unique ID, and will maybe type in the just the Date of Birth and the Surname. How do I setup a search subroutine that will ignore null fields and just search on the fields with data in and open the form at the correct record?

Many thanks in advance!

Si

 

[hymn]

have a look at the threads below
Thread702-534577
Thread702-563930

Hope this helps
Hymn

 

[gusbrunston]

[tt]
Hi Si: (Now that has a ring to it!)

On several forms I have put multiple combo boxes to search for and display records by different fields:

Select by:
______ _________ ___________ _____
| ID | | Surname | | FirstName | | DOB |

Each combo box will show a list of records to select from.

I was pretty new when I did this, so I let the Wizard build the combo boxes for me.

If you want to search on more than one field, you could synchronize your combo boxes: first, show all surnames "McLaughlin", the next box will list the DOB for all the McLaughins, etc.

To synchronize combo boxes, there is a Microsoft demo database if you want it. For Access 2000, it's "FrmSmp00". There other versions for the different versions of Access.

HTH,[/tt]

[tt]Gus Brunston - Access2000(DAO)
Skill level based on 1-10: 7
Webmaster: www.rentdex.com[/tt]

 

[Rolliee]

Si,

This code searches two tables but the same idea could be used under multiple command buttons to achieve what you desire. To skip null fields, do the following.

if isnull( recObject![MyField]) then do something

Private Sub cmdSearch_Click()
Dim rt As DAO.Recordset, rm As DAO.Recordset, TOkay As Boolean, MOkay As Boolean

Set rt = CurrentDb.OpenRecordset("Table1&quot
Set rm = CurrentDb.OpenRecordset("Table2&quot
If rm.EOF And rm.BOF Then
MsgBox "There are no records in the Member Table - Table2"
Exit Sub
End If
If rt.EOF And rt.BOF Then
MsgBox "There are no records in the Team Table - Table1"
Exit Sub
End If

If IsNull(Me.Text0) Then Exit Sub

MOkay = TOkay = False
rt.MoveLast
rt.MoveFirst
Do While Not rt.EOF
If Me.Text0 = rt![TName] Then
TOkay = True
Exit Do
End If
rt.MoveNext
Loop
rm.MoveLast
rm.MoveFirst
Do While Not rm.EOF
If Me.Text0 = rm("MName&quot Then
MOkay = True
Exit Do
End If
rm.MoveNext
Loop

If TOkay Then
MsgBox "The search value is in the team table - Table1"
' Other stuff - even add records to rt
End If
If MOkay Then
MsgBox "The search value is in the Member table - table2"
' Other stuff - even add records to rt
End If

rt.Close
rm.Close
Set rt = Nothing
Set rm = Nothing


End Sub

R