Deselect check box in previous records

[matt704]

This may also help...when I try to run that modified query in VBA I get "Compile Error: Argument not optional", probably because the query does not make sense?

[matt704]

Another update...I got it to work using '' (2 single ') before the "& Me.txtItem". I also had to put the query in DoCmd.RunSQL query. However, when I click on the check box, a dialogue appears that asks for the Item. If I add the item that is on the current form, the records that I want to no longer say Present are no longer marked as present in the table. So what I need to figure out is why the code is not referencing the item number from the current form?

[Micron]

Whoops. I forgot that you can't take a Me reference and use that in a sql statement. If you have to change to "" to get anything, then it seems like you're dealing with Null values somehow - that is, maybe you're trying to run this with no value chosen in the combo box or value displayed/entered in the text box? Or is there an issue with data types? Your OP (original post) shows that item is text (a) and location looks like a number (1), but in post 12 you explain it as the reverse. Let's clear this up first...

In the meantime, if you want to try the query thing again, replace Me.whatever with Forms!frmDataUpdate.txtItem etc.

[matt704]

Okay, I'll try to reference the form directly. Item is actually a number, and location is text. My OP is incorrect and shows items and locations reversed. Sorry for the confusion.

[matt704]

It is working great referencing the form via Form!...Is there a way to edit the message that appears on Update from "You are about to update XX rows..." to something that'll make a little more sense to the users? Also, if they select "No" on the message box, is there a way to prevent run-time error 3059 from appearing (I might want to say something like "previous records listed as Present were not changed for this location/item combo").

[Micron]

you can use DoCmd.SetWarnings False and DoCmd.SetWarnings True on either side of the Docmd.RunSql statement, which usually should be done in conjunction with an error handler to prevent warnings from being 'permanently' disabled because the code execution takes an unexpected path due to some error, thus they don't get turned back on. Or you can use the .Execute method of the Currentdb object as follows:

Private Sub chbxPresent_AfterUpdate()
Dim sql as String

sql = "Update tblAllCompiledLocations..."
If Me.chbxPresent = 0 Then
  Currentdb.Execute sql, dbFailOnError
End If
End Sub

[matt704]

The Set Warnings commands work as you intended but I realized I don't really want the warning to go away. Is there any way to change the warning message "You are about to update..." to something of my choice? And how do I add an "error handler" to the SetWarnings?

When I try the code you provided in the previous post, I get "Run-time error '3061': Too few parameters. Expected 1.". This line of code is highlighted "CurrentDb.Execute sql, dbFailOnError". Do you know whats going on there?

I'm open to using either method, but I'd like to understand each before I decided which to go with. However, both of the methods might not really be getting to what I want. Ideally, I'd be able to change the update message from " you are about to update XX rows" to something my users could make sense of. Also I'd like to see nothing appear if they click "no" on the update message box I just mentioned. currently, If they click "no" run-time error "3059" appears (operation canceled by user).

[Bulzie]

Guess you need to figure out that RunTime error but for the messages, you could do your own Message box Option:

Dim LResponse As Integer

LResponse = MsgBox("Do you wish to continue?", vbYesNo, "Continue")

If LResponse = vbYes Then
'Put your update code here including the SetWarnings lines
Else
MsgBox "Operation Cancelled" 'or leave this blank
End If

https://www.techonthenet.com/access/...sgbox_args.php

[matt704]

Perfect. Thanks!

[Micron]

There are other alternatives as well. Because you turned the Me. references into Forms! collection references, you'd have to declare the parameters, which for this case is more difficult than it needs to be, but you could research DAO parameters for edification. You can also skip presenting a message box (which sometimes quickly becomes cumbersome and annoying) and learn how to incorporate an error handling routine if you go back to the other code construct using RunSql. This example doesn't trap for any particular error, it just displays a message for any error:

Private Sub chbxPresent_AfterUpdate()
Dim sql as String

On Error GoTo errHandler
sql = "Update tblAllCompiledLocations..."
DoCmd.SetWarnings False
If Me.chbxPresent = 0 Then
  DoCmd.RunSql sql 'if an error is generated by this action line, execution goes to errHandler:
End If

exitHere:
DoCmd SetWarnings True 'this gets executed regardless if there is an error or not
Exit Sub 'this exits the sub if there is no error so that execution does not go to the error handler.

errHandler:
msgbox "Error " & err.Number & ": " & err.Description 'a custom error message displayed for any error.
Resume exitHere 'this ensures execution goes to the exitHere: line to turn warnings on
End Sub