Determine user response to action query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I would like to know if a user responded to an action query warning message
with 'Yes' or 'No' to determine what to do next.

My delete query removes records from an unrelated table which matches an
'Item Code' on a subform when the user presses Ctrl-D. Then I would like to
delete the record on the subform also if the user OK'd the delete message.

Case vbKeyD
On Error Resume Next ' stop errors from Ctrl-D on blank line
KeyCode = 0 ' cancel keystroke
DoCmd.RunSQL "DELETE * " & _
"FROM [Table1] " & _
"WHERE [Table1].[Item Code]='" & [Item Code] & "'"

Dim rstMEform As dao.Recordset
Set rstMEform = Me.Recordset
rstMEform.Delete ' delete current record on subform

I thought about checking the table to see if there are any occurances of the
"Item Code" once the delete query had run, but this doesn't guarantee how the
user responded because there may not been any "Item Code" records in the
table to begin with.

Any thoughts?
Thanks
James
 
Turn off the system warning message ("You are about to delete...") and ask
the user to confirm before the query is run:

If MsgBox("You are about to delete....blah, blah, blah.... Do you wish to
continue?",vbExclamation + vbYesNo, "Delete") = vbYes Then
DoCmd.SetWarnings False
<Insert your SQL stuff here>
DoCmd.SetWarnings True
End If
 
Thanks Brian,
I can't believe I didn't think of that.
I guess I need to pull my head out of the fog.
James

Brian said:
Turn off the system warning message ("You are about to delete...") and ask
the user to confirm before the query is run:

If MsgBox("You are about to delete....blah, blah, blah.... Do you wish to
continue?",vbExclamation + vbYesNo, "Delete") = vbYes Then
DoCmd.SetWarnings False
<Insert your SQL stuff here>
DoCmd.SetWarnings True
End If

jamesdeckert said:
I would like to know if a user responded to an action query warning message
with 'Yes' or 'No' to determine what to do next.

My delete query removes records from an unrelated table which matches an
'Item Code' on a subform when the user presses Ctrl-D. Then I would like to
delete the record on the subform also if the user OK'd the delete message.

Case vbKeyD
On Error Resume Next ' stop errors from Ctrl-D on blank line
KeyCode = 0 ' cancel keystroke
DoCmd.RunSQL "DELETE * " & _
"FROM [Table1] " & _
"WHERE [Table1].[Item Code]='" & [Item Code] & "'"

Dim rstMEform As dao.Recordset
Set rstMEform = Me.Recordset
rstMEform.Delete ' delete current record on subform

I thought about checking the table to see if there are any occurances of the
"Item Code" once the delete query had run, but this doesn't guarantee how the
user responded because there may not been any "Item Code" records in the
table to begin with.

Any thoughts?
Thanks
James
 
Here's something that I put together using the .Execute method of a
Database object. By doing so, the prompt will advise the user of the
number of records affected. (FYI - my first experience with Transaction
processing) I suppose that for finese I would check the records affected
and provide an alternate message if there were 0 records impacted.

Sub snoopDB()

Dim wrk As DAO.Workspace
Dim db As DAO.Database
Dim strSQL As String

strSQL = ""
strSQL = strSQL & "INSERT INTO _tblSnooperResults ( id,
txtObjectType, txtObjectName ) "
strSQL = strSQL & "SELECT [_tblSnooperResults_backup].id,
[_tblSnooperResults_backup].txtObjectType,
[_tblSnooperResults_backup].txtObjectName "
strSQL = strSQL & "FROM _tblSnooperResults_backup;"

Set wrk = DBEngine.Workspaces(0)
Set db = CurrentDb()
wrk.BeginTrans

db.Execute (strSQL)
If MsgBox("Commit transaction? " & db.RecordsAffected & " record(s)
will be inserted.", vbOKCancel) = vbOK Then
wrk.CommitTrans
Else
wrk.Rollback
End If

Set db = Nothing
Set wrk = Nothing

End Sub
Thanks Brian,
I can't believe I didn't think of that.
I guess I need to pull my head out of the fog.
James

:

Turn off the system warning message ("You are about to delete...") and ask
the user to confirm before the query is run:

If MsgBox("You are about to delete....blah, blah, blah.... Do you wish to
continue?",vbExclamation + vbYesNo, "Delete") = vbYes Then
DoCmd.SetWarnings False
<Insert your SQL stuff here>
DoCmd.SetWarnings True
End If

:

I would like to know if a user responded to an action query warning message
with 'Yes' or 'No' to determine what to do next.

My delete query removes records from an unrelated table which matches an
'Item Code' on a subform when the user presses Ctrl-D. Then I would like to
delete the record on the subform also if the user OK'd the delete message.

Case vbKeyD
On Error Resume Next ' stop errors from Ctrl-D on blank line
KeyCode = 0 ' cancel keystroke
DoCmd.RunSQL "DELETE * " & _
"FROM [Table1] " & _
"WHERE [Table1].[Item Code]='" & [Item Code] & "'"

Dim rstMEform As dao.Recordset
Set rstMEform = Me.Recordset
rstMEform.Delete ' delete current record on subform

I thought about checking the table to see if there are any occurances of the
"Item Code" once the delete query had run, but this doesn't guarantee how the
user responded because there may not been any "Item Code" records in the
table to begin with.

Any thoughts?
Thanks
James
 
Back
Top