Pass ComboBox Value to Stored Query and run with VBA

[AlexB123]

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:

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:

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!

 

[AlexB123]

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 :

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]

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]

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]

Did you try:

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

 

[AlexB123]

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 ...

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

 

[AlexB123]

Did you try:

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

It does not return a result. I also tried

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]

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:

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]

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.

VarType function (Visual Basic for Applications)

Office VBA reference topic

docs.microsoft.com

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.