List Box search

  • Thread starter Thread starter Terrence Carroll
  • Start date Start date
T

Terrence Carroll

I am looking to do a little bit more of a sophisticated look up. I have a
form with a list box (listCOFAMNAME) a text box (txtCOFAMNAME) and a command
button (cmdSEARCH). What I want to do is have the list box populate with the
Company family name and company name when the user clicks on the command
button after entering a text string into the text box. Ideally the query
would do a like "*" & CompanyFamilyName & like "*" search so that even if
there is a partial match, it would show up in the list box. I tried use the
list box wizard and modify the sql statement but it is not working. Listed
below is the SQL statement. I would like to use VBA by linking the query
action to the search button object but I am not sure how.

umulativeCOFAMNAMECONAME].[CompanyFamilyName],
[CumulativeCOFAMNAMECONAME].[Company Name] FROM CumulativeCOFAMNAMECONAME
WHERE [CumulativeCOFAMNAMECONAME].[CompanyFamilyName] Like "*" &
DATAENTRYFORM.txtCOFAMNAME ORDER BY [CompanyFamilyName], [Company Name];
 
Terrence Carroll said:
I am looking to do a little bit more of a sophisticated look up. I have a
form with a list box (listCOFAMNAME) a text box (txtCOFAMNAME) and a
command
button (cmdSEARCH). What I want to do is have the list box populate with
the
Company family name and company name when the user clicks on the command
button after entering a text string into the text box. Ideally the query
would do a like "*" & CompanyFamilyName & like "*" search so that even if
there is a partial match, it would show up in the list box. I tried use
the
list box wizard and modify the sql statement but it is not working.
Listed
below is the SQL statement.
Click to expand...

Well, part of it.
I would like to use VBA by linking the query
action to the search button object but I am not sure how.

umulativeCOFAMNAMECONAME].[CompanyFamilyName],
[CumulativeCOFAMNAMECONAME].[Company Name] FROM CumulativeCOFAMNAMECONAME
WHERE [CumulativeCOFAMNAMECONAME].[CompanyFamilyName] Like "*" &
DATAENTRYFORM.txtCOFAMNAME ORDER BY [CompanyFamilyName], [Company Name];
Click to expand...

You need to include the Forms collection as part of your reference, and use
the bang (!) operator. Try this for the WHERE clause of your list box's
RowSource query:

WHERE [CumulativeCOFAMNAMECONAME].[CompanyFamilyName]
Like "*" & [Forms]![DATAENTRYFORM]![txtCOFAMNAME] & "*"
ORDER BY [CompanyFamilyName], [Company Name]


Then, in the command button's Click event, requery the list box. In VBA,
that would be:

Me.listCOFAMNAME.Requery

Or you could forget the command button altogether, and requery the list box
in the AfterUpdate event of txtCOFAMNAME.
 
When I make the changes, on opening up the form I get all records in the
table in the list box. When I attempt to enter text into the text filed and
requery I still get all the records from the CumulativeCompanyFamilyName
table. I tried the requery code in the command button's on click event and
 
Terence,

You could do this without using a button, as the user enters text into
the Text box the List box will just filter the results to show the
matches. If you want to try this do the following :-

In the Text box's On Change event, enter a list box requery, something
like :-

Private Sub txtCOFAMNAME_Change()
Me.listCOFAMNAME.Requery
End Sub

In the query that is bound to the List box add this in the Criteria
row for the CompanyFamilyName field :-

Like "*" & [Forms]![DATAENTRYFORM]![txtCOFAMNAME].[Text] & "*"

Actually you could probably omit the Forms reference. For example :-

Like "*" & [txtCOFAMNAME].[Text] & "*"

would probably work OK but it is usually safer to leave it there.

Note that you need to compare with the field's .Text property rather
than the .Value property because this property holds the current text
in the field when the Change event is triggered.

