Solved: Is it possible to create a search button as an inputbox to search for records based o

[wedd]

Hi vba experts! I would like to create a search button (Input box)that searches for existing customer records using vb in access 2010. For example when the user enters in either a customer record number or customer first name or surname their customer details of the booking will be displayed on the form. Is this possible, if so how can this be done? I am a beginner using vba, so would you have an example code how to do this or know of any websites that could assist me in this interesting challenge.


Thanks for your contributions :friends:

[orange]

Here is some code related to a button (I have acc2003) click event on a form.
Also on the form is a textbox. The purpose of this code is to search for a Number or a Name in an Employees table.




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 Value to Search for in the textBox"
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 tblEmployees where [employee number] =" & CLng(Me.txtSearchValue)
Else
'searching for Name
MsgBox "Use code to search for Surname " & vbCrLf _
& "select * from tblEmployees where [name] = '" & Me.txtSearchValue & "'"
End If

Hope it's useful to you.

[wedd]

Thanks, Orange! I'll test it and let you know if it works fine in my version of acc2010

[wedd]

Hi Orange, I received this error message:

Compile error: label not defined. Do you know what this error message means?

[orange]

Sorry about that, I didn't copy all of the procedure. Here it is.


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 Value to Search for in the textBox"
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 tblEmployees where [employee number] =" & CLng(Me.txtSearchValue)
Else
'searching for Name
MsgBox "Use code to search for Surname " & vbCrLf _
& "select * from tblEmployees where [name] = '" & Me.txtSearchValue & "'"
End If

Exit_btnSearch_Click:
Exit Sub

Err_btnSearch_Click:
MsgBox Err.Description
Resume Exit_btnSearch_Click

End Sub

[wedd]

Thanks Orange, that's great! So based on your procedure...it should be placed on a command button on click event. Should the procedure also be placed on the textbox event? If so, would it be placed in the on focus event? Also does the user enter the employee name or employee number in the textbox, and then clicks the command button to search for the employee's records? Thanks for the help!

[wedd]

Great, it works! Thanks Orange! It works well...I've learn't something new.