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
755 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
  • 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

867 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now