Before Update Event

[Guest]

Hello All,
I have a main form and sub-form. After selecting an employee on the
main form, the sub-form will display all matching records. If there are no
matching records, the subform will prefill with the ID from the main form and
start the 1st record for the employee.
I wanted to put data validation on the before update event, however
the warning message fires as soon as the form is pre-filled for new records.
I only want the messages to fire when the navigation buttons are used.
I am using the default navigation buttons, because I want to keep the
"1 of 3" feature.

Any suggestions would be greatly appreciated!
Thank you in advance,
Renee

 

[Allen Browne]

In a standard form and subform, the subform will not inherit the ID of the
main form until you start entering something. What is triggering this? Are
you assigning a value to the new record row of the subform programmatically?
Entering a value? ...?

If the subform is based on a query that has multiple tables, there is a
problem where Access fires off a message saying it cannot save the record
before you have finished filling it in. The message is something about not
being able to assign a value. The cause of the problem is that the other
table (not the one where you are actually trying to write this record) has a
Default Value assigned. Access wrongly tries to assign the Default Value to
the other table, and so gets itself in a knot because it should not be
trying to create a record in the other table. The solution is to remove the
Default Value from all fields in the other table, and remove the Default
Value from any controls on the subform that are bound to fields from the
other table.

 

[Guest]

Thank you for your response, however there are no default values assigned to
the tables or forms. The main form selects the employee. The main and
subforms are linked on the EmpID.

On the subform's current event, if there are no current matching records I
have it set to start a new record:

Private Sub Form_Current()

Dim varWeek As Variant

If Not IsNull(Me.Parent.EmpID) Then
If Me.NewRecord = True Then
varWeek = DMax("[Week#]", "[tblTraining]", "[EmpID] = '" &
Me.Parent.EmpID & "'")
Me.EmpID = Forms![frmUpdateTraining]!EmpID
Me.[Week#] = Nz(varWeek, 0) + 1
End If
End If
End Sub

On the before update event of the sub-form:
If Not IsNull(Me.EmpID) _
And Not IsNull(Me.[Week#]) Then
If IsNull(Me.Tot_Sales) Then
MsgBox "Enter the total sales.", vbOKOnly
Cancel = True
Else
If IsNull(Me.Day1) Then
MsgBox "Enter the totals for Day 1.", vbOKOnly
Cancel = True
End If
End If
End If

The validation from the Before Update event fired as soon as the new record
is started from the On Current event. So I moved the validation from the
before update event to the close command button. However, that also removes
the validation from the default navigation buttons.

Is there a way to add the validation to the default navigation buttons? Or,
a way to re-create the "1 of 3" feature from the default navigation buttons
(then I can disable the default and create command buttons)?

 

[Allen Browne]

Instead of assigning the values for the new record in Form_Current, how
about using Form_BeforeInsert? This should avoid creating the new record
unnecessarily (i.e. just because the user moved there, even if they don't
want it.)

If the EmpID is named in the subform control's
LinkMasterFields/LinkChildFields, you don't need to perform that assignment:
Access will do that automatically.

Are you sure the code in the BeforeUpdate event is in the event of the form,
not the control, i.e. Form_BeforeUpdate, not EmpID_BeforeUpdate?

Form_BeforeUpdate is fired by the nav buttons if any changes have been made,
because Access fires that event before it saves the record, which it has to
do before it can move to another.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thank you for your response, however there are no default values assigned
to
the tables or forms. The main form selects the employee. The main and
subforms are linked on the EmpID.

On the subform's current event, if there are no current matching records I
have it set to start a new record:

Private Sub Form_Current()

Dim varWeek As Variant

If Not IsNull(Me.Parent.EmpID) Then
If Me.NewRecord = True Then
varWeek = DMax("[Week#]", "[tblTraining]", "[EmpID] = '" &
Me.Parent.EmpID & "'")
Me.EmpID = Forms![frmUpdateTraining]!EmpID
Me.[Week#] = Nz(varWeek, 0) + 1
End If
End If
End Sub

On the before update event of the sub-form:
If Not IsNull(Me.EmpID) _
And Not IsNull(Me.[Week#]) Then
If IsNull(Me.Tot_Sales) Then
MsgBox "Enter the total sales.", vbOKOnly
Cancel = True
Else
If IsNull(Me.Day1) Then
MsgBox "Enter the totals for Day 1.", vbOKOnly
Cancel = True
End If
End If
End If

The validation from the Before Update event fired as soon as the new
record
is started from the On Current event. So I moved the validation from the
before update event to the close command button. However, that also
removes
the validation from the default navigation buttons.

Is there a way to add the validation to the default navigation buttons?
Or,
a way to re-create the "1 of 3" feature from the default navigation
buttons
(then I can disable the default and create command buttons)?

In a standard form and subform, the subform will not inherit the ID of
the
main form until you start entering something. What is triggering this?
Are
you assigning a value to the new record row of the subform
programmatically?
Entering a value? ...?

If the subform is based on a query that has multiple tables, there is a
problem where Access fires off a message saying it cannot save the record
before you have finished filling it in. The message is something about
not
being able to assign a value. The cause of the problem is that the other
table (not the one where you are actually trying to write this record)
has a
Default Value assigned. Access wrongly tries to assign the Default Value
to
the other table, and so gets itself in a knot because it should not be
trying to create a record in the other table. The solution is to remove
the
Default Value from all fields in the other table, and remove the Default
Value from any controls on the subform that are bound to fields from the
other table.