help with subform behaving strange

[Mark Andrews]

Help!!!

I have an Access2003 db with a form called "frmEditAppointment" that has a
subform called "frmServicesBooked".
The form "frmEditAppointment" has information about an appointment and the
subform
"frmServicesBooked" is a datasheet used to select service, and enter qty,
rate etc...

When the user selects a service from the combobox a field txtColorID is
populated with column(4) of the combobox (underlying control source of
ColorID)
A field in the footer of the subform called txtServicesColor has this as
controlsource =nz(First([ColorID]),0)

So the idea is the user might add 5 rows of data for 5 different services
and each row would have a colorID value corresponding to the color used for
the service

The txtServicesColor field takes the first value and that value is used to
adjust a combobox on the main form.
I want the color of the first service added

The weird part is if I set a breakpoint in the AdjustColorofAppt subroutine
the logic seems to always work. If I do not set a breakpoint
the logic of AdjustColorofAppt doesn't work right. Basically looking for
help on what to try or diff ways to approach issue.


Here is code in the form "frmServicesBooked"

Option Compare Database
Option Explicit

Private Sub ComboServiceID_AfterUpdate()
On Error GoTo Err_ComboServiceID_AfterUpdate
Me.txtRate.Value = Me.ComboServiceID.Column(3)
Me.txtDurationMinutes.Value = Me.ComboServiceID.Column(4)
Me.txtColorID.Value = Nz(Me.ComboServiceID.Column(5), 0)
Err_ComboServiceID_AfterUpdate:

End Sub

Private Sub Form_AfterInsert()
Call AdjustColorofAppt
End Sub

Private Sub Form_AfterUpdate()
Call AdjustColorofAppt

End Sub

Private Sub Form_BeforeInsert(Cancel As Integer)
On Error GoTo Err_Form_BeforeInsert

If (Nz(Forms!frmEditAppointment.ComboCustomer, 0) = 0) Then
MsgBox "You must select a customer before adding services",
vbOKOnly, "Services"
Cancel = True
Else
Me.txtEventID = Forms!frmEditAppointment.txtEventID
Me.txtCustomerID = Forms!frmEditAppointment.ComboCustomer
End If

Err_Form_BeforeInsert:

End Sub


Private Sub Form_Open(Cancel As Integer)
On Error GoTo Err_Form_Open
Me.Controls("txtServiceBookedID").ColumnHidden = True
Me.Controls("txtEventID").ColumnHidden = True
Me.Controls("txtCustomerID").ColumnHidden = True
Me.Controls("txtDurationMinutes").ColumnHidden = True
' Me.Controls("txtColorID").ColumnHidden = True

Me.txtTaxRate = GlobalTaxRate
Err_Form_Open:
End Sub


Private Sub AdjustColorofAppt()
On Error GoTo Err_AdjustColorofAppt
'optionally set label color (based on service(s) selected)

If (CLng(Nz(Me.txtServicesColor, 0)) > 0) Then
Forms!frmEditAppointment.cmbLabel = Me.txtServicesColor
Forms!frmEditAppointment.lblSample.Visible = True
Forms!frmEditAppointment.lblSample.BackColor =
Forms!frmEditAppointment.cmbLabel.Column(2)
End If


Exit_AdjustColorofAppt:
Exit Sub

Err_AdjustColorofAppt:
MsgBox Err.Description
Resume Exit_AdjustColorofAppt

End Sub