BeforeUpdate without update?

[Vince]

I have a form which is using Oracle as the datasource = single table
and the controls are mostly bound (2 textboxes are not bound and exist
in order to display names from ids). the form displays only a single
record at a time.

If I make changes to a new record (i.e. fill in the first field) and
attempt to navigate to the next record, the beforeUpdate event fires
and then I get an error saying such and such a field is null and must
be populated first,etc. This works everytime.

My form also has 2 buttons to navigate away from the form (all other
navigation such as closing the form, etc. have been disabled). These
buttons will close the form and open a seach form and the other simply
closes the form. When I make changes to a new record and click either
of these buttons the beforeUpdate event fires but only if all the not
null columns are populated does the record save and the navigation
procedes. If I only populate one field and click one of the buttons, I
know the beforeUpdate event is firing, however, the data is not saved
and no error message indicating not null columns must be populated.
The data is simply lost, without warning.

Any ideas?

I always get both messages.

Private Sub Form_BeforeUpdate(Cancel As Integer)

On Error GoTo e

MsgBox "BeforeUpdate event"

If IsNull(Me.ID.Value) Then
Me.ID.Value = conn.getCarrierDeliveriesSeqNextVal
End If

'oracle db triggers will sort out insert vs update and keep only
'relevant data
Me.DATE_CREATED.Value = Now
Me.USER_ID_CREATOR.Value = conn.appUser.ID

Me.DATE_UPDATED.Value = Now
Me.USER_ID_UPDATER = conn.appUser.ID

MsgBox "Leaving BeforeUpdate"

Exit Sub

e:
MsgBox "Error " & Err.Number & "- " & Err.Description & vbCrLf & _
"Source " & Err.Source, vbCritical

Exit Sub

End Sub

 

[Vince]

I have a form which is using Oracle as the datasource = single table
and the controls are mostly bound (2 textboxes are not bound and exist
in order to display names from ids). the form displays only a single
record at a time.

If I make changes to a new record (i.e. fill in the first field) and
attempt to navigate to the next record, the beforeUpdate event fires
and then I get an error saying such and such a field is null and must
be populated first,etc. This works everytime.

My form also has 2 buttons to navigate away from the form (all other
navigation such as closing the form, etc. have been disabled). These
buttons will close the form and open a seach form and the other simply
closes the form. When I make changes to a new record and click either
of these buttons the beforeUpdate event fires but only if all the not
null columns are populated does the record save and the navigation
procedes. If I only populate one field and click one of the buttons, I
know the beforeUpdate event is firing, however, the data is not saved
and no error message indicating not null columns must be populated.
The data is simply lost, without warning.

Any ideas?

I always get both messages.

Private Sub Form_BeforeUpdate(Cancel As Integer)

On Error GoTo e

MsgBox "BeforeUpdate event"

If IsNull(Me.ID.Value) Then
Me.ID.Value = conn.getCarrierDeliveriesSeqNextVal
End If

'oracle db triggers will sort out insert vs update and keep only
'relevant data
Me.DATE_CREATED.Value = Now
Me.USER_ID_CREATOR.Value = conn.appUser.ID

Me.DATE_UPDATED.Value = Now
Me.USER_ID_UPDATER = conn.appUser.ID

MsgBox "Leaving BeforeUpdate"

Exit Sub

e:
MsgBox "Error " & Err.Number & "- " & Err.Description & vbCrLf & _
"Source " & Err.Source, vbCritical

Exit Sub

End Sub

Thought about this some more and decided to more elegantly handle the
null values. Now I trap for all the required columns not populated and
cancel the beforeUpdate event, however, now my button click event does
not also cancel. How can I propagate the cancel? The docmd.close is
causing beforeUpdate to fire, but how can I cancel the btnClick event
when beforeUpdate cancels?


Private Sub btnSearch_Click()
DoCmd.Close
DoCmd.OpenForm "ClinicalTestsSearch"

End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)

On Error GoTo e

If IsNull(Me.ACQUISITION_DATE.Value) Or _
IsNull(Me.STUDY_ID.Value) Or _
IsNull(Me.SITE.Value) Or _
IsNull(Me.SUBJECT_CODE.Value) Or _
IsNull(Me.SUBJECT_INITIALS.Value) Or _
IsNull(Me.DELIVERY_PACKAGE_ID.Value) Or _
IsNull(Me.DATE_RECIEVED.Value) Or _
IsNull(Me.RECEIVED_BY.Value) Then

MsgBox "Required fields are not populated, please provide
values and try again"
Cancel = True
End If

....

 

[John Smith]

Put your field checking in a form function. You can then call it from each
event e.g.:

Private Sub btnSearch_Click()
If FormValid Then
DoCmd.Close
DoCmd.OpenForm "ClinicalTestsSearch"
End If
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
Cancel = Not FormValid
End If

Private Sub FormValid() As Boolean
If IsNull(Me.ACQUISITION_DATE.Value) Or _
IsNull(Me.STUDY_ID.Value) Or _
IsNull(Me.SITE.Value) Or _
IsNull(Me.SUBJECT_CODE.Value) Or _
IsNull(Me.SUBJECT_INITIALS.Value) Or _
IsNull(Me.DELIVERY_PACKAGE_ID.Value) Or _
IsNull(Me.DATE_RECIEVED.Value) Or _
IsNull(Me.RECEIVED_BY.Value) Then
MsgBox "Required fields are not populated, please provide values and try
again"
FormValid = False
Else
FormValid = True
End If
End Sub

HTH
John
##################################
Don't Print - Save trees