By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,638 Members | 2,204 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,638 IT Pros & Developers. It's quick & easy.

How to create custom search box

100+
P: 184
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.
Mar 20 '07 #1
Share this Question
Share on Google+
7 Replies


Rabbit
Expert Mod 10K+
P: 12,366
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub but_search_Click()
  2.  
  3. Dim db As Database
  4. Dim rst As Recordset
  5.  
  6. Set db = CurrentDb
  7.  
  8. If IsNull(CDCNO) And IsNull(LastName) Then
  9.     MsgBox "Please enter a search criteria", vbOKOnly
  10.     CDCNO.SetFocus
  11.     Exit Sub
  12. End If
  13.  
  14. 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] & "*'))")
  15.  
  16. Select Case rst.RecordCount
  17.     Case Is > 0
  18.         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"
  19.     Case Else
  20.         Results.RowSource = ""
  21.         MsgBox "Zero records found.", vbOKOnly
  22. End Select
  23.  
  24. Refresh
  25. db.Close
  26. End Sub
And this runs when they double click a record:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Results_DblClick(Cancel As Integer)
  2.   Dim SearchString As String
  3.   SearchString = "CDCNO = '" & Results & "'" & " AND " & "EnrollDate = #" & Results.Column(3) & "#"
  4.   DoCmd.OpenForm "frm_Demo", , , SearchString
  5. End Sub
Mar 20 '07 #2

100+
P: 184
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
Mar 21 '07 #3

Rabbit
Expert Mod 10K+
P: 12,366
Not a problem, good luck.
Mar 21 '07 #4

100+
P: 184
Howzit Rabbit

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

Thanks
Mar 22 '07 #5

Rabbit
Expert Mod 10K+
P: 12,366
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.
Mar 23 '07 #6

100+
P: 184
Excellent .Thanks for that reply, that's cleared that one up for me!
Mar 23 '07 #7

Rabbit
Expert Mod 10K+
P: 12,366
Excellent .Thanks for that reply, that's cleared that one up for me!
Not a problem, let us know if you get stuck anywhere.
Mar 23 '07 #8

Post your reply

Sign in to post your reply or Sign up for a free account.