Run query from form whwn records edited

  • Thread starter Thread starter Randy
  • Start date Start date
R

Randy

I need a query to run from my main form only when a change has been made to
a previous record, not a new record. The code below works fine when a
change has been made to a previous record but it also runs when a new record
is entered. How do I prevent the query from running when a new record is
entered...Thanks...Randy

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim stDocName As String
If Me.Dirty Then 'Save any edits first.
Me.Dirty = True
stDocName = "AppendCorrections"
DoCmd.OpenQuery stDocName, acNormal, acEdit
Else
End If
End Sub
 
I need a query to run from my main form only when a change has been made to
a previous record, not a new record. The code below works fine when a
change has been made to a previous record but it also runs when a new record
is entered. How do I prevent the query from running when a new record is
entered...Thanks...Randy
Click to expand...

A Form has a NewRecord property that will help. Also, the form will
ALWAYS be dirty at this point, since BeforeUpdate fires only when
there are changes to save.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim stDocName As String
If Not Me.NewRecord Then 'Save any edits first.
Me.Dirty = False ' False, not True to save the record
stDocName = "AppendCorrections"
DoCmd.OpenQuery stDocName, acNormal, acEdit
End If
End Sub
 
Thank you very much, that worked great. One more thing, when the query is
run, is there a way to prevent the messages "You are about to append
records" etc. from popping up. I would like this query to be run in the
background. Thanks again...Randy
 
Thank you very much, that worked great. One more thing, when the query is
run, is there a way to prevent the messages "You are about to append
records" etc. from popping up. I would like this query to be run in the
background. Thanks again...Randy
Click to expand...

Two ways:

DoCmd.SetWarnings False
DoCmd.OpenQuery...
DoCmd.SetWarnings True

Or, better since it traps errors:

Dim qd As DAO.Querydef
Dim db As DAO.Database
Dim prm As Parameter
On Error GoTo Proc_Error
.... <decide if you want to run it>
Set db = CurrentDb()
Set qd = db.Querydefs("AppendCorrections")
For Each prm In qd.Parameters
prm.Value = Eval(prm.Name)
Next prm
qd.Execute dbFailOnError
Set qd = Nothing
Proc_Exit:
Exit Sub
Proc_Error:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume Proc_Exit
End Sub
 
Worked Great! You guys are wizards...
John Vinson said:
Two ways:

DoCmd.SetWarnings False
DoCmd.OpenQuery...
DoCmd.SetWarnings True

Or, better since it traps errors:

Dim qd As DAO.Querydef
Dim db As DAO.Database
Dim prm As Parameter
On Error GoTo Proc_Error
... <decide if you want to run it>
Set db = CurrentDb()
Set qd = db.Querydefs("AppendCorrections")
For Each prm In qd.Parameters
prm.Value = Eval(prm.Name)
Next prm
qd.Execute dbFailOnError
Set qd = Nothing
Proc_Exit:
Exit Sub
Proc_Error:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume Proc_Exit
End Sub
Click to expand...
 
Back
Top