BeforeUpdate problem

[Guest]

To improve data quality, I put code in the BeforeUpdate event of my main form
to check for certain items the user left blank, cancel the update, and inform
the user. This works well except an error occurs when there's missing info.
and the user tries to navigate to a new record using a search combo box. The
error happens because Access tries to automatically save the record which
conflicts with the BeforeUpdate code.

Perhaps I need code to jump to the BeforeUpdate code/criteria (I'd rather
not retype it a second time in the search box's AfterUpdate event) and then,
if all is well, save and go to the next record. Any idea how best to handle
this? I'm looking for something efficient because I plan to add similar
BeforeUpdate code to some subforms too.

Thanks. Kurt

###

The error
---------
Run-time error ‘2115’: The macro function set to the BeforeUpdate … property
for this field is preventing [the database] from saving the data in the
field.â€

Code for search box
-----------------------
Private Sub cboFindCase_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[INT_CPUCODE] = " & Str(Nz(Me![cboFindCase], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Form's BeforeUpdate code
------------------------
Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me.strInt_Jurisdiction) Then
MsgBox "Please select the city for this case.", vbExclamation, "Missing
Data"
Me.strInt_Jurisdiction.SetFocus
Cancel = True
ElseIf IsNull(Me.strOut_Disposition) Then
MsgBox "Please select the disposition for this case.", vbExclamation,
"Missing Data"
Me.strOut_Disposition.SetFocus
Cancel = True
...
End If
End Sub

 

[Samantha Rawson via AccessMonster.com]

This is probably a stupid idea and not what you're looking for, but couldn't you just set the fields as having required entry?

 

[Marshall Barton]

To improve data quality, I put code in the BeforeUpdate event of my main form
to check for certain items the user left blank, cancel the update, and inform
the user. This works well except an error occurs when there's missing info.
and the user tries to navigate to a new record using a search combo box. The
error happens because Access tries to automatically save the record which
conflicts with the BeforeUpdate code.

Perhaps I need code to jump to the BeforeUpdate code/criteria (I'd rather
not retype it a second time in the search box's AfterUpdate event) and then,
if all is well, save and go to the next record. Any idea how best to handle
this? I'm looking for something efficient because I plan to add similar
BeforeUpdate code to some subforms too.

Thanks. Kurt

###

The error
---------
Run-time error ‘2115’: The macro function set to the BeforeUpdate … property
for this field is preventing [the database] from saving the data in the
field.”

Code for search box
-----------------------
Private Sub cboFindCase_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[INT_CPUCODE] = " & Str(Nz(Me![cboFindCase], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Form's BeforeUpdate code
------------------------
Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me.strInt_Jurisdiction) Then
MsgBox "Please select the city for this case.", vbExclamation, "Missing
Data"
Me.strInt_Jurisdiction.SetFocus
Cancel = True
ElseIf IsNull(Me.strOut_Disposition) Then
MsgBox "Please select the disposition for this case.", vbExclamation,
"Missing Data"
Me.strOut_Disposition.SetFocus
Cancel = True
...
End If
End Sub

Rather than let the bookmark trigger the save/validation,
you could call the BeforeUpdate event directly. Try
something like this air code:

Private Sub cboFindCase_AfterUpdate()
Dim rs As Recordset
Dim bolCancel As Integer
If Me.Dirty Then
Call Form_BeforeUpdate(bolCancel)
If bolCancel Then
Exit Sub
Else
Me.Dirty = False
End If
End If
' Find the record that matches the control.
Set rs = Me.RecordsetClone
rs.FindFirst "[INT_CPUCODE] = " & Nz(Me![cboFindCase], 0)
If Not rs.NoMatch Then Me.Bookmark = rs.Bookmark
End Sub

Note the other changes too, particularly RecordsetClone
instead of Recordset.Clone and NoMatch instead of EOF

 

[Guest]

This is probably a stupid idea and not what you're

looking for, but couldn't you just set the fields
as having required entry?

That would work for simple situations but I have some related fields which
need to be evaluated together. For example, one requirement is that Field1,
Field2, *or* Field3 is entered. Code is the best way to handle this.

 

[Guest]

Just what I was looking for. Thanks.

To improve data quality, I put code in the BeforeUpdate event of my main form
to check for certain items the user left blank, cancel the update, and inform
the user. This works well except an error occurs when there's missing info.
and the user tries to navigate to a new record using a search combo box. The
error happens because Access tries to automatically save the record which
conflicts with the BeforeUpdate code.

Perhaps I need code to jump to the BeforeUpdate code/criteria (I'd rather
not retype it a second time in the search box's AfterUpdate event) and then,
if all is well, save and go to the next record. Any idea how best to handle
this? I'm looking for something efficient because I plan to add similar
BeforeUpdate code to some subforms too.

Thanks. Kurt

###

The error
---------
Run-time error ‘2115’: The macro function set to the BeforeUpdate … property
for this field is preventing [the database] from saving the data in the
field.â€

Code for search box
-----------------------
Private Sub cboFindCase_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[INT_CPUCODE] = " & Str(Nz(Me![cboFindCase], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Form's BeforeUpdate code
------------------------
Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me.strInt_Jurisdiction) Then
MsgBox "Please select the city for this case.", vbExclamation, "Missing
Data"
Me.strInt_Jurisdiction.SetFocus
Cancel = True
ElseIf IsNull(Me.strOut_Disposition) Then
MsgBox "Please select the disposition for this case.", vbExclamation,
"Missing Data"
Me.strOut_Disposition.SetFocus
Cancel = True
...
End If
End Sub

Rather than let the bookmark trigger the save/validation,
you could call the BeforeUpdate event directly. Try
something like this air code:

Private Sub cboFindCase_AfterUpdate()
Dim rs As Recordset
Dim bolCancel As Integer
If Me.Dirty Then
Call Form_BeforeUpdate(bolCancel)
If bolCancel Then
Exit Sub
Else
Me.Dirty = False
End If
End If
' Find the record that matches the control.
Set rs = Me.RecordsetClone
rs.FindFirst "[INT_CPUCODE] = " & Nz(Me![cboFindCase], 0)
If Not rs.NoMatch Then Me.Bookmark = rs.Bookmark
End Sub

Note the other changes too, particularly RecordsetClone
instead of Recordset.Clone and NoMatch instead of EOF