Combobox as variable in where clause

[Broker666]

Hi,

I have exhausted my tried and tested technique or trial and error and copious amounts of searching the internet to try and resolve this but to no avail.

Basically what I have a table called Pnd_Trades and a combobox called 'broker' located on a form called 'Getstats' and I want to integrate this into the Where clause on the below (and several others similar queries):

Select *
FROM Pnd_Trades
Where Pnd_Trades.Borrower = 'value contained in a combobox called 'broker' located on a form called 'Getstats'

Ideally I would like to choose a value from the dropdown list contained in the combobox then click a button to display the result of the query based on the current input of the variable selected in the combobox.

Any help would be greatly appreciated. I am having a frustrating and fruitless time trying to resolve this.

 

[pr2-eugin]

I hope your ComboBox does not have two columns, the bound column is 1 and the First column is an ID field.. Did you try the following..

SELECT *
FROM Pnd_Trades
WHERE Pnd_Trades.Borrower = Forms![Getstats]![broker]

Is this in a Query?

 

[Broker666]

Hi,

Thanks for getting back to me. It is in a query yes, but I open to suggestions if you think this would be better served by calling the query in VBA? I tried the code and it doesn't generate any errors but for some reason doesn't produce any results either?

 

[Broker666]

Oh and you are correct, the label for the combo box is Broker but the combo box itself is called Combo0.

 

[pr2-eugin]

So did you try..

SELECT *
FROM Pnd_Trades
WHERE Pnd_Trades.Borrower = Forms![Getstats]![Combo0]

 

[Broker666]

The query doesn't generate any errors but when I switch to Datasheet View it doesn't display any results. Basically I have the form open, with a value selected and then open the query. I also tried creating a button on the form with the combobox on and then created a button to open the report linked to the query when the button is clicked after I have selected a value but it still doesn't return any results, any suggestions?

 

[Broker666]

Ok now when I open the report it is prompting me to enter the parameter value for Forms!Getstats!Combo0 but doesn't not display the list?

 

[Broker666]

Would really appreciate some advice on this.

 

[pr2-eugin]

Am sorry might have lost track of the thread.. what is the Data Type of the field 'Borrower' in the 'Pnd_Trades' table? Also could you show the Rowsource of the Combo0?

 

[Broker666]

Here's where I'm at with it now. I have a form that contains nothing but a combobox, which contains all possible values for the borrower field in the table Pnd_Trades. What I want to do is select a borrower from the combobox and then get it to launch a report based on the below query:

SELECT *
FROM Pnd_Trades
WHERE Borrower = 'borrower selected in combobox' AND 'several other criteria that are not variable are also met';

What I'm struggling with is the correct syntax for correctly referencing the variable value selected in the combo box (which is now called 'Combo2' and is located on the 'Stats' form)?

Thanks again for your help so far.

 

[pr2-eugin]

That was not the answer I was expecting.. Please could you let me know,,

..what is the Data Type of the field 'Borrower' in the 'Pnd_Trades' table? Also could you show the Rowsource of the Combo0?

Did you try this as the Query?

SELECT *
FROM Pnd_Trades
WHERE [COLOR=Red][B]Borrower = Forms![Stats]![Combo2][/B][/COLOR] AND 'several other criteria that are not variable are also met';

The above will work if the Borrower field in the table Pnd_Trades is the same as the Rowsource Bound Column in the Combo2..