Using Column(N) of a combo box in an SQL Query 1

[xbigblue]

Hello All,
If this appears twice, I apologize. I wrote first copy, wanted to edit it a bit, it disappeared. Hope I did not post it.

I have a data entry application in Access 2000 that uses a mainform/subform approach. I want to use column(5) from a combo box on the MAINFORM as a criterion in an SQL query that will be the rowsource for a combo box on the SUBFORM.
In other words, what gets chosen on mainform influences what can be chosen in the subform combo box.

Very simplified SQL conceptually is:

SELECT FIELD1, FIELD2,...FIELDN
FROM TABLE1
WHERE FIELD2 = FORMS!MAINFORM!COMBOSOURCE!COLUMN(5)
ETC.

I have tried many variations for the criterion, such as:

Forms!Mainform!Combosource.Column(5)
Me.Parent.Combosource.Column(5)
Me.Parent.Mainform.Combosource.column(5)
Me![Mainform]![Combosource].[Column](5)

All are thrown out by the SQL syntax checker. I can most likely solve this by pushing the column(5) on an after update event of the Combosource control to a hidden unbound textbox on the Mainform and then referring to the control in the SQL. But is there a direct solution using the desired column value in the SQL qurey?

Thanks in advance,
xbigblue

 

[Philly44]

You have to use contatenate syntax. Something like
SELECT Field1 From Table1
WHERE Field1 = " & Forms!Mainform!ComboSource(5) & ";"

Abviously you have to the change the names to the actual names you used but that should work.

I hope this helps

 

[xbigblue]

Hi Philly44,

Thanks for your quick response. From the syntax of your suggestion, it looks like I would use that in VBA code. I have not seen SQL written in the QBE pane(which is where my SQL query is written)look like your suggestion. But I tried it anyway. No go, syntax errors generated. (is the suggested code missing a quote? It only has 3 and usually quotes are in pairs). I tried taking off the concatenation and just using your suggested Combosource(5) notation, (did you mean to leave out the word "Column"??) as follows:

Field1=Forms!Mainform!Combosource(5)

and that is accepted by the QBE processor but produces an empty answer set when executed.

So, I can make it acceptable syntax wise but it does not work. Any more ideas?

Thanks,
xbigblue

 

[tlbroadbent]

Access is trying to process the column name as a function that doesn't exist. You can work around this by creating your own function. Open a VB Module and paste the following code.

Function SelectComboColumn()
SelectComboColumn = Forms!MainForm!ComboSource.Column(5)End Function

The use the function in the criteria of the query.

SELECT Field1, Field2, ... FieldN
FROM Table1
WHERE Field2 = SelectComboColumn() Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[xbigblue]

Terry,

Thanks for your response on writing the Function. It works great and gives me new ideas on how to do other things.

Ever so slowly, I inch up on knowing what the Access Monster can really do.

regards,
xbigblue