Prompting for query criteria even though criteria refers to control

[chrisinparra]

I don't know what I am doing wrong here. It is doing my head in but hopefully it is something simple.

I have a query as follows

SELECT [Count List].Barcode, [Count List].[No Units in Carton], [Count List].[No Cartons], [Count List].[No Singles], [_Item List Current QRY].LastOfDescription, [_Item List Current QRY].[LastOfUnit Sale Price], [Count List].[Order No]
FROM [_Item List Current QRY] INNER JOIN [Count List] ON [_Item List Current QRY].[Barcode] = [Count List].[Barcode]
WHERE ((([Count List].[Order No])=[Forms]![Order Detail]![txtOrderID]));

I have this query as the record source for a listbox on a form. When I open the form, I want the form to open with the listbox showing the records for the relevant order. But instead, before the form opens, an input box comes up asking for 'Enter Parameter Value?' with the reference [Forms]![Order Detail]![txtOrderID]

I have tried referring to both a textbox on the form being opened, and a listbox on the form prior and I get the same popup. What am I doing wrong?

 

[MajP]

Is the form itself unbound? Is there anything in the forms sortby or orderby properties? Can you add this in the forms on open event:
msgbox [Forms]![Order Detail]![txtOrderID]
and report what it says.

 

[dhookom]

I believe I created your setup and didn't get any prompt. Did you use the builder in the query design of the list box Row Source?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[chrisinparra]

Thanks to MajP, I played around with msgbox and managed to solve the problem. I am now setting the value of [Forms]![Order Detail]![txtOrderID] in the form open event, and the requery the listbox in the same event. It appears to work. I can only assume that the value of [Forms]![Order Detail]![txtOrderID] wasn't being set before the listbox wanted to run the query when the form opens. I tried it both bound and unbound and I couldn't get it work until this solution. It still doesn't make any sense to me, but it is working now.

Duane - I did use the builder. I have no idea why I was getting the prompt when the query was referring to a valid control.

It works now. Thanks to both of you.

 

[MajP]

Is [Forms]![Order Detail]![txtOrderID] the parent form that the listbox is on. If so this is likely an order of operations issue. Forms load from the inside out. Which means subforms and controls load before the mainform.