Run an Action Query but bypass the "Are you Sure" message boxes 2

[kev747]

Hey all,

I'm wanting to run both an apend and a delete action query with a single command button, but I really want them to run without the "Are you sure you want to run this type of query" and the "You are about to apend X rows" message boxes.

Is this at all possible?

Cheers.

This is a politically correct signature, due to constant whinging about my previous signature.
Long live the fun police.

 

[Remou]

You can use DoCmd.SetWarnings.

 

[dhookom]

To enhance Remou's reply a little:

DoCmd.SetWarnings False
DoCmd.OpenQuery "qappMyAppendQuery"
DoCmd.SetWarnings True

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[kev747]

Hey guys.

Thanks for the replies. The DoCmd.SetWarnings does what I requested, however I have hit a snag.

I'm running multiple Apend queries from a single command button on my form. Turning of the warnings works fine provided there are no errors in the data being apended.

However, if some of the data being apended conflicts with existing data (duplication of an indexed field in the first apend query running for example), it prevents all remaining queries from executing.

Below is the code I have attached to the command button -

Private Sub cmdRunQuery_Click()
On Error GoTo Err_cmdRunQuery_Click

    Dim stDocName31 As String
    Dim stDocName32 As String
    Dim stDocName33 As String
    Dim stDocName34 As String
    Dim stDocName41 As String
    Dim stDocName51 As String
    Dim stDocName61 As String
    Dim stDocNameTR As String

DoCmd.SetWarnings False

    stDocName31 = "qry31ModuleDataApend"
    DoCmd.OpenQuery stDocName31, acNormal, acEdit

    stDocName32 = "qry32ModuleDataApend"
    DoCmd.OpenQuery stDocName32, acNormal, acEdit

    stDocName33 = "qry33ModuleDataApend"
    DoCmd.OpenQuery stDocName33, acNormal, acEdit

    stDocName34 = "qry34ModuleDataApend"
    DoCmd.OpenQuery stDocName34, acNormal, acEdit

    stDocName41 = "qry41ModuleDataApend"
    DoCmd.OpenQuery stDocName41, acNormal, acEdit

    stDocName51 = "qry51ModuleDataApend"
    DoCmd.OpenQuery stDocName51, acNormal, acEdit

    stDocName61 = "qry61ModuleDataApend"
    DoCmd.OpenQuery stDocName61, acNormal, acEdit

    stDocNameTR = "qryThrustRevDataApend"
    DoCmd.OpenQuery stDocNameTR, acNormal, acEdit
    
DoCmd.SetWarnings True

Exit_cmdRunQuery_Click:
    Exit Sub

Err_cmdRunQuery_Click:
    MsgBox Err.Description
    Resume Exit_cmdRunQuery_Click

End Sub

Each table where the data is being apended to conatins a field that is indexed with no duplicates allowed. If I enable the SetWarnings, then deliberately create a record with a duplicated field, then execute the apend queries command button and click on yes for all of the "Are You Sure" message boxes that appear, it seems to go through correctly. The record with the duplicated field is not apended (which is correct), but the other queries apend their data to the tables, as they should.

However if I disable the SetWarnings it won't progress past the query where the duplicated field exists.

Does this make sense the way I've explained it?

Any suggestions or help very much appreciated.

Thanks for you time.

Kev.

This is a politically correct signature, due to constant whinging about my previous signature.
Long live the fun police.

 

[dhookom]

Set up your append queries to not attempt to insert values allready contained in your target table.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[kev747]

Hi all.

Thanks for the suggestions and replies.

I have successfully managed to stop the warnings and the queries will run but ignore duplicate records. Thanks to all who suggested fixes.

One last problem (I hope) is that when I enter data into the fields on my form then click on the command button to run the queries, it doesn't apend the data to the table. However if I close the form then re-open it and go to the record I've just created, or navigate to the previous record then return, and then click on the command button, the data apends.

This is most frustrating as I can't figure out why it won't apend on the first try.

Any help is greatly appreciated.

Cheers.

This is a politically correct signature, due to constant whinging about my previous signature.
Long live the fun police.

 

[dhookom]

Add a line of code needs to be added to save the current record prior to running queries or reports.
Me.Dirty = False

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[kev747]

You guys are the bomb.

Works a treat. Thanks to all.

This is a politically correct signature, due to constant whinging about my previous signature.
Long live the fun police.