How to create custom search box

[bloukopkoggelmander]

Hi all

Does anyone know how to create a search box on a form where you can for instance enter a name or something like a vehicle registration which then queries the database and if found and selected, autofills a form with all relevant data relating to the selection you made?

I have searched The Scripts and also googled this , but cannot seem to find anything.

Hopes this makes sense.

Thanks in advance for any help on this.

[Rabbit]

Here's what I use. I have a form with 2 unbound textboxes, a search button, and a listbox that lists the results.

This runs when they click the search button:

Private Sub but_search_Click()
 
Dim db As Database
Dim rst As Recordset
 
Set db = CurrentDb
 
If IsNull(CDCNO) And IsNull(LastName) Then
    MsgBox "Please enter a search criteria", vbOKOnly
    CDCNO.SetFocus
    Exit Sub
End If
 
Set rst = db.OpenRecordset("SELECT CDCNO, LastName, FirstName, EnrollDate, ParoleDate FROM tbl_Demo WHERE (((LastName) Like '*" & [Forms]![frm_Find]![LastName] & "*')) AND (((CDCNO) Like '*" & [Forms]![frm_Find]![CDCNO] & "*'))")
 
Select Case rst.RecordCount
    Case Is > 0
        Results.RowSource = "SELECT CDCNO AS [CDCR #], LastName AS [Last Name], FirstName AS [First Name], EnrollDate AS [Enrollment Date], ParoleDate AS [Parole Date] FROM tbl_Demo WHERE (((CDCNO) Like '*" & [Forms]![frm_Find]![CDCNO] & "*')) and (((LastName) Like '*" & [Forms]![frm_Find]![LastName] & "*')) ORDER BY LastName, FirstName, EnrollDate"
    Case Else
        Results.RowSource = ""
        MsgBox "Zero records found.", vbOKOnly
End Select
 
Refresh
db.Close
End Sub

And this runs when they double click a record:

Private Sub Results_DblClick(Cancel As Integer)
  Dim SearchString As String
  SearchString = "CDCNO = '" & Results & "'" & " AND " & "EnrollDate = #" & Results.Column(3) & "#"
  DoCmd.OpenForm "frm_Demo", , , SearchString
End Sub

[bloukopkoggelmander]

Thanks for that buddy.

I will have a look through the code and see if I can apply it to my database. I will get back if I am struggeling.

Thanks

[Rabbit]

Not a problem, good luck.

[bloukopkoggelmander]

Howzit Rabbit

Just one question. Maybe I am just being blind and stupid today, but what does CDCNO in your code refer to?

Thanks

[Rabbit]

CDCNO and EnrollDate is how our department uniquely identifies participants. CDCNO is a unique identifier for an individual. SELECT CDCNO is selecting the field called CDCNO from the table tbl_Demo. Me.CDCNO refers to a control on my form that they are typing into to search for part or whole of a CDCNO.

[bloukopkoggelmander]

Excellent .Thanks for that reply, that's cleared that one up for me!

[Rabbit]

Excellent .Thanks for that reply, that's cleared that one up for me!

Not a problem, let us know if you get stuck anywhere.