Combo box using "like"

  • Thread starter Thread starter BusterB
  • Start date Start date
B

BusterB

I have been searching for ages but cant find an answer to help.

I want to create a combo box that works like normal (e.g type "PRO" and
"PROducer" would show) but I also want record containing "PRO" to show (e.g
"Global PROducer")

I have done some experimenting but not gotten very far.

Is this possible? if so - how?
If this is not possible is there a way to work around it?

I created a query with the following sql and it works as required -

SELECT InstallationNamesTbl.InstallationID,
InstallationNamesLookupTbl.InstallationName, InstallationNamesTbl.Active
FROM InstallationNamesLookupTbl INNER JOIN InstallationNamesTbl ON
InstallationNamesLookupTbl.ID = InstallationNamesTbl.InstallationNamesID
WHERE (((InstallationNamesLookupTbl.InstallationName) Like "*" &
[Forms]![MainMenu]![InstallationCbo].[Text] & "*") AND
((InstallationNamesTbl.Active)=True));

but putting it as the rowsource of the combo box doesnt work (needs to query
on its self??)

Also tried an OnChange event to save a string containing the letters typed
into the combo box and updating the rowsource strSql in code but got a Type
Mismatch error.

Any help appreciated.
 
BusterB said:
I have been searching for ages but cant find an answer to help.

I want to create a combo box that works like normal (e.g type "PRO" and
"PROducer" would show) but I also want record containing "PRO" to show (e.g
"Global PROducer")

I have done some experimenting but not gotten very far.

Is this possible? if so - how?
If this is not possible is there a way to work around it?

I created a query with the following sql and it works as required -

SELECT InstallationNamesTbl.InstallationID,
InstallationNamesLookupTbl.InstallationName, InstallationNamesTbl.Active
FROM InstallationNamesLookupTbl INNER JOIN InstallationNamesTbl ON
InstallationNamesLookupTbl.ID = InstallationNamesTbl.InstallationNamesID
WHERE (((InstallationNamesLookupTbl.InstallationName) Like "*" &
[Forms]![MainMenu]![InstallationCbo].[Text] & "*") AND
((InstallationNamesTbl.Active)=True));

but putting it as the rowsource of the combo box doesnt work (needs to query
on its self??)

Also tried an OnChange event to save a string containing the letters typed
into the combo box and updating the rowsource strSql in code but got a Type
Mismatch error.
Click to expand...


I think using the Text property in a query will cause an
error in some situations so I recommend that you use your
other idea. The type mismatch error is probably a quoting
mistake in your code. I think the Change event procedure
would be something like this:

Dim strSQL As String
strSQL = "SELECT InstallationID,InstallationName,Active " _
& "FROM InstallationNamesLookupTbl " _
& "INNER JOIN InstallationNamesTbl " _
& " ON InstallationNamesLookupTbl.ID = " _
& " InstallationNamesTbl.InstallationNamesID " _
& "WHERE InstallationName) Like """*" _
& Me.InstallationCbo.Text & "*"" AND Active=True"
Me.InstallationCbo.RowSource = strSQL
 
Thanks For the reply Marshall.

I pasted the code from your reply into the OnChange event (removed an
spurious close bracket from the where clause). Still got the Type mismatch.

I have also tried debug.print strSql but the type mismatch error fires
before it.

I have also replaced the Me.InstallationCbo.Text by saving the text to a
string and incorporating the string in the like clause - same error.

Here is my code as it stands -

Dim strSQL, strSearch As String

strSearch = Me.InstallationCbo.Text

strSQL = "SELECT InstallationID, InstallationName, Active " _
& "FROM InstallationNamesLookupTbl " _
& "INNER JOIN InstallationNamesTbl " _
& "ON InstallationNamesLookupTbl.ID = " _
& "InstallationNamesTbl.InstallationNamesID " _
& "WHERE InstallationName Like """ * "" _
& strSearch & "*"" AND Active = True"

Me.InstallationCbo.RowSource = strSQL

Is there still a quote mistake there?

Marshall Barton said:
BusterB said:
I have been searching for ages but cant find an answer to help.

I want to create a combo box that works like normal (e.g type "PRO" and
"PROducer" would show) but I also want record containing "PRO" to show (e.g
"Global PROducer")

I have done some experimenting but not gotten very far.

Is this possible? if so - how?
If this is not possible is there a way to work around it?

I created a query with the following sql and it works as required -

SELECT InstallationNamesTbl.InstallationID,
InstallationNamesLookupTbl.InstallationName, InstallationNamesTbl.Active
FROM InstallationNamesLookupTbl INNER JOIN InstallationNamesTbl ON
InstallationNamesLookupTbl.ID = InstallationNamesTbl.InstallationNamesID
WHERE (((InstallationNamesLookupTbl.InstallationName) Like "*" &
[Forms]![MainMenu]![InstallationCbo].[Text] & "*") AND
((InstallationNamesTbl.Active)=True));

but putting it as the rowsource of the combo box doesnt work (needs to query
on its self??)

Also tried an OnChange event to save a string containing the letters typed
into the combo box and updating the rowsource strSql in code but got a Type
Mismatch error.
Click to expand...


I think using the Text property in a query will cause an
error in some situations so I recommend that you use your
other idea. The type mismatch error is probably a quoting
mistake in your code. I think the Change event procedure
would be something like this:

Dim strSQL As String
strSQL = "SELECT InstallationID,InstallationName,Active " _
& "FROM InstallationNamesLookupTbl " _
& "INNER JOIN InstallationNamesTbl " _
& " ON InstallationNamesLookupTbl.ID = " _
& " InstallationNamesTbl.InstallationNamesID " _
& "WHERE InstallationName) Like """*" _
& Me.InstallationCbo.Text & "*"" AND Active=True"
Me.InstallationCbo.RowSource = strSQL
Click to expand...
 
