Query Def Always Asking for Parameter!!

C

CarlRostron

Registered User.
Local time
Today, 23:14
Joined
Nov 14, 2011
Messages
88
I have a form called "frmManageACF", query called "qryACFFill".

On frmManageACF I have a combo box (name cmbACFFill) which has three cols. col1 = SiteID, col2 = Site code, and col 3 = SiteName. The combo box Bound column is set to 1.

In qryACFFill, results are selected where SiteID=[Forms]![frmManageACF]!cmbACFFill

The query when I hit refresh always gives me the 'Enter Parameter Value' dialogue box. I have used this method of dynamically updating a query def before so not sure why it isn't working now.

Any ideas why this might be and why the Parameter dialogue box is always coming up??

Thanks
 
Your form with the combobox needs to be open. If it isn't this could be one reason
 
Yes I have it open in a form view already and I am changing the selection in the combo box to test it and then flipping back to the query results tab and hitting refresh all. It is then when I get the dialogue box asking for parameters...
 
I have attached the db after it has been compacted and zipped. I have spent 3 hours trying to find what the issue is on this. please can someone have a look, I bet it is only a 2 min fix for a professional ;-)
 

Attachments

I have resolved this.

I am not entirely clear why, but I removed the existing Combo Box and applied a fresh one. it is now working. There must have been some problems with the combo box but for the life of me I have no clue what was wrong with it.

For anyone reading this post, you may try this to see if it resolves itself.
 
I jjust tried rebuilding the reference to the combobox in the query and that worked as well.

Have to say, I don't know why this would be the case
 
Could be that there was a slight misspelling of the control or form and by rebuilding the reference to it, the correct one was set. Given the name

cmbACFFill

it could be that the actual name of the object had been misspelled and so the reference couldn't find it. Or the reference misspelled.
 
Thanks Bob - I had rebuilt the string about 8 times and each time the Auto Fields got shown in Access 2010 when building the reference so I was pretty sure the spelling was correct as I went straight to that as my first resolution. I checked it literally character by character to ensure the form, combo and query all matched... I put it down to one of this things
 
CarlRostron said:
Thanks Bob - I had rebuilt the string about 8 times and each time the Auto Fields got shown in Access 2010 when building the reference so I was pretty sure the spelling was correct as I went straight to that as my first resolution. I checked it literally character by character to ensure the form, combo and query all matched... I put it down to one of this things
Click to expand...
Remember it will go for fields and if you have a field and control named the same, it may choose the wrong one. So I rename my controls so they are different than the field names so I can know, with certainty, what I am selecting or using.
 
boblarson said:
Remember it will go for fields and if you have a field and control named the same, it may choose the wrong one.
Click to expand...

No so. The default when controls have the same name as fields is always to the controls collection of the form then to the fields collection of the form's recordset.

To refer a field when there is a control by the name simply use the expanded reference:
Me.Recordset.fieldname
 
Galaxiom said:
No so. The default when controls have the same name as fields is always to the controls collection of the form then to the fields collection of the form's recordset.

To refer a field when there is a control by the name simply use the expanded reference:
Me.Recordset.fieldname
Click to expand...

So have you used Access 2010 or 2013? There is intellisense in queries now and it chooses the field name by default instead of the control. So to simplify I change the names. You have your method and for my tastes I change the names and I can tell you immediately what anything refers to even out of context because nothing has the same name - nothing.
 
boblarson said:
So have you used Access 2010 or 2013? There is intellisense in queries now and it chooses the field name by default instead of the control.
Click to expand...

I have Access 2010 and my experience does not match your description. Indeed quite the opposite.

Intellisense on entering "Forms!formname!" in a query criteria only includes the field (unfortunately described as a "Recordsource Column") if there is no control by the same name on the form.

Although Intellisense is new in queries, this fundamental behaviour continues to be consistent with the way Access has functioned in previous versions and exactly as I have described. The default is always to the Controls Collection.

You have your method and for my tastes I change the names and I can tell you immediately what anything refers to even out of context because nothing has the same name - nothing.
Click to expand...

No argument there. I just wanted to point out that what you suugested about the field getting involved would not be the issue so that others did not waste their time chanigng the control names.
 
You must log in or register to reply here.

Similar threads

S
Solved Need help querying subform's combo box based on the main form.
Replies
13
Views
370
SkeletorPlus
S
Uncle Gizmo
MS Access: An Ordinal Curiosity
Replies
9
Views
703
Uncle Gizmo
Uncle Gizmo
D
Solved Synchronising a List Box to a Combo Box
Replies
9
Views
1,284
davegoodo
D
D
Need help with SQL in an Access query that has UNION for the non-relational queries/tables
2
Replies
26
Views
1,455
dlhappy
D
S
Should my code embrace more of the "Single Responsibility Principle"?
Replies
6
Views
686
jwcolby54
jwcolby54

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom