Updating a QueryDef

[The Professor]

Hi,



I have a predefined query that a combo box uses for its Row Source, and it works - after a fashion.

The problem is that the criteria in one of the columns in the QueryDef is a value in a Control on a Form. However as that values changes (as the records change), the query is not updating to reflect that, so I need a way in code, to "refresh" or "requery" the query, before the Combo Box displays the results in its Drop Down list.

If I run the Query myself as a user so that it displays the results in a Datasheet, it works fine and displays the correct data each time. However the Combo Box results are only correct the first time it is used, after that that drop down list always displays that same data it did first time, whatever the value of the Criteria Field in the Query.

I have had a stab at trying to update this query before the Combo Box is used, but I keep running into problems, the latest one being the "Error 3065, cannot execute a Select Query". I think I could use some help/guidance with this.

Prof.

[orange]

It sounds like you are trying to do something like

db.execute strMySQL

you can not execute a select query.
Execute works for action queries (Add(Insert), Modify(Update), Delete(Delete))

It also sounds like you need to "refresh" your query in the Afterupdate event of the combo.

Eg:
Select a value in the combo, requery the combo to refresh the form so the query has proper value, then run the query.

Perhaps you could show us some code, since I'm assuming a lot from your post.

[John_G]

Hi

Try requerying the combo box in the On Current event of the form. If you manually change the value in the form control the query uses, Requery the combo box in the After Update event of that form control too.

The syntax is : Me![comboboxname].Requery

HTH

John

[The Professor]

Hi,

Thanks for the replies.

>>It sounds like you are trying to do something like
>>db.execute strMySQL

Yes, Orange, that is what I was trying, and of course it didn't work, but it did result in some interesting bedtime reading about differences between Action queries, Select queries, Parameter queries etc. as I brushed up on chapter 5 of my copy of "Access Inside Out".

John_G, thanks for the example of Me![comboboxname].Requery it did the trick! Great - job done, thanks!

Prof.