MS Access VBA: I can't get WHERE LIKE clause (SQL) to work, and can't thus get one field's choice to change rowsource of downstream field

* I'm using Access 2010, VBA, and SQL linked to a SQL Server database.
* I want the choice made in an "upstream" field Clustered_Or_Standalone drop-down to change the choices available in the "downstream" field's Clusters_Nr_Needed drop-down Rowsource.
* I have changed the Clustered_Or_Standalone  "After Update" event to issue a SQL query that is to update the Rowsource for  Clusters_Nr_Needed using a WHERE LIKE clause that has the Clustered_Or_Standalone field's choice.

* I can't get the WHERE LIKE clause to work using the '%' or '*' wildcard.
WHERE LIKE clause doesn't work thus can't make cascading dropdown choices work
Who is Participating?
stuckp1Connect With a Mentor Author Commented:
OK -- I've found the error:

The Clusters_Nr_Needed has three columns:

1) Clusters_Nr_Needed_ID which was a Primary Key in the database.

2) Clusters_Nr_Needed

3) Clustered_Or_Standalone

Thus when I used that table as a datasource in the Designer View, the Designer insisted that I include column 1, even though I only want to see column 2.

I had to go back to the database, and make column 2 the Primary Key, then the Designer didn't fight me anymore.

AND the code suddenly worked.

Why, frankly, is something I'd like a VBA expert explain to me.
I think that the Selected_Str is going to have some number like 1/2/3.
And this is no way going to match with the text of Clustered_Or_Standalone.

You should use something like DISPLAYVALUE instead of VALUE for storing in Selected_Str and then try to match. That would work.
stuckp1Author Commented:
I used the VBA debugger's Watch

SQL_Str = "SELECT Clusters_Nr_Needed FROM [dbo_Clusters_Nr_Needed] where [Clustered_Or_Standalone] LIKE '*Clustered*'"

Selected_Str = "Clustered"

 The SQL_Str looks OK -- does one use single-quote or double-quote?

Never miss a deadline with

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

VBA uses double quote...
I mean MS Access uses double quote.
stuckp1Author Commented:
I'm still puzzled why the Access Form Designer for ComboBox gets affected by having another than the displayed field be a Primary Key. But the problem is solved.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.