ComboBox Search with Like

[Amanda Byrne]

I have a ComboBox search in my form where you type in the Last Name and the
comboBox generates a list based on what the user types. I'd like the search
to return a list of all the items that contain the text entered, so that the
user can search by last name or by first name, and the list presented would
be ordered by last name, but don't know if this can be controlled by the SQL
statement or the AfterUpdate code or how to change the code.

(Househole table holds address, Name table holds indivdual names)

Row Source SQL statement:
SELECT Name.[Household ID], Name.[Last Name], Name.[First Name]
FROM Household INNER JOIN Name ON Household.[Household ID] = Name.[Household
ID]
ORDER BY Name.[Last Name], Name.[First Name];


AfterUpdate Code:
Private Sub ComboName_AfterUpdate()
If Not IsNull(ComboName) Then
Me.RecordsetClone.FindFirst "[Household ID] = " & Me.ComboName
Me.Bookmark = Me.RecordsetClone.Bookmark
Me.ComboName = Null
Form.Refresh
End If
End Sub

I saw something in the forum about using stLinkCriteria, but not sure how
this fits in or works (most coding experience is from VBScript and ASPs)

Thanks! Amanda

 

[Ofer Cohen]

Where do you write the text to search for? Do you have another text box where
the user type a name and the combo RowSource will be filtered by?

If that the case, you can add a filter to the RowSource of the combo:

SELECT Name.[Household ID], Name.[Last Name], Name.[First Name]
FROM Household INNER JOIN Name ON Household.[Household ID] = Name.[Household
ID]
WHERE
Name.[Last Name] Like "*" & Forms![FormName]![TextBoxName] & "*" Or
Name.[First Name] Like "*" & Forms![FormName]![TextBoxName] & "*"
ORDER BY Name.[Last Name], Name.[First Name

On the AfterUpdate event of the combo box, you'll need to refresh the combo
Me.[ComboName].Requery

===============
Please consider changing the table name from [Name] to another name which is
not a key word in Access.

 

[Amanda Byrne]

My RowSource for ComboName now reads:
SELECT Name.[Household ID], Name.[Last Name], Name.[First Name]
FROM Household INNER JOIN Name ON Household.[Household ID] = Name.[Household
ID]
WHERE (((Name.[Last Name]) Like "*" & [Forms]![Household Account
Entry]![ComboName] & "*")) OR (((Name.[First Name]) Like "*" &
[Forms]![Household Account Entry]![ComboName] & "*"))
ORDER BY Name.[Last Name], Name.[First Name];

and my code for ComboName AfterUpdate reads:
Private Sub ComboName_AfterUpdate()
If Not IsNull(ComboName) Then
Me.RecordsetClone.FindFirst "[Household ID] = " & Me.ComboName
Me.Bookmark = Me.RecordsetClone.Bookmark
Me.ComboName = Null
Form.Refresh
Me.[ComboName].Requery
End If
End Sub

The form is behaving exactly as before. I'm not sure what you mean by
having another textbox, there is just the one combo where the user starts
typing in the text, and this filters what shows up when the dropdown is
clicked on.

Thank you for the tip on the table name. If I rename the table, will the
form adjust automatically?

Where do you write the text to search for? Do you have another text box where
the user type a name and the combo RowSource will be filtered by?

If that the case, you can add a filter to the RowSource of the combo:

SELECT Name.[Household ID], Name.[Last Name], Name.[First Name]
FROM Household INNER JOIN Name ON Household.[Household ID] = Name.[Household
ID]
WHERE
Name.[Last Name] Like "*" & Forms![FormName]![TextBoxName] & "*" Or
Name.[First Name] Like "*" & Forms![FormName]![TextBoxName] & "*"
ORDER BY Name.[Last Name], Name.[First Name

On the AfterUpdate event of the combo box, you'll need to refresh the combo
Me.[ComboName].Requery

===============
Please consider changing the table name from [Name] to another name which is
not a key word in Access.

--
Good Luck
BS"D

I have a ComboBox search in my form where you type in the Last Name and the
comboBox generates a list based on what the user types. I'd like the search
to return a list of all the items that contain the text entered, so that the
user can search by last name or by first name, and the list presented would
be ordered by last name, but don't know if this can be controlled by the SQL
statement or the AfterUpdate code or how to change the code.

(Househole table holds address, Name table holds indivdual names)

Row Source SQL statement:
SELECT Name.[Household ID], Name.[Last Name], Name.[First Name]
FROM Household INNER JOIN Name ON Household.[Household ID] = Name.[Household
ID]
ORDER BY Name.[Last Name], Name.[First Name];


AfterUpdate Code:
Private Sub ComboName_AfterUpdate()
If Not IsNull(ComboName) Then
Me.RecordsetClone.FindFirst "[Household ID] = " & Me.ComboName
Me.Bookmark = Me.RecordsetClone.Bookmark
Me.ComboName = Null
Form.Refresh
End If
End Sub

I saw something in the forum about using stLinkCriteria, but not sure how
this fits in or works (most coding experience is from VBScript and ASPs)

Thanks! Amanda