• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 768
  • Last Modified:

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

Background:
* 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.

Problem:
* 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
0
stuckp1
Asked:
stuckp1
  • 3
  • 3
1 Solution
 
rajeevnandanmishraCommented:
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.
0
 
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?

Peter
0
 
rajeevnandanmishraCommented:
VBA uses double quote...
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
rajeevnandanmishraCommented:
I mean MS Access uses double quote.
0
 
stuckp1Author 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.
0
 
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.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now