Changing Update Query Warning Behavior

[lyness]

Hi,

I am using MS Access 2003.

I have put an update query behind a command button on a form, and,
when I push the command button I get the standard (2) warning
messages.

The first: "You are about to run an update query that will modify
data in your table... etc.,
The second: "You are about to update XX rows... etc.,

I know how to put code behind the command button to turn the warnings
off and back on again (once query is executed); but that is not
exactly what I want.

What I want is to disable 'The first' warning message, but keep 'The
second' enabled. I want the user to see how many records they are
updating, and to confirm whether or not to go forward.

Is this possible?

Thanks,

Quid

 

[Klatuu]

First, you want to change the method you use to execute the action query.
Use the Execute method. It is much faster than any other method because it
does not go through the Acces UI and therefore does not trigger the warning
messages. That means you don't have to turn them on or off.

Because the records to update message is part of the warning messages, you
will need to roll your own. So, here is what you need:

Create a select query that has the same table and criteria as your update
query. It will be used only to get a count of the number of records to be
udpated. Then you first create a recordset using the select query, get the
recordcount, present a message box to the user, and either update or cancel,
depending on the user selection:

Dim rst As Recordset
Dim lngCount as Long

set rst = Currentdb.OpenRecordset("qselGetCount")
rst.MoveLast
lngCount = rst.RecordCount
rst.Close
set rst = Nothing

If lngCount < 1 Then
MsgBox "No Matching Records Found"
Else
If MsgBox( lngCount & " Records Will Be Updates" vbQuestion +
vbYesNo) _
= vbYes Then
CurrentDb.Execute("qupdUpdateRecords"), dbFailOnError
End If
End If

 

[lyness]

Dave,

Thank-you.

That worked perfectly... I would have never thought of the second
query to capture the row count...

Take Care,

Quid

 

[Klatuu]

Good, glad I could help

 

[lyness]

I spoke to soon... and this speaks to my inexperience with VB coding.

Following is my code behind the command button:

Private Sub cmdUpdateRecords_Click()

Dim rst As Recordset
Dim lngCount As Long

Set rst = CurrentDb.OpenRecordset("qry_New_Prom_BOM_Counts")
rst.MoveLast
lngCount = rst.RecordCount
rst.Close
Set rst = Nothing


If lngCount < 1 Then
MsgBox "No Matching Records Found"
Else
If MsgBox(lngCount & " Records Will be Updated", vbQuestion,
vbYesNo) = vbYes Then
CurrentDb.Execute ("qry_New_Prom_Update_Crit_Date"),
dbFailOnError
End If
End If

End Sub

When I push the command button, I get this error message:

"Run-time error '3061': Too few parameters, Expected 3"

When I push the debug, it puts me at the first line of code: "....
Set rst = CurrentDb.OpenRecordset("qry_New_Prom_BOM_Counts")..."

I've checked the spelling on my query names, and it all seems to be
ok. Did I make a syntax error somewhere?

Thanks.

 

[Klatuu]

Your query is expecting parameters (criteria) and 3 are missing.
If your update query works, you need the exact same criteria in the select
query.

 

[lyness]

I checked the update query, and it works.

Then I checked the select query, and that works also.

They both have the same 3 parameters based on combo boxes on the
form. So the select query and update query have these fields:
ENGFAM: with criteria set to [Forms]![frm_New_Prom_Crit_Bom]!
[cbo_EngProg]
SERIAL: with criteria set to [Forms]![frm_New_Prom_Crit_Bom]!
[cbo_SerialNumber]
BOM: with criteria set to [Forms]![frm_New_Prom_Crit_Bom]![cbo_BOM]

In addition, the update query has one additional field, to update a
date:
CRIT_VAL: With 'Update to:' set to Forms!frm_New_Prom_Crit_Bom!
cboNewCritDate

Do I need to push these values into the code behind the 'Update'
command button? Or am I way off?

Thanks

Quid

 

[Klatuu]

No, if it works, it works. Why it is expecting the parameters then, I am not
sure. I am wondering if it is having a problem using a stored query with
criteria based on the form control values.

Let me do some experimenting and get back to you.

 

[Carol Grismore]

Try putting in some breakpoints to see exactly where the code is failing. I
have had problems with the currentdb.openrecordset("sometable") command, for
example. Sometimes it fails with just the message you're describing.