Dim strSQL, strSearch As String

should be:
Dim strSQL As String, strSearch As String
as written, strSQL is not specifically typed (per VBA rules) and is
therefore a Variant. I doubt that is the problem though.
I have also tried debug.print strSql but the type mismatch error fires
before it.
Click to expand...

....but I could be wrong! Not much of a reason for a type mismatch before
debug.print. (assuming debug.print is just before you set the cbo
rowsource).

--
HTH,
George


BusterB said:
Thanks For the reply Marshall.

I pasted the code from your reply into the OnChange event (removed an
spurious close bracket from the where clause). Still got the Type
mismatch.

I have also tried debug.print strSql but the type mismatch error fires
before it.

I have also replaced the Me.InstallationCbo.Text by saving the text to a
string and incorporating the string in the like clause - same error.

Here is my code as it stands -

Dim strSQL, strSearch As String

strSearch = Me.InstallationCbo.Text

strSQL = "SELECT InstallationID, InstallationName, Active " _
& "FROM InstallationNamesLookupTbl " _
& "INNER JOIN InstallationNamesTbl " _
& "ON InstallationNamesLookupTbl.ID = " _
& "InstallationNamesTbl.InstallationNamesID " _
& "WHERE InstallationName Like """ * "" _
& strSearch & "*"" AND Active = True"

Me.InstallationCbo.RowSource = strSQL

Is there still a quote mistake there?

Marshall Barton said:
BusterB said:
I have been searching for ages but cant find an answer to help.

I want to create a combo box that works like normal (e.g type "PRO" and
"PROducer" would show) but I also want record containing "PRO" to show
(e.g
"Global PROducer")

I have done some experimenting but not gotten very far.

Is this possible? if so - how?
If this is not possible is there a way to work around it?

I created a query with the following sql and it works as required -

SELECT InstallationNamesTbl.InstallationID,
InstallationNamesLookupTbl.InstallationName, InstallationNamesTbl.Active
FROM InstallationNamesLookupTbl INNER JOIN InstallationNamesTbl ON
InstallationNamesLookupTbl.ID = InstallationNamesTbl.InstallationNamesID
WHERE (((InstallationNamesLookupTbl.InstallationName) Like "*" &
[Forms]![MainMenu]![InstallationCbo].[Text] & "*") AND
((InstallationNamesTbl.Active)=True));

but putting it as the rowsource of the combo box doesnt work (needs to
query
on its self??)

Also tried an OnChange event to save a string containing the letters
typed
into the combo box and updating the rowsource strSql in code but got a
Type
Mismatch error.
Click to expand...


I think using the Text property in a query will cause an
error in some situations so I recommend that you use your
other idea. The type mismatch error is probably a quoting
mistake in your code. I think the Change event procedure
would be something like this:

Dim strSQL As String
strSQL = "SELECT InstallationID,InstallationName,Active " _
& "FROM InstallationNamesLookupTbl " _
& "INNER JOIN InstallationNamesTbl " _
& " ON InstallationNamesLookupTbl.ID = " _
& " InstallationNamesTbl.InstallationNamesID " _
& "WHERE InstallationName) Like """*" _
& Me.InstallationCbo.Text & "*"" AND Active=True"
Me.InstallationCbo.RowSource = strSQL
Click to expand...
Click to expand...
 
BusterB said:
I pasted the code from your reply into the OnChange event (removed an
spurious close bracket from the where clause). Still got the Type mismatch.

I have also tried debug.print strSql but the type mismatch error fires
before it.

I have also replaced the Me.InstallationCbo.Text by saving the text to a
string and incorporating the string in the like clause - same error.

Here is my code as it stands -

Dim strSQL, strSearch As String

strSearch = Me.InstallationCbo.Text

strSQL = "SELECT InstallationID, InstallationName, Active " _
& "FROM InstallationNamesLookupTbl " _
& "INNER JOIN InstallationNamesTbl " _
& "ON InstallationNamesLookupTbl.ID = " _
& "InstallationNamesTbl.InstallationNamesID " _
& "WHERE InstallationName Like """ * "" _
& strSearch & "*"" AND Active = True"

Me.InstallationCbo.RowSource = strSQL

Is there still a quote mistake there?
Click to expand...


Yes, but one of the quote mistakes was mine. You also added
a couple of extra spaces around the first *

Dim strSQL As String, strSearch As String

strSearch = Me.InstallationCbo.Text

strSQL="SELECT InstallationID, InstallationName, Active " _
& "FROM InstallationNamesLookupTbl " _
& "INNER JOIN InstallationNamesTbl " _
& "ON InstallationNamesLookupTbl.ID = " _
& "InstallationNamesTbl.InstallationNamesID " _
& "WHERE InstallationName Like ""*" _
& strSearch & "*"" AND Active = True"
 
Thanks again Marshall. Works like a dream!


Marshall Barton said:
Yes, but one of the quote mistakes was mine. You also added
a couple of extra spaces around the first *

Dim strSQL As String, strSearch As String

strSearch = Me.InstallationCbo.Text

strSQL="SELECT InstallationID, InstallationName, Active " _
& "FROM InstallationNamesLookupTbl " _
& "INNER JOIN InstallationNamesTbl " _
& "ON InstallationNamesLookupTbl.ID = " _
& "InstallationNamesTbl.InstallationNamesID " _
& "WHERE InstallationName Like ""*" _
& strSearch & "*"" AND Active = True"
Click to expand...
 
Back
Top