Searching for a record

[meinu76]

I wish to create a form which will enable someone to type in a name, search for that name in the database and display that record on another form. So far I've been able to use the 'form operation' command button, but unless what's been type in is the exact match, it won't bring up the record. Is there a way to somehow modify this code so that I will be able to bring up a record based on a partial match?

Private Sub Search_Click()
On Error GoTo Err_Search_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Client"

stLinkCriteria = "[AccName]= " & "'" & Me![AccName] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Search_Click:
Exit Sub

Err_Search_Click:
MsgBox Err.Description
Resume Exit_Search_Click

End Sub

Any help would be appreciated.

 

[JoeMiller]

Try this, using the Like operator in the where clause instead of using the filter clause:

Private Sub Search_Click()
On Error GoTo Err_Search_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Client"
stDocWhere = "[AccName] Like " & "'" & Me![AccName] & "'"

DoCmd.OpenForm stDocName, , , , stDocWhere

Exit_Search_Click:
Exit Sub

Err_Search_Click:
MsgBox Err.Description
Resume Exit_Search_Click

End Sub
Joe Miller
joe.miller@flotech.net

 

[JoeMiller]

Try this, using the Like operator in the where clause instead of using the filter clause:

Private Sub Search_Click()
On Error GoTo Err_Search_Click

Dim stDocName As String
Dim stDocWhere As String

stDocName = "Client"
stDocWhere = "[AccName] Like " & "'" & Me![AccName] & "'"

DoCmd.OpenForm stDocName, , , , stDocWhere

Exit_Search_Click:
Exit Sub

Err_Search_Click:
MsgBox Err.Description
Resume Exit_Search_Click

End Sub
Joe Miller
joe.miller@flotech.net

 

[meinu76]

Thanks Joe for your response. Unfortunately that gave me a type mismatch error. Any other thoughts? Much appreciated.

 

[JoeMiller]

The field AccName is giving you a number instead of a string. You need to remove the extra quotes or change your criteria to get a string.

Joe Miller
joe.miller@flotech.net