Solved: Is it possible to create a search cmd button that retrieves records using vba?

[wedd]

Hi experts, I am developing a database that when users want to search for dates of a booking scheduled or unscheduled,times of an appointment or records of a customer's history based on a ID number of a customer; they just need to enter the number in a text box and click on a button that searches through all the records in the forms and tables...all the possible outcomes based on the date entered or customer name or customerID number entered. Can this be done? If so what will I need to do? Would you know of any logical code that can perform this challenging task? If you have or know of any code through websites or possible programs you've created to do this would be very helpful...I am an intermediate user of vba so it seems a bit complex for me...I created a program to search for records on a form( including new records)based on customer ID numbers...but it only displays a messagebox of the customerID number rather than searching for the actual record and displaying it. (I've listed this code below - maybe you can adjust, adapt or taylor the code to meet my requirements). If you know of a website that has similar samples on how to do this that would also be very useful...

I am creating it in this method rather than using the drop down list because it will be more user friendly for staff at my workplace to use...as they receive a lot of data and need a way to keep track of bookings that have been done...for future dates and years...as well as forthcoming dates...so this way will make it more efficient and quicker to search for a customers record rather than navigating down a long drop down list...

Thanks for your contributions:friends: ...this will definitely enhance my learning of vba for access.


The code I've used to do this: but it only displays a messagebox of the customer's ID number; rather than searching and displaying a historical record, dates of a particular booking; dates when a booking is available or not based on when the user enters a date in the text box...


Private Sub btnSearch_Click()
On Error GoTo Err_btnSearch_Click
If IsNull(Me.TxtSearchValue) Or Len(Me.TxtSearchValue) = 0 Then
MsgBox "You must enter a CustomerID number to Search for in the Blank Box to the right"
Me.TxtSearchValue.SetFocus
Exit Sub
End If
If Left(Me.TxtSearchValue, 1) Like "[0-9]" Then
'searching for number
MsgBox "Use code to search for Number " & vbCrLf _
& "select * from tblCustomers where [Customer ID] =" & CLng(Me.TxtSearchValue)
Else
'searching for Name
MsgBox "Use code to search for Customer Name " & vbCrLf _
& "select * from tblCustomers where [Customer Name] = '" & Me.TxtSearchValue & "'"
End If
Exit_btnSearch_Click:
Exit Sub
Err_btnSearch_Click:
MsgBox Err.Description
Resume Exit_btnSearch_Click
End Sub

[Movian]

I have a database with a similar feature, but it allows a user to search on an ID number, firstname or lastname.

The way i have this setup is that i have a form popup when they click search, with a list box displaying all records in the system. Then i have a radio selecting which item they are searching on and a text box for their search criteria. This will then alter the SQL statement on the listbox to filter the results and allow them to double click on the particular record that they want.

I'm not sure if this setup will be helpful for you as you mentioned that you are searching on dates and perhaps you will get multiple dates back. This solution will allow your user to then easily select which of the multiple returned records it is that they wish to alter. This is sort of a halfway between your mentioned drop down option and your hard search 1 record options. Kind of gives you the best of both worlds. You can even search on dates between to dates "SELECT * FROM table WHERE date field between 'date1' and 'date2'" for example. If you wish to consider this solution then let me know.

[wedd]

Hi Movian, thanks for your response. That is exactly what I require...do you have a suggestion how I can either fix my code? Or maybe do you have a sample I can look at just to get an idea if it's a similar idea I am looking to do with my database...I would like the users to search for records either based on someone's first name or surname or do a search based on dates...I tried it with a customer number but it was a bit difficult to retrieve the data...

[Movian]

Here is some the code from my afterupdate event on the textbox the user enters their search criteria in.

Patlist is a list box displaying the resulsts. PLEASE NOTE i did not have time to modify any of these to gear it towards what your working on, so you will need to do that part. This is just an example of how it works in our system. you select the radio to either medical id, firstname or last name. Enter your text and then the list updates. You double click the record you want, then using the ID number that record is located on the main form. you of course will need to modify it to work with dates using the SQL syntax i mentioned previously.


Dim SqlText As String, Temp As String
If IsNull(Me.FilterText) Or Me.FilterText = "" Then
Temp = "%"
Else
Temp = Me.FilterText
End If
SqlText = "SELECT [MedicalID#], LastName, Firstname, Birthdate FROM tblPatient"
Select Case Me.SearchOption
Case 1
SqlText = SqlText & " WHERE [MedicalID#] LIKE '%" & Temp & "%'"
Case 2
SqlText = SqlText & " WHERE Firstname LIKE '%" & Temp & "%'"
Case 3
SqlText = SqlText & " WHERE Lastname LIKE '%" & Temp & "%'"
End Select
SqlText = SqlText & " ORDER BY " & SortBy & " " & ascdec & ";"
Me.PatList.RowSource = SqlText
Me.PatList.Requery
End Sub
I then have a double click event on the list box for the selection to locate them on our "Main form"

If Not IsNull(Me.PatList.Value) Then
searchstring = Me.PatList.Value
DoCmd.Close acForm, "Search"
Forms("frmMain").MedicalID.SetFocus
DoCmd.FindRecord searchstring, acEntire, , acSearchAll, , acCurrent
End If
I hope these two examples will get your started :)

[wedd]

Hi Movian, ideally I wanted the user to either type in a customer name or customerID or even a date in a textbox and then click on a command button that would either display the table or form of where that particular record is. I had initaily started with a listbox, but the users would prefer to type in the details in a textbox and click on a button to search for the records to display...not to worry...thanks! I've tried various methods including an input box but like my code would only display a message box and not the actual records...the only other way to do this is to create a macro based button to display a query which I would personally not prefer to do...

[wedd]

Hi, Movian! I managed to find code that does exactly what I want. The user enters a customerID in a textbox and clicks enter and record is displayed as seen below: the only other thing to add to the code is the ability to search for dates and times between different periods, customer first name and surname...

Private Sub Text313_AfterUpdate()
If (txtGoTo & vbNullString) = vbNullString Then Exit Sub
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
rs.FindFirst "[CustomerID]=" & txt313
If rs.NoMatch Then
MsgBox "Sorry, no such record exists '" & txtGoTo & "' was found.", _
vbOKOnly + vbInformation
Else
Me.Recordset.Bookmark = rs.Bookmark
End If
rs.Close
txtGoTo = Null
End Sub