Adding a Wildcard Searchto the Combo Box Wizard code.

[gbs01]

I have a Main form with NAME1 & ACCT.

I currently have a LookUp box that allows you to start typing the NAME1 and it Finds the customer.

When you click Enter, that Customer's information displays fully on the Main Form.

This is the code created by the LookUp Wizard:
--------------------------------
Private Sub Combo23_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[NAME1] = '" & Me![Combo23] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Combo23 = "" 'Clears the Look-Up boxes after searching

End Sub
--------------------------------

How do I alter this code to find the Customer:

1) When you only type in a First name
2) or Last Name
3) or Account number etc ?

I need a WildCard search that will display the Customer details on my Form when a User enters anything they remember be it First, Last or Acct number.
(NAME1 is a Text field type; first name <space> last name.)
(ex. John Doe)
(ACCT is a 4 digit Decimal number type) (ex. 3902)

Thanks in advance!
jlig

 

[PHV]

Something like this ?
Private Sub Combo23_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object, strCrit As String
If IsNumeric(Me!Combo23) Then
strCrit = "ACCT=" & Me!Combo23
Else
strCrit = "NAME1 Like [tt]'*"[/tt] & Me!Combo23 & [tt]"*'"[/tt]
End If
Set rs = Me.RecordsetClone
rs.FindFirst strCrit
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Combo23 = "" 'Clears the Look-Up boxes after searching
End Sub

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[gbs01]

Thanks PHV, I tried it but it works same as before.

I think these properties have something to do with the problem:

Combo23 Properties:
-------------------
Row Source Type : Table/Query
Row Source : SELECT tblCust.name1 FROM tblCust ORDER BY tblCust.name1;
Column Count : 1
Bound Column : 1
--------------------

DO you have any other ideas? Because I chose NAME1 when I used the Lookup wizard, I think it still references that info.

Thanks again,
jlig

 

[PHV]

What are the values of the LimitToList and AutoExpand properties of Combo23 ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[gbs01]

LimitToList : No
AutoExpand : Yes