Query MS-Access Form ComboBox

0
votes

as the title suggests I am writing SQL out of excel vba to query Forms contents out of a MS-Access db. The SQL works fine however, the fields where combo boxes are, the SQL returns their index instead of the text field.

I spent some time googling this but most of the results are asking how to display on the form in the combo box, I am just trying to return the text display form the combo box with my SQL.

I will go ahead and say the person who designed this did a bad job, and the tables relating to these drops down have nothing in common(the tables are just a list of the drop down values and ID's).

My question is what is the best way to return this value? Can I join based off the drop down index?

1
sqlformsexcelms-accessvba
The standard value of a combobox reference will be the index. If you need to return the value of another column, use the .Column() property of the combobox object. However, this is not available in SQL, so you might need to pass the value from vba to your SQL statement.MSDN – MoondogsMaDawg
I've never tried this, but apparently you can map the desired output column to the "tag" property and call it in SQL using [Forms]![MyForm]![Combo1].Tag. From here – MoondogsMaDawg
I tried the tag method not to long ago but i got an error. I didnt think about the column property, so I need to figure out how to reference access combobox values form excel vba – Doug Coats
If you're just using SQL, perhaps use a left join to the table used in the combobox. – geeFlo

1 Answers

0
votes

This link should help you get started.

http://access.mvps.org/access/forms/frm0031.htm

Something like this, I presume...

Forms!Mainform.RecordSource