What should happen is when the form is opened, all the names will be
displayed. As you enter text into the txtCOFAMNAME field, the list of
names will be filtered to show only those that match the text.

HTH

Peter Hibbs.




When I make the changes, on opening up the form I get all records in the
table in the list box. When I attempt to enter text into the text filed and
requery I still get all the records from the CumulativeCompanyFamilyName
table. I tried the requery code in the command button's on click event and
I am looking to do a little bit more of a sophisticated look up. I have a
form with a list box (listCOFAMNAME) a text box (txtCOFAMNAME) and a command
button (cmdSEARCH). What I want to do is have the list box populate with the
Company family name and company name when the user clicks on the command
button after entering a text string into the text box. Ideally the query
would do a like "*" & CompanyFamilyName & like "*" search so that even if
there is a partial match, it would show up in the list box. I tried use the
list box wizard and modify the sql statement but it is not working. Listed
below is the SQL statement. I would like to use VBA by linking the query
action to the search button object but I am not sure how.

umulativeCOFAMNAMECONAME].[CompanyFamilyName],
[CumulativeCOFAMNAMECONAME].[Company Name] FROM CumulativeCOFAMNAMECONAME
WHERE [CumulativeCOFAMNAMECONAME].[CompanyFamilyName] Like "*" &
DATAENTRYFORM.txtCOFAMNAME ORDER BY [CompanyFamilyName], [Company Name];
Click to expand...
Click to expand...
 
I deleted the textbox control from the form and added a new textbox which I
renamed the same as the old one. Now everything works just great. Thank
you very much, Dirk, Ken, and Peter for your assistance.

Peter Hibbs said:
Terence,

You could do this without using a button, as the user enters text into
the Text box the List box will just filter the results to show the
matches. If you want to try this do the following :-

In the Text box's On Change event, enter a list box requery, something
like :-

Private Sub txtCOFAMNAME_Change()
Me.listCOFAMNAME.Requery
End Sub

In the query that is bound to the List box add this in the Criteria
row for the CompanyFamilyName field :-

Like "*" & [Forms]![DATAENTRYFORM]![txtCOFAMNAME].[Text] & "*"

Actually you could probably omit the Forms reference. For example :-

Like "*" & [txtCOFAMNAME].[Text] & "*"

would probably work OK but it is usually safer to leave it there.

Note that you need to compare with the field's .Text property rather
than the .Value property because this property holds the current text
in the field when the Change event is triggered.

What should happen is when the form is opened, all the names will be
displayed. As you enter text into the txtCOFAMNAME field, the list of
names will be filtered to show only those that match the text.

HTH

Peter Hibbs.




When I make the changes, on opening up the form I get all records in the
table in the list box. When I attempt to enter text into the text filed and
requery I still get all the records from the CumulativeCompanyFamilyName
table. I tried the requery code in the command button's on click event and
I am looking to do a little bit more of a sophisticated look up. I have a
form with a list box (listCOFAMNAME) a text box (txtCOFAMNAME) and a command
button (cmdSEARCH). What I want to do is have the list box populate with the
Company family name and company name when the user clicks on the command
button after entering a text string into the text box. Ideally the query
would do a like "*" & CompanyFamilyName & like "*" search so that even if
there is a partial match, it would show up in the list box. I tried use the
list box wizard and modify the sql statement but it is not working. Listed
below is the SQL statement. I would like to use VBA by linking the query
action to the search button object but I am not sure how.

umulativeCOFAMNAMECONAME].[CompanyFamilyName],
[CumulativeCOFAMNAMECONAME].[Company Name] FROM CumulativeCOFAMNAMECONAME
WHERE [CumulativeCOFAMNAMECONAME].[CompanyFamilyName] Like "*" &
DATAENTRYFORM.txtCOFAMNAME ORDER BY [CompanyFamilyName], [Company Name];
Click to expand...
Click to expand...
.
Click to expand...
 
Back
Top