Solved

ON change run a query

Posted on 2010-08-14
29
359 Views
Last Modified: 2012-05-10
experts,

I have a form and I need to run a query on a change of the form.  
 DoCmd.OpenQuery "qryUpdateEUNameMS"

I see that there is no "on change" event of a form but there is an "on change" event of a field so im not sure where to put this?

thank you
0
Comment
Question by:pdvsa
  • 10
  • 8
  • 7
  • +2
29 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
you can try using the form's On dirty event
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
Humm ... what exactly are you trying to accomplish ?
Probably don't want to use the Change event of a control.

Maybe a command button ?

Private Sub btnUpdate
    CurrentDb.Execute "qryUpdateEUNameMS", dbFailOnError   ' no warning message will occur
End Sub

?

mx
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility

For bound forms, the first time that data changes in the current record, the form’s Dirty event will occur between the KeyPress and Change events of any control on the form.

The Change event doesn’t occur when a value changes in a calculated control, or when you select an item from a combo box list.
0
 
LVL 42

Expert Comment

by:dqmq
Comment Utility
Sounds like a job for the form's AfterUpdate event. That fires just after the underlying record (for a bound form) is saved. If you want to make sure your query works before you allow the underlying record to be saved, then use the BeforeUpdate event.  In that event, you can cancel the update if your query fails.

0
 

Author Comment

by:pdvsa
Comment Utility
Well it is updating but not the way I would like it too. I assumed it would be something with dirty form event but when entering a record it does not update when I tab out of the record.  it will update wehn I enter a new record.  

Private Sub Form_Dirty(Cancel As Integer)

     CurrentDb.Execute "qryUpdateEUNameMS", dbFailOnError   ' no warning message will occur

End Sub

untitled.JPG
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility


this will all depends on what the query "qryUpdateEUNameMS" does and how it is related to the record being added or edited
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
for new record, you can use the form's current event

private sub form_current()

if me.newrecord then

     CurrentDb.Execute "qryUpdateEUNameMS", dbFailOnError   ' no warning message will occur
end if
End Sub
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
Why don't you try the Form AfterUpdate event suggest above by dqmq

Private Sub Form_AfterUpdate()
     CurrentDb.Execute "qryUpdateEUNameMS", dbFailOnError  
     Me.Refresh   ' if this query is changing something in the underlying table of this form   ?
End Sub

mx
0
 

Author Comment

by:pdvsa
Comment Utility
Ok i will try this in a bit.  New record sounds like it would be answer but not sure if it would update on tab out of the record.   I believe i tried the afterupdate but will test to make sure i did.   Not at cmptr now.  Thx
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
IF ... you have added a new record on a bound Form or edited a bound field, then the Form AfterUpdate event *will* always occur.

mx
0
 

Author Comment

by:pdvsa
Comment Utility
Both ideas (new record route and Afterupdate of form) do work but not actually the way I need it to work.  If there is a change on any one  of the cells in the datasheet then I need to run that qry. The qry seems to only run when I click out of the record.  The form is in datasheet.  

How can I make that qry run on change of any of the cells?  I might have to run event of "on change" of each of the cells?  There is no "on change" for the form.  I am needing this qry to run to update the End User Name as I am taking that name into an Audit table.  Currently, the End User Name is blank and need to run the qry to update it.  

Private Sub Form_AfterUpdate()

     CurrentDb.Execute "qryUpdateEUNameMS", dbFailOnError
     Me.Refresh   ' if this query is changing something in the underlying table of this form   ?

    Call AuditEditEnd("tblMilestones", "audTmpMilestones", "audMilestones", "ID", Nz(Me!ID, 0), bWasNewRecord)

End Sub
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
then you will need to use the On Dirty event of the form

Private Sub Form_Dirty(Cancel As Integer)



End Sub

0
 

Author Comment

by:pdvsa
Comment Utility
Cap, i thought i had used that route but will test to see if it will work.  Thank you
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
" I am needing this qry to run to update the End User Name as I am taking that name into an Audit table"
Where is this name coming from ... and when is it valid?  It may not be valid when the form first becomes Dirty ...?  And yes, the AU event is only going to trigger when you leave a record ... but ... it should be picking up your End User Name ....

mx
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Comment

by:pdvsa
Comment Utility
FrmDirty did not work either.  This combo box i am trying to update has a qry on it: select tblEndUser.EndUserID, tblEndUser.[End User] FROM tblEndUser

