QRY not reading textbox (1 Viewer)

[LB79]

Hello,

I have written a query that I want to filter based on the value of a text box. The result of this query then feeds to a ComboBox.
For some reason though, when I click the ComboBox I get prompted to type a value in (as though its not reading the ComboBox).

This is the SQL from the query... can anyone see any obvious error?

SELECT PIC_tblContacts.Contact, PIC_tblContacts.Tel, PIC_tblContacts.Email, PIC_tblContacts.Type, PIC_tblContacts.[AG Code]
FROM PIC_tblContacts
WHERE (((PIC_tblContacts.[AG Code])=[Forms]![PIC_subPIC]![PIC_txtPIC2]));

Thanks

 

[aman]

Hi

Is AG Code of PIC_tblContacts table number data type . If yes then write the following statement:

SELECT PIC_tblContacts.Contact, PIC_tblContacts.Tel, PIC_tblContacts.Email, PIC_tblContacts.Type, PIC_tblContacts.[AG Code]
FROM PIC_tblContacts
WHERE (((PIC_tblContacts.[AG Code])=" & [Forms]![PIC_subPIC]![PIC_txtPIC2] & "));

If it has text data type then you need single quotes around that.

Cheers

 

[neileg]

So your form is called PIC_subPIC?
And your text box is PIC_txtPIC2?

Is your form a subform? Is the form open when the query runs? Does your text box have the same name as the field it is bound to?

Not sure what aman is advising is correct, but you haven't given us a lot to go on.

 

[LB79]

Thanks for the replies. Aman im afriad that didnt work.

To give some more info.
My for is a subform
I have a txtbox that a user can type a code into.
The cbobox row source is a SQL statement (made in the same was as a normal query).
I want the SQL statement to filter information based on the txtbox, then the remeining results to be avaliable in the cbobox.

Does that make sense?
Thanks for any help

 

[Dennisk]

you need to requery the combo box, whenever the value in the text box changes.

 

[LB79]

Thanks Dennisk... can I ask for some further guidance too...

Ive added some vba to requery the cbobox after the txtbox is updated, but its still not working. The vba ive used is: Me.PIC_cboPIC2.Requery

Or is it possible that the SQL doesnt like the reference to the txtbox as its still promting me to type the code in?

 

[JANR]

Refering to controls on subforms is tricky, try this:

(((PIC_tblContacts.[AG Code])=[Forms]![[COLOR=red]NameOfMainForm[/COLOR]]![PIC_subPIC].[COLOR=red]Form[/COLOR]![PIC_txtPIC2]));

Add the elements marked in red. When in doubt use the wizard to create these refrences, helps a long way.

JR

 

[LB79]

Thanks very much guys... thats got it going!!