non related table holds criteria (Access97)

Topic

New Reply

i have a query in which i want to pull the parameter criteria from a combo box on a form. the combo box references a table that has no relationship to the other tables in the db… it is simply a criteria list… that means i want the query to pull the data from the table literally. the values in this table are things like 254. when this criteria is put directly into the criteria row of the query it works fine… but trying to pull it from the combo box doesn’t work…. any suggestions on where i might look for the culprit? i apologize if i am unclear… ask and i will try and exlain… thanks… in advance

Reply | Quote

Replies

When you build you sql string the combobox has to be out of the quotes.
sample
strSQL = “SELECT * FROM MyTable WHERE MyField ” & Me.MyCombo

This suppose that the values always will be a number and you always supply the operator in the combo box.

Reply | Quote

This is the sql statement for the query that the combo box should select the criteria for…

SELECT [State Legislators & Comm].LegTitle, [State Legislators & Comm].LegDist, [State Legislators & Comm].Party, [State Legislators & Comm].CAdd1, [State Legislators & Comm].MSSNYDist, [State Legislators & Comm].FName, [State Legislators & Comm].LName, [Current Event].[Proposed$], [Current Event].CkAmt, [Current Event].[Ck#], [Current Event].EvntDt, [State Legislators & Comm].PrevCycle
FROM [State Legislators & Comm] INNER JOIN [Current Event] ON [State Legislators & Comm].ID = [Current Event].ID
WHERE ((([State Legislators & Comm].ID)=[Forms]![Frm Operations]![Combo61]));

Providing the sql above is correct, there may be an problem with what the combo box is selecting… i have the bound colum of the combo box equal to the field in the table that holds the actual criteria… Is this a possible avenue for my inability to generate a recordset with this query?

Reply | Quote

If combo61 contains the operators (= …) like you say in your first mail you will have to set the row source of the other combo by code.
Dim strSQL As String

strSQL = "SELECT [State Legislators & Comm].LegTitle, [State Legislators & Comm].LegDist, " & _
         "[State Legislators & Comm].Party, [State Legislators & Comm].CAdd1, " & _
         "[State Legislators & Comm].MSSNYDist, [State Legislators & Comm].FName, " & _
         "[State Legislators & Comm].LName, [Current Event].[Proposed$], [Current Event].CkAmt, " & _
         "[Current Event].[Ck#], [Current Event].EvntDt, [State Legislators & Comm].PrevCycle " & _
         "FROM [State Legislators & Comm] INNER JOIN [Current Event] ON [State Legislators & Comm].ID = "
         "[Current Event].ID WHERE ((([State Legislators & Comm].ID) " & [Forms]![Frm Operations]![Combo61] & _
         "));"
Me.comboxx.Rowsource = strSQL

Pay attention to the where condition : there is no = as the operator will be provided by combo61.

Reply | Quote