Run Query From AfterUpdate

[Traveler]

Hi,

Is there a way to run a query from a controls AfterUpdate event. I have a
query named qryPersonnel that I want to run from the AfterUpdate of a control
named txtNameLookup. How do I code something like this?

 

[Jeanette Cunningham]

Hi Traveler,
I suggest you run the query from the after update of the form instead of the
control.
You can create a saved update query and run it when you want to.
You can alternatively create the query in vba and run it when you want to.

To run a query, code like this-->

Dim strSQL As String

strSQL = "NameOfSavedUpdateQuery"
CurrentDb.Execute strSQL

If you are using a query in vba,
strSQL = "Select yadda, yadda, from yadda etc"
CurrentDb.Execute strSQL

You can put this code in the after update event in the same way as you use
other code.

Note this method only works with action queries.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Traveler]

Jeanette,

My saved query actually returns a value. Do I code things the same way?

 

[Piet Linden]

No. CurrentDB.Execute only works for Action queries (update/delete/
insert), which return no records.

You want
DoCmd.OpenQuery "QueryName'

 

[Traveler]

Piet,

I only want to run the query to return a value without actually opening the
query window. Is it possible to do that with a saved query, or do I need to
create a query string and get a returned value that way?

I already use the saved query in a control, I just wondered if it was
possible to run it from code. Here's the code that's in the ControlSource:

=DLookUp("maxV","qryGetVal"," [Main_ID]=" & Forms![MainForm]!Main_ID)

Thanks in advance!

 

[John W. Vinson]

I already use the saved query in a control, I just wondered if it was
possible to run it from code. Here's the code that's in the ControlSource:

=DLookUp("maxV","qryGetVal"," [Main_ID]=" & Forms![MainForm]!Main_ID)

Ummm.... that's exactly how you would return a record from a select query.

Perhaps you just need to Requery this control to see the current value of
maxV.

 

[Traveler]

To All,

The requery wasn't giving me what I needed, so I did a forced save before
running the saved query. Then I copied code I was already using in the
ControlSource of a different form control. I ended up with this:

Dim varX As Variant

DoCmd.RunCommand acCmdSaveRecord

varX = DLookUp("maxV","qryGetVal"," [Main_ID]=" & Forms![MainForm]!Main_ID)

Variable varX is meaningless to me since I only needed a way to run my saved
query in a different module without actually opening a query window. Now I
have exactly what I needed and learned a few things along the way.

Thank you to all who replied to this post. Each of you gave me ideas that
helped me to find a solution for my particular situation. I can't figure
these things out without your guidance, at least not yet. I'm glad you're
here for us all. Peace!

-T