run a query and skip the yes no options

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

Guest

I am running an append query from a command button on a form, and i need to
be able to stop the pop up messages that ask if i'm sure i want to run the
query, is this possible H E L P thanks in advance
 
You can create a macro, in which you can SetWarnings to Off, then OpenQuery
to run your query and then SetWarnings to On.

~John
 
I am running an append query from a command button on a form, and i need to
be able to stop the pop up messages that ask if i'm sure i want to run the
query, is this possible H E L P thanks in advance
Click to expand...

Two ways to do this:

1. Put a line

DoCmd.SetWarnings False

in the VBA code (or a line Setwarnings False in the Macro, if that's
what you're using) before running the query. Be CERTAIN to put a
SetWarnings True after the query execution, or it will turn off all
warning messages from then on!

2. Use the Querydef Execute method:

Private Sub cmdRunQuery_Click()
Dim db As DAO.Database
Dim qd As DAO.Querydef
On Error GoTo Proc_Error
Set db = CurrentDb
Set qd = db.Querydefs("MyQueryName")
qd.Execute dbFailOnError
Set qd = Nothing
Proc_Exit:
Exit Sub
Proc_Error:
<handle error condition appropriately>
Resume Proc_Exit
End Sub

John W. Vinson[MVP]
 
Hi
I have access 2007 and I get 2 warnings,, the first one says "You are about
to run an append query that will modify data in your table...
are you sure you want to run this type of action query?"


If user selects YES

then the 2nd warning pop up is displayed "You are about to append n rows.
Once you click Yes you can not undo,, blah blah blah"

I onlly want to supress the first warning, but want to keep the 2nd one so
user knows how many records they are adding.

Is this possible?

And nuther question,,,

Can you really not undo this without going into the table, finding the
records and deleting them?

I am running the append query from a command button on a form.
I was hoping I could create a command button and maybe label it undo that
will delete the records that were just added, but i haven't gotten that far
yet,,
Is this possible?

thanks so much in advance for any help with this

Nancy
 
Nurse said:
I have access 2007 and I get 2 warnings,, the first one says "You are about
to run an append query that will modify data in your table...
are you sure you want to run this type of action query?"


If user selects YES

then the 2nd warning pop up is displayed "You are about to append n rows.
Once you click Yes you can not undo,, blah blah blah"

I onlly want to supress the first warning, but want to keep the 2nd one so
user knows how many records they are adding.
Click to expand...

Do you only care about notifying them how many are about to be added, or
do you also want them to be able to cancel the operation?
Is this possible?

And nuther question,,,

Can you really not undo this without going into the table, finding the
records and deleting them?
Click to expand...

Look into transactions. If you execute your INSERT from VBA code, you
can declare a transaction on the database connection. Then, if you
want, you can roll back the transaction to leave the database in the
same state as before you attempted the INSERT.

Otherwise, once you INSERT something, it's in there and the only
recourse is to DELETE it.
 
Yes, I want to alert them of how many records will be added, and would like
to allow them to cancel if neccessary.

thanks
nancy
 
Nurse said:
Yes, I want to alert them of how many records will be added, and would like
to allow them to cancel if neccessary.
Click to expand...

You can use the Database.RecordsAffected Property to determine the
number of records affected by the most recently invoked Execute method.

And you can create a transaction with [workspace].BeginTrans

See http://msdn.microsoft.com/en-us/library/bb243806.aspx for detailed
information about managing transactions.

So you can combine those two concepts as in this brief outline:

1. BeginTrans

2. Execute your INSERT statement

3. display RecordsAffected in MsgBox to user, asking whether to continue
or abort

4a. if "continue" --> CommitTrans

4b. if "abort" --> Rollback
 
Hey Hans, thanks so much, I have read thru the url you gave me, and I don't
know where to begin or how to implement this,, can you provide me any more
direction--
Nancy


Hans Up said:
Nurse said:
Yes, I want to alert them of how many records will be added, and would like
to allow them to cancel if neccessary.
Click to expand...

You can use the Database.RecordsAffected Property to determine the
number of records affected by the most recently invoked Execute method.

And you can create a transaction with [workspace].BeginTrans

See http://msdn.microsoft.com/en-us/library/bb243806.aspx for detailed
information about managing transactions.

So you can combine those two concepts as in this brief outline:

1. BeginTrans

2. Execute your INSERT statement

3. display RecordsAffected in MsgBox to user, asking whether to continue
or abort

4a. if "continue" --> CommitTrans

4b. if "abort" --> Rollback
Click to expand...
 
Here is code I was playing with. DML_InTransaction does the work of
creating a transaction, showing RecordsAffected to the user and asking
for confirmation, then commit or roll back the transaction based on
user's response. TestInsertWithTransaction is how I called the function.

One side effect of this approach is that you will wind up with gaps in
an autonumber field when the user cancels an INSERT. That would
probably be a deal breaker if you're attaching meaning to an autonumber.
I prefer to treat autonumbers as unique meaningless values the user
never sees, so I wouldn't care about autonumber gaps.

Public Function DML_InTransaction(ByVal pstrSql As String, _
ByVal ActionType As String) As Long
Dim objWrkspc As DAO.Workspace
Dim db As DAO.Database
Dim strMsg As String
Dim lngRumRecords As Long

If Not Eval("""" & ActionType & _
""" In (""DELETE"",""INSERT"",""UPDATE"")") Then
'* require one of those 3 DML commands for ActionType *'
lngRumRecords = -1 '* signals error to caller *'
Else
Set db = CurrentDb
Set objWrkspc = DBEngine.Workspaces(0)

objWrkspc.BeginTrans
db.Execute pstrSql, dbFailOnError
strMsg = UCase(ActionType) & db.RecordsAffected _
& " records?"
If MsgBox(strMsg, vbYesNo) = vbYes Then
objWrkspc.CommitTrans
lngRumRecords = db.RecordsAffected
Else
objWrkspc.Rollback
lngRumRecords = -2 '* signals Rollback to caller *'
End If

Set objWrkspc = Nothing
Set db = Nothing
End If
DmlInTransaction = lngRumRecords
End Function

Public Sub TestInsertWithTransaction()
Dim strSql As String
Dim strMsg As String
Dim lngNumRows As Long
strSql = "INSERT INTO tblFoo (some_text)" & vbNewLine _
& "VALUES ('" & CStr(Now()) & "');"
lngNumRows = DML_InTransaction(strSql, "INSERT")
Select Case lngNumRows
Case -2
strMsg = "user canceled operation"
Case -1
strMsg = "error from DmlInTransaction"
Case 0
strMsg = "no rows affected"
Case Is >= 1
strMsg = lngNumRows & " rows affected"
Case Else
'should not get here; just in case:
strMsg = "contact developer"
End Select
'Debug.Print strMsg
MsgBox strMsg
End Sub
 
Back
Top