i also placed the update qry on the before and AfterUpdate of each cell but to no avail.

Does that row source qry have something to do with it?

Thanks
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
i see that you are using the AUdittrail codes from allen browne's,  (Call AuditEditEnd)

you may not need to run the query.. i ask  a question a while back but you did not give any answer so here is the question again,

<this will all depends on what the query "qryUpdateEUNameMS" does and how it is related to the record being added or edited>

so what does the query "qryUpdateEUNameMS" do?
how is the query related to the record being added or edited?
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
and pdvsa ... see my question here:  http:#a33442009

mx
0
 

Author Comment

by:pdvsa
Comment Utility
Mx: i am on ee mobil and apparently hyperlinks to qstns dont work.  I assume your qstn was related to what the qry does like caps qstn above.  

Cap's qstn:
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
" I assume your qstn was related to what the qry does like caps qstn above.  "
No.  The question was:
" I am needing this qry to run to update the End User Name as I am taking that name into an Audit table"
Question:
Where is this name coming from ... and when is it valid?  It may not be valid when the form first becomes Dirty ...?
Comment:
And yes, the AU event is only going to trigger when you leave a record ... but ... it should be picking up your End User Name ....
mx
0
 

Author Comment

by:pdvsa
Comment Utility
Mx:
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
What about this:

Private Sub Form_AfterUpdate()
     CurrentDb.Execute "qryUpdateEUNameMS", dbFailOnError  
     Me.Refresh   ' if this query is changing something in the underlying table of this form   ?
     Me.cboEndUser.Requery  '  Add this *******************************
End Sub

mx
0
 
LVL 42

Expert Comment

by:dqmq
Comment Utility
Author: I have a FORM (in datasheet view) and I need to run a query on a change of the form.  
Expert:s: Use form OnDirty event and/or form AfterUpdate event
Author: OnDirty event does not update when I tab out of a record
Experts: That's correct, but AfterUpdate does
Author: AfterUpdate only works when I click out of a record. It does not work when I change a field
Experts: That's correct, but OnDirty does
Author:  OnDirty does not work. The COMBO BOX I am trying to change has a query on it. Does that row source qry have something to do with it?
Experts: COMBO BOX?????   Oh-oh...let's pretend we didn't hear that.  Won't the form events we are trying just override anything you do with the combo box?   Rest assured, the row source query behind the combo box is NOT the problem.  
My 2 cents: Honestly, "running" the (determistic, I presume) query to update a field every time a field changes seems a bit odd to me.  But the conventional way to do that is on the fields' afterupdate events.  Followed, of course, by .refresh so that the changes are conveyed to the form's recordset.  But it seems to me, we will likely continue to chase our tail until we get clarification on the requirements.  
1. When should the update occur?
2. Where does the query get the EndUserID with which to update the record?
3.  Is there any time a record should be viewed without updating the field
4.  How does the combo box fit in?  
 
 
 
 

 
0
 

Author Comment

by:pdvsa
Comment Utility
I think i might hv to post this part of db so it can be seen visually.    
0
 
LVL 42

Expert Comment

by:dqmq
Comment Utility
Excellent.  But please also explain what you want to happen and when.
0
 

Expert Comment

by:alivar
Comment Utility
Yes, posting that part of the db would make it easier for us to resolve it quicker. Along with a example scenario...

User opens DataSheet and types in "XYZ" into field "Field9" and then (which field in which table?) is updated with the name of the user who made the change (which can be found where?)
0
 

Author Comment

by:pdvsa
Comment Utility
Ok Experts, sorry for my late reply.  I have been a little under the weather and had to catch up on some stuff.  I have attached the db.  On the form that opens please double click "projects Name" and then go to tabl "Milestones".  YOu can add a row and see that the EndUserID does not populate until tabbing off of the record.  I need the EndUserID to appear WHEN the new record is entered and not after tab out.

thank you
Copy-of-System.accdb
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
how will you know which EndUserID must be placed in the added record?
0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
Comment Utility
test this

place this code in the current event of form "frmMilestones"

Private Sub Form_Current()
If Me.NewRecord Then
    Me.EndUserID = Me.Parent.cboEndUserID
End If
End Sub

Copy-of-System.accdb
0
 

Author Comment

by:pdvsa
Comment Utility
Yes!  That worked perfectly.  Thank you
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Spell checking an unbound form in MS Access 2013 9 34
Supress Detail 4 17
Access Date Query 28 27
formattig excel from access 3 17
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now