Pass ComboBox Value to Stored Query and run with VBA

AlexB123

Board Regular
Joined
Dec 19, 2014
Messages
199
Hi everyone, it's been a while so I'm pretty rusty. I am building a form that uses combobox's to select parameters from tables, and I want to pass those form control values to a stored query. I have created a button on the form that calls the stored query, qry_VIP_DESCR, but I am returning no results.

Here is the SQL Design of qry_VIP_DESCR:

VBA Code:
SELECT tbl_VIP_COUNTS.[ID], tbl_VIP_COUNTS.[MSO], tbl_VIP_COUNTS.[VIP_FLG], tbl_VIP_COUNTS.[VIPDESCR], tbl_VIP_COUNTS.[ACTVS], tbl_VIP_COUNTS.[DISCOS], tbl_VIP_COUNTS.[CNT]
FROM tbl_VIP_COUNTS
WHERE (([Forms]![frm_VIP_DESC]![cmbVIP]=[tbl_VIP_COUNTS]![VIP_FLG]));
And here is the code I am calling in the form VBA:
VBA Code:
Private Sub cmd_VIP_Desc_Click()

DoCmd.OpenQuery "qry_VIP_DESCR"


End Sub

Please help. I know there is something very simple I am missing! Thanks!
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

AlexB123

Board Regular
Joined
Dec 19, 2014
Messages
199
I believe the issue is related to the data type of the combobox. I need the value to be read as text ... I checked the tables mentioned in the query, as well as the tables used in the Row Source. The values in those tables are all set as text.

However, when I change the SQL of qry_VIP_DESCR to include a hard coded string value the query runs :

SQL:
SELECT tbl_VIP_COUNTS.ID, tbl_VIP_COUNTS.MSO, tbl_VIP_COUNTS.VIP_FLG, tbl_VIP_COUNTS.VIPDESCR, tbl_VIP_COUNTS.ACTVS, tbl_VIP_COUNTS.DISCOS, tbl_VIP_COUNTS.CNT
FROM tbl_VIP_COUNTS
WHERE (([tbl_VIP_COUNTS.VIP_FLG]="4"));
I tried to set the Format option under Properties to text, but am not given the option.
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
1,803
WHERE (([Forms]![frm_VIP_DESC]![cmbVIP]=[tbl_VIP_COUNTS]![VIP_FLG]));
You have this backwards. You want where the field contains the form value. You're asking for where the form value = the field value. In your 2nd post you have turned it around.
 

AlexB123

Board Regular
Joined
Dec 19, 2014
Messages
199
You have this backwards. You want where the field contains the form value. You're asking for where the form value = the field value. In your 2nd post you have turned it around.
I did switch it around for the exact reason you mentioned. It still does not return a result...
 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,601
Office Version
2013
Platform
Windows
Did you try:

WHERE ((CStr([Forms]![frm_VIP_DESC]![cmbVIP])=[tbl_VIP_COUNTS]![VIP_FLG]));
 

AlexB123

Board Regular
Joined
Dec 19, 2014
Messages
199
SQL:
SELECT tbl_VIP_COUNTS.ID, tbl_VIP_COUNTS.MSO, tbl_VIP_COUNTS.VIP_FLG, tbl_VIP_COUNTS.VIPDESCR, tbl_VIP_COUNTS.ACTVS, tbl_VIP_COUNTS.DISCOS, tbl_VIP_COUNTS.CNT

FROM tbl_VIP_COUNTS

WHERE (([tbl_VIP_COUNTS.VIP_FLG]=[Forms]![frm_VIP_DESC]![cmbVIP]));
If I choose one of the values between 1-8, the query will return a result of the next digit (i.e., set VIP to 2 on the form, the returned VIP is 3). However, when I get to 9, or choose a character (the potential values are 0-9, A-Z, and special characters) it returns nothing. In all of my various tables I have ensured that these are all stored as text.

To display the drop down values I use the following query as a Row Source ...

SQL:
SELECT [tbl_VIP_CAT].[ID], [tbl_VIP_CAT].[VIP_FLG] FROM [tbl_VIP_CAT];
 

AlexB123

Board Regular
Joined
Dec 19, 2014
Messages
199
Did you try:

WHERE ((CStr([Forms]![frm_VIP_DESC]![cmbVIP])=[tbl_VIP_COUNTS]![VIP_FLG]));
It does not return a result. I also tried

SQL:
SELECT tbl_VIP_COUNTS.ID, tbl_VIP_COUNTS.MSO, tbl_VIP_COUNTS.VIP_FLG, tbl_VIP_COUNTS.VIPDESCR, tbl_VIP_COUNTS.ACTVS, tbl_VIP_COUNTS.DISCOS, tbl_VIP_COUNTS.CNT

FROM tbl_VIP_COUNTS

WHERE (([tbl_VIP_COUNTS.VIP_FLG]=str([Forms]![frm_VIP_DESC]![cmbVIP]));
 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,601
Office Version
2013
Platform
Windows
Maybe just to be sure I should ask if the form is open when you run the query?

Also what value is selected when you run the query?

To check this, I would put a temporary textbox on the form and set its value to an expresssion such as:

=CStr([cmbVIP])
or
=CStr([Forms]![frm_VIP_DESC]![cmbVIP])

also check all your spellings.

Also beware that the value of the combobox is probably by default the index value of the item in the list (i.e, the fourth item, whatever it is, is just 4). This looks suspicious here - it appears that you want to get a "VIP_Flag" but also your row source has ID and VIP_FLG. That's why we want to check what the value of the combobox is that you are actually evaluating (hence the temporary textbox or some other strategy to verify the value of the combobox based on how you are retrieving the value). I believe however you can adjust the bound value and so on so that's why I'm not making any definite statements - just suggesting things to look into!


In short, if this works:
VBA Code:
SELECT tbl_VIP_COUNTS.ID, tbl_VIP_COUNTS.MSO, tbl_VIP_COUNTS.VIP_FLG, tbl_VIP_COUNTS.VIPDESCR, tbl_VIP_COUNTS.ACTVS, tbl_VIP_COUNTS.DISCOS, tbl_VIP_COUNTS.CNT
FROM tbl_VIP_COUNTS
WHERE (([tbl_VIP_COUNTS.VIP_FLG]="4"));
then there is no reason why using the combobox should be any different -- IF you are REALLY getting "4" from the combobox!
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
1,803
Your combo seems to have 2 columns/fields, the first likely being numeric. AFAIK, you cannot reference a combo column in a query, only the bound field, which in your case may be the first. Since you've not mentioned that you are trying to reference a combo column, I suspect you're passing a number (ID) to the query, not text. If that doesn't lead you to the solution, then in your button click event you could put
msgbox VarType(Me.cmbVIP)
Anything from 2 to 6 is a number type. I think 7 is a date and 8 is text. More info here on that if you need it.


How you fix that I don't know without knowing more about the combo. Usually a 2 column combo would be where the first is bound and it provides the criteria value; the other(s) provide more visual clues to the user.
 

Forum statistics

Threads
1,089,217
Messages
5,406,916
Members
403,111
Latest member
Donbozone

This Week's Hot Topics

Top