Querying a combo box

[chris6976]

Is it possible to query the value of a combo box on a form?
(The value of the combo box appearing in the WHERE clause).

Query to be used in a report.
Is it possible to achieve this in SQL only or does VBA have to be used?

Thanks

 

[TLady]

Yes, it is possible.
But if you were LITTLE more specific - it would help A LOT

TIA

 

[chris6976]

Ok!

I have the following SQL statement:

SELECT [Exhibitor].[Exhibitor_Ref], [Exhibitor].[Exhibitor_Name], [Exhibitor].[Tel]
FROM Exhibitor, Exhibitions, Booking
WHERE [Exhibitions].[Exhibition_Name]=Form1.Combo1="Chris";

The combo box is to provide the available 'exhibitions' from the Exhibitions table.

I want to display the SELECT data when the value of the combo box matches that of a exhibition name (and another yes/no variable which works!)

I am just having trouble getting the query to recognise the value of the combo box.

Any advice on this aspect would be gratefully received

Thanks again

 

[Rick39]

Try:
SELECT [Exhibitor].[Exhibitor_Ref], [Exhibitor].[Exhibitor_Name], [Exhibitor].[Tel]
FROM Exhibitor, Exhibitions, Booking
WHERE [Exhibitions].[Exhibition_Name]='" & Forms!Form1!Combo1 & "';"

 

[TLady]

I've just added combo and text box to the form
Combo was populated with whatever date

Private Sub Form_Load()

textbox.Value = [Forms]![frm_Name]![combobox].Value

End Sub

Is that what you were looking for ?

Regards
TIA

 

[chris6976]

erm, your advice doesnt quite help Rick..that pulls no data...Im guessing that it still doesnt recognise the combobox value?

Tia's comment about turning it to a text value, seems logical but I still cant get it to recognise the inserted value (which is pulled from the Exhibitions table)

Any further thoughts would be great!!

 

[Rick39]

In looking again at your SQL, the FROM clause would also cauase a problem....

SELECT [Exhibitor].[Exhibitor_Ref], [Exhibitor].[Exhibitor_Name], [Exhibitor].[Tel]
FROM Exhibitor, Exhibitions, Booking
WHERE [Exhibitions].[Exhibition_Name]=Form1.Combo1="Chris";

You need to specify how & what fields are to be joined

 

[chris6976]

I have modified a bit since Rick,...

SELECT [Exhibitor].[Exhibitor_Ref], [Exhibitor].[Exhibitor_Name], [Exhibitor].[Tel]
FROM Exhibitor INNER JOIN Booking ON [Exhibitor].[Exhibitor_Ref]=[Booking].[Exhibitor_Ref], Exhibitions
WHERE [Exhibitions].[Exhibition_Name] ='" & Forms!Form1!Combo1 & "' AND [Booking].[Paid] = 0;

This is 90% correct....recognising the combo value would give the final limit on the data..

Thanks

Chris

 

[TLady]

I thought combo was populated fine
And if it is - then [Forms]![frm_Name]![combobox].Value
will do the trick
Whatever Value selected in combobox will be read from the form. If it is not - there is another problem

What exactly doesn't work ?
TIA

 

[Rick39]

If using sql in VBA, the following would recognize the combo box:
SELECT [Exhibitor].[Exhibitor_Ref], [Exhibitor].[Exhibitor_Name], [Exhibitor].[Tel]
FROM Exhibitor INNER JOIN Booking ON [Exhibitor].[Exhibitor_Ref]=[Booking].[Exhibitor_Ref], Exhibitions
WHERE [Exhibitions].[Exhibition_Name] ='" & Forms!Form1!Combo1 & "' AND [Booking].[Paid] = 0;

If sql in a query, the following should recognize it:

SELECT [Exhibitor].[Exhibitor_Ref], [Exhibitor].[Exhibitor_Name], [Exhibitor].[Tel]
FROM Exhibitor INNER JOIN Booking ON [Exhibitor].[Exhibitor_Ref]=[Booking].[Exhibitor_Ref], Exhibitions
WHERE [Exhibitions].[Exhibition_Name] = Forms!Form1!Combo1 AND [Booking].[Paid] = 0;

 

[chris6976]

I have no text box on the form Tia!

The combo box pulls the data correctly from the relevant table, but the query above does not recognise the value in the text box.

I [think] thats all that's wrong!

Thanks

 

[TLady]

I had created text box as an example only !
[Forms]![frm_Name]![combobox].Value - does not reffering to a text box, don't you see it ?

[Forms]![frm_Name]![combobox].Value IS recognizable by anything

Like I said if it is not - then it is another issue alltogeather

TIA

 

[TLady]

Ofcourse it will not recognize values in combobox
It has wrong syntax, that's all

SELECT [Exhibitor].[Exhibitor_Ref], [Exhibitor].[Exhibitor_Name], [Exhibitor].[Tel]
FROM Exhibitor INNER JOIN Booking ON [Exhibitor].[Exhibitor_Ref]=[Booking].[Exhibitor_Ref], Exhibitions
WHERE [Exhibitions].[Exhibition_Name] = [Forms]![Form1]![Combo1].VALUE AND [Booking].[Paid] = 0;

NOW it will recognize it

TIA

 

[chris6976]

Also, regardless of the value in the combo box, the same data is pulled from the database.

 

[Rick39]

The only other reason that I can see that the value in your combo box would not be recognized is that the value you're looking to capture IS NOT in the bound column of the combo box........

 

[TLady]

You mean now , after you corrected syntax it pulls the same data ?
Could it be that [Exhibitions].[Exhibition_Name] only has data for value in combobox?

TIA

 

[chris6976]

Thanks all

SELECT [Exhibitor].[Exhibitor_Ref], [Exhibitor].[Exhibitor_Name], [Exhibitor].[Tel]
FROM Exhibitor INNER JOIN Booking ON [Exhibitor].[Exhibitor_Ref]=[Booking].[Exhibitor_Ref]
WHERE [Booking].[Exhibition_Name]=[Forms]![Form1]![Combo1].Value And [Booking].[Paid]=0;

The final solution!!!

Thanks to you all. All responses were helpful and gratefully receieved!!!

I shall return I have no doubt!

 

[TLady]

O, G_d, she made my day !

Come back soon,
TIA