Action Query Messages

[Ted]

i am using a2k.

i think my users will find the action query messages they get kind of a
nuisance, so in my VBA where i launch the code to actuate the query like the
one below

DoCmd.SetWarnings False
DoCmd.RunMacro "Update Screening Log (Edit Only) Record"
DoCmd.SetWarnings True

or this one

DoCmd.SetWarnings False
DoCmd.RunMacro "Append Off Study Pxs--Edit Form"
DoCmd.SetWarnings True

or this one

DoCmd.SetWarnings False
DoCmd.RunMacro "Delete Off Study Pxs--Edit Form"
DoCmd.SetWarnings True

i pre/post-pend setwarnings false/true to the relevant command.

the effect that this has is on one hand good but on the other a source of
un-ease since none knows for certain the command executed
successfully/correctly.

is there some 1/2-way measure that would permit the query confirmation
messages to display?

tia,

-ted

 

[fredg]

i am using a2k.

i think my users will find the action query messages they get kind of a
nuisance, so in my VBA where i launch the code to actuate the query like the
one below

DoCmd.SetWarnings False
DoCmd.RunMacro "Update Screening Log (Edit Only) Record"
DoCmd.SetWarnings True

or this one

DoCmd.SetWarnings False
DoCmd.RunMacro "Append Off Study Pxs--Edit Form"
DoCmd.SetWarnings True

or this one

DoCmd.SetWarnings False
DoCmd.RunMacro "Delete Off Study Pxs--Edit Form"
DoCmd.SetWarnings True

i pre/post-pend setwarnings false/true to the relevant command.

the effect that this has is on one hand good but on the other a source of
un-ease since none knows for certain the command executed
successfully/correctly.

is there some 1/2-way measure that would permit the query confirmation
messages to display?

tia,

-ted

Don't use Macros!
Use Code.

CurrentDb.Execute "Delete Off Study Pxs--Edit Form", dbFailOnError

Now warnings will be given, but a message will display if the query
fails.

 

[Ted]

Hi FredG

Did you mean to say NO warnings will be given, but a message will display if
the query fails? If so, I could use this approach.

ted

 

[fredg]

Hi FredG

Did you mean to say NO warnings will be given, but a message will display if
the query fails? If so, I could use this approach.

ted

A slip of the finger added a w.
NO warnings will be given.

 

[Ted]

i think there's a 'problem' or misunderstanding somewhere in this.....

here's an instance of the larger vba code which shows where i replaced the
runmacro with Currentdb.execute text and added dbFailOnError to the right of
the name of the macro.
If myvarOldValue = 5 Or myvarOldValue = 6 _
Or myvarOldValue = 7 Then
DoCmd.RunCommand acCmdSaveRecord
CurrentDb.Execute "Delete Off Study Pxs--Edit Form",
dbFailOnError
End If

when it gets to the revised line (currentdb....), it gives me a run time
error 3075: syntax error (missing operator) in query expression 'Off Study
Pxs-Edit Form' and when i hover over dbFailOnError = 128.

what's that mean?

 

[Ted]

following up....in another instance using the same form, i got a run-time
error 3078 claiming

"The MS Jet DB engine cannot find the input table or query 'Append Off Study
Pxs--Edit Form'. Make sure it exists and that its name is spelled correctly."
??

 

[fredg]

i think there's a 'problem' or misunderstanding somewhere in this.....

here's an instance of the larger vba code which shows where i replaced the
runmacro with Currentdb.execute text and added dbFailOnError to the right of
the name of the macro.
If myvarOldValue = 5 Or myvarOldValue = 6 _
Or myvarOldValue = 7 Then
DoCmd.RunCommand acCmdSaveRecord

CurrentDb.Execute "Delete Off Study Pxs--Edit Form",

dbFailOnError
End If

when it gets to the revised line (currentdb....), it gives me a run time
error 3075: syntax error (missing operator) in query expression 'Off Study
Pxs-Edit Form' and when i hover over dbFailOnError = 128.

what's that mean?

