Finding closest match in string lookup

[rsynan]

I have a main form where user enters the name of a business. The subform ("frm_NewMembersVerifyInvoiceFlag&quot pulls up the record, but only if user types an exact match (upper/lower case, spaces, punctuation, etc.) I would like to make this more flexible by having the user type in the fist few letters of company name [MAIL_NAME] upper or lower case and then have the subform present a filtered list of records that match input string. User could then scroll through short list to locate exact record. Current working code is below. I am fairly new to vba and need some direction. Thanks for helping.

Option Compare Database

Private Sub cmdLookup_Click()
On Error GoTo Err_cmdLookup_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frm_NewMembersVerifyInvoiceFlag"

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

Exit_cmdLookup_Click:
Exit Sub

Err_cmdLookup_Click:
MsgBox Err.Description
Resume Exit_cmdLookup_Click

End Sub

 

[Stickarm]

Try this:

Private Sub cmdLookup_Click()
On Error GoTo Err_cmdLookup_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frm_NewMembersVerifyInvoiceFlag"

stLinkCriteria = "[MAIL_NAME]like" & "'" & Me![Text1] & "*" & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdLookup_Click:
Exit Sub

Err_cmdLookup_Click:
MsgBox Err.Description
Resume Exit_cmdLookup_Click

End Sub

 

[rsynan]

I inserted the & "*" in the strLinkCriteria and it pulled up the subform but with a blank filtered record (1 of 1). I believe it does this when it doesn't find a match.

 

[Stickarm]

did you change the "= " to "like"?

 

[rsynan]

I get this error: Syntax error (missing operator) in query expression '[MAIL_NAME]Like='Florida*".

 

[rsynan]

Sorry! It worked perfectly. I just had a typo. Thank you very much for the great solution. Made my day!!

 

[Stickarm]

These quotes are a pain.....
try it this way

"[MAIL_NAME]like" & "'" & Me![Text1] & "'" & "*"

 

[rsynan]

Yes, I noticed that = sign. I took it out and ba-da-bing...it worked! That was a wonderful solution. Thanks again for helping me with this.