?
Solved

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

Posted on 2012-03-28
6
Medium Priority
?
764 Views
Last Modified: 2012-04-03
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
Comment
Question by:stuckp1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 9

Expert Comment

by:rajeevnandanmishra
ID: 37778940
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
 

Author Comment

by:stuckp1
ID: 37779282
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
 
LVL 9

Expert Comment

by:rajeevnandanmishra
ID: 37779298
VBA uses double quote...
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 9

Expert Comment

by:rajeevnandanmishra
ID: 37779302
I mean MS Access uses double quote.
0
 

Accepted Solution

by:
stuckp1 earned 0 total points
ID: 37779334
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
 

Author Closing Comment

by:stuckp1
ID: 37799998
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

Containers & Docker to Create a Powerful Team

Containers are an incredibly powerful technology that can provide you and/or your engineering team with huge productivity gains. Using containers, you can deploy, back up, replicate, and move apps and their dependencies quickly and easily.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question