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
758 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
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 different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

839 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