Using a Combobox to run a query

[echo0001]

I need the code or any help to be able to get a combobox to run a query
based apon the value selected within the combobox, i also dont know how to make the query variable and linked to the combobox so that i dont have to produce loads of queries.

I so far have this SQL code

SELECT NRTS.Item, NRTS.Description, NRTS.[NRTS No], NRTS.[Serial Number], NRTS.[Date Issued], NRTS.[Cal Due], NRTS.[Allocated To], NRTS.[Pat due Date], NRTS.Comments
FROM NRTS
WHERE [Allocated To]=Forms![Main]![Combo5];

And in the afterupdate event of the combobox,
DoCmd.OpenQuery "MyQuery"

But when i select anything in the combobox the query runs, but dosent return any records.

Any help would be great.

 

[Zigzag]

If your form is based on this query then all you need to do is requery the form
Try replacing

DoCmd.OpenQuery "MyQuery"

with

Me.requery

That is providing the select query is correct...
If you could post a copy of the form and query it would be easier to check.

 

[echo0001]

So got a bit wrong in the coding.

im not using docmd im using a a openquery marco in the afterupdate property sheet.

the form is not based apon the query. the combobox im using gets its values from a diiferent query which is

SELECT DISTINCT [Allocated To]
FROM NRTS
WHERE [Allocated To] IS NOT NULL;

so i want the value i select to run the query based upon it.

 

[Zigzag]

Can you post a copy of your form and query?

 

[echo0001]

How do i do that

 

[Zigzag]

Below the message box there is button 'Go Advanced' click this will then allow you to add attachments to your post. You might want to zip up the file first.

 

[echo0001]

Database is to large, plus contains sensative information, what i will do is delete a large part of the table but you should still be able to see what i want to do

 

[echo0001]

The file is attached

 

[Zigzag]

The combo box does not work properly in the Db that you attached.

However the way that you have set your Combo box up is to display 2 columns, they are ID and Allocate to.

Because of this you then have to reference you query to the column of the combo box.

WHERE [Allocated To]=Forms!Main!Combo5.column(0)

or

WHERE [Allocated To]=Forms!Main!Combo5.column(1)

or just set your combo box to contain only one column.

Have a play around with the combo box and let me know how you get on.

 

[echo0001]

I set the combobox to one column and now it works thank you very much for your help.

 

[Zigzag]

Your welcome.