Turn off update query prompts

[Jay]

Hi,

I have a simple update query, run from a command button, which changes a few
fields.

However, I want the user to be able to simply click the command button & the
fields to be updated without the Prompts - You are about to run a query etc.
And You have updated x no. of rows etc.

If anyone could let me know how to ndo this I'd be very grateful.

Many thanks,

Jason

 

[Nick 'The database Guy']

Hi Jay

There are two ways, that I know of, that you can accomplish this.

The easiest and simplest is:

Private Sub CommandBtn
With Docmd.
.SetWarnings False
.RunSQL sql
.SetWarnings True
End With
End Sub

The other requires a connection object be set up and you would need to
call the .Execute method of that object.

Good luck

 

[Jay]

Thanks for the help Nick.

I'm a total VBA novice so would really appreciate some advice. Do I simply
paste the code below into the Event Procedure of my command button? The VBA
for the button is:

Private Sub CmdResolveQuery_Click()

On Error GoTo Err_CmdResolveQuery_Click

Dim stDocName As String

stDocName = "QryResolveQuery"

DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_CmdResolveQuery_Click:

Exit Sub

Err_CmdResolveQuery_Click:

MsgBox Err.Description

Resume Exit_CmdResolveQuery_Click


End Sub

Where would I add your code? Any advice GREATLY appreciated.

Jason

 

[Nick 'The database Guy']

Hi Jay,

Paste this code in to the event procedure for the Click event.

Private Sub CmdResolveQuery_Click()
On Error GoTo Err_CmdResolveQuery_Click
Dim stDocName As String
stDocName = "QryResolveQuery"
With DoCmd
.SetWarnings False
.OpenQuery stDocName, acNormal, acEdit
.SetWarnings True
End With

Exit_CmdResolveQuery_Click:
Exit Sub

Err_CmdResolveQuery_Click:
MsgBox Err.description
Resume Exit_CmdResolveQuery_Click
End Sub

Good luck,

Nick

 

[Jay]

Cheers Nick, that works perfectly.

The only problem is it takes about 20 seconds? The update query is only
changing two fields. Is this typical, or is there any way I could speed it
up?

Many thanks,

Jay

 

[Nick 'The database Guy']

Hi Jay,

The code will run through in fractions of a second, what is taking the
time is the query. Take a look at your table design, are all the
fields that are be searched in the query indexed?

Please don't 'over index' the table as indexing slows down adding data,
as it has to be added to the index.

Good luck!