query fails on combobox update

[GolferJohn]

Hello all wiser than I.
I am getting closer to my goal. Perhaps you can advance me further.
In access 2000, I have constructed a query that will use the value selected in a combo box on my form as criteria, and will return the correct record the first time I open my database, but when I change the selection in the combobox and run the query again, it returns the record of the original combobox entry.
How can I ensure my query will refernce the current value selected in the combo box???

John

 

[xlbo]

You should use something like:
myVar = comboboxname.text

Criteria in query would then reference myVar

If yuo are already doing that, you may need to use REQUERY
Another suggeastion is to ask this in the Access Forms forum

Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[GolferJohn]

Hi.
Thanks for your suggestion.
From your reference to variables, it sounds like you are suggesting I need to accomplish this using VBA scripting.
Is it not possible to do it any other way? Should I do the script in a button? I have done a little programming in Delphi, but no VB.

John

 

[xlbo]

Unfortunately, I believe that the events associated with controls (like a combobox) are only available thru code. Therefore, to run the query again, you would need to use the Combobox CHANGE event
Do get to this, simply doubleclick on the combo in design mode
I think you can set up a macro to run the query and call that from code via the doCmd command
doCmd openquery "queryname"

Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[GolferJohn]

Thanks,

I guess I'll have to do a little study.

John

 

[smurf01]

golfer John, have you solved this yet ??. how do you run the query, with a command button ??

Regards

Paul

 

[GolferJohn]

Hi Paul,
Yes I have solved my problem and all works well.
In this instance however I did not use a command button, rather as xlbo suggested, I used the 'after update' property (found on the event tab) of my combo box. Double clicking to the right of the entry area of the 'after update' event brings up the VBA editor with a 'private sub' routine ready to use. I then entered 'Me.requery" and that was all ther was to it.

In another place on my form, I have used a command button to run a query. After creating and testing the query I went to the 'On Click' property of the command button and clicking to the right of the entry area brings up a macro editor.
Under 'action' I selected 'open query' on the first line, and 'close' on the second line, and at the bottom entered the name of my query, 'Datasheet' for view, and 'Edit' for data mode.

This works fine.
Hope this helps,

John

 

[smurf01]

Golfer John, I asked the question because if you had not then I had a solution for you that is all

Regards

Paul

 

[GolferJohn]

Thanks

John