I just ran the execute code, using an update query with your query
name ("Delete Off Study Pxs--Edit Form"). It worked fine.

Your query name contains spaces and 2 hyphens.
CurrentDb.Execute "Delete Off Study Pxs--Edit Form"

The error message has just 1 hyphen.
'Off Study Pxs-Edit Form'

Make sure the name is spelled correctly.
I would select and copy the name of the query, then paste it into this
code.
I hope the ,dbFailOnError is on the same line. It should be.

Do you really need such a long query name?
Do you really need to use spaces within the name?

 

[Ted]

fred, i've imroved on the spaced out names (hopefully) per your idea and
mod'd the code below; when i go from case 5 to case 4, i get the following
error now on this line: qd.Execute dbFailOnError, i.e. run-time error 3061.
too few parameters. expected 2.




Private Sub Frame1_AfterUpdate()
Dim Response As Long
Dim Results As Integer
Dim db As DAO.Database
Dim qd As DAO.QueryDef
Set db = CurrentDb


Select Case Frame1.Value
Case 1
Me.Onlistdate.SetFocus

If myvarOldValue = 5 Or myvarOldValue = 6 _
Or myvarOldValue = 7 Then
DoCmd.RunCommand acCmdSaveRecord
Set qd = db.QueryDefs("DeleteFromFUEditForm")
qd.Execute dbFailOnError
End If

Case 2
Me.REgisteredDAte.SetFocus

If myvarOldValue = 5 Or myvarOldValue = 6 _
Or myvarOldValue = 7 Then
DoCmd.RunCommand acCmdSaveRecord
Set qd = db.QueryDefs("DeleteFromFUEditForm")
qd.Execute dbFailOnError
End If

Case 3
Me.OnStudyDate.SetFocus

If myvarOldValue = 5 Or myvarOldValue = 6 _
Or myvarOldValue = 7 Then
DoCmd.RunCommand acCmdSaveRecord
Set qd = db.QueryDefs("DeleteFromFUEditForm")
qd.Execute dbFailOnError
End If

Case 4
Me.TXEndedDate.SetFocus

If myvarOldValue = 5 Or myvarOldValue = 6 Or myvarOldValue =
7 Then
DoCmd.RunCommand acCmdSaveRecord
Set qd = db.QueryDefs("DeleteFromFUEditForm")
qd.Execute dbFailOnError
End If

Case 5
Me.OffStudyDate.SetFocus


If myvarOldValue = 1 Or myvarOldValue = 2 Or myvarOldValue =
3 Or myvarOldValue = 4 _
And Not IsNull(OffStudyDate) And Not IsNull(SequenceNum)
Then
DoCmd.RunCommand acCmdSaveRecord
If Flag >= 0 Then
Set qd = db.QueryDefs("AppendToFUEditForm")
qd.Execute dbFailOnError
End If
ElseIf myvarOldValue = 6 Or myvarOldValue = 7 And Not
IsNull(OffStudyDate) _
And Not IsNull(SequenceNum) Then
DoCmd.RunCommand acCmdSaveRecord
If Flag = 0 Then
Set qd = db.QueryDefs("UpdateFUEditForm")
qd.Execute dbFailOnError
End If
End If

Case 6
Me.LTFUDate.SetFocus

If myvarOldValue = 5 Or myvarOldValue = 7 Then
DoCmd.RunCommand acCmdSaveRecord
If Flag = 0 Then
Set qd = db.QueryDefs("UpdateFUEditForm")
qd.Execute dbFailOnError
End If
End If

Case 7
Me.DateDth.SetFocus

If myvarOldValue = 5 Or myvarOldValue = 6 Then
DoCmd.RunCommand acCmdSaveRecord
If Flag = 0 Then
Set qd = db.QueryDefs("UpdateFUEditForm")
qd.Execute dbFailOnError
End If
End If

Case Else

End Select

myvarOldValue = Me.Frame1 ' myvarOldValue is a Module Level vble per
DGoldgar (see also OnCurrent Event)

End Sub