• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 410
  • Last Modified:

ON change run a query

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
pdvsa
Asked:
pdvsa
  • 10
  • 8
  • 7
  • +2
1 Solution
 
Rey Obrero (Capricorn1)Commented:
you can try using the form's On dirty event
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
 
Rey Obrero (Capricorn1)Commented:

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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
dqmqCommented:
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
 
pdvsaAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:


this will all depends on what the query "qryUpdateEUNameMS" does and how it is related to the record being added or edited
0
 
Rey Obrero (Capricorn1)Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
 
pdvsaAuthor Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
 
pdvsaAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
then you will need to use the On Dirty event of the form

Private Sub Form_Dirty(Cancel As Integer)



End Sub

0
 
pdvsaAuthor Commented:
Cap, i thought i had used that route but will test to see if it will work.  Thank you
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
" 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
 
pdvsaAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
and pdvsa ... see my question here:  http:#a33442009

mx
0
 
pdvsaAuthor Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
" 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
 
pdvsaAuthor Commented:
Mx:
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
 
dqmqCommented:
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
 
pdvsaAuthor Commented:
I think i might hv to post this part of db so it can be seen visually.    
0
 
dqmqCommented:
Excellent.  But please also explain what you want to happen and when.
0
 
alivarCommented:
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
 
pdvsaAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
how will you know which EndUserID must be placed in the added record?
0
 
Rey Obrero (Capricorn1)Commented:
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
 
pdvsaAuthor Commented:
Yes!  That worked perfectly.  Thank you
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 10
  • 8
  • 7
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now