Combo box used in detail record

[zombeerose]

I have created 2 forms - 1) Master form for selecting a
person and 2) Sub form for entering 1+ time logs for the
selected individual. On the Sub form, the detail section
can display multiple records. Per each detail record, a
job can be assigned as well as a corresponding job step.
Both the job and job step can be selected from drop-down
combo boxes.

I have written code to update the rowsource for my job
step combo box when the job combo is changed. The problem
is that when a user selects a different job from the combo
box, the row source for EVERY job step combo box is
changed rather than the corresponding detail record.

Code for updating rowsource:
"Private Sub JobIDCombo_Change()
On Error GoTo JobIDCombo_Change_Err

Dim JobID As Long
JobID = JobIDCombo.Column(1)

If JobID > 0 Then
JobStepCombo.RowSource = "SELECT Description FROM
[JobStep] WHERE JobID = " & JobID
Else
JobStepCombo.RowSource = ""

End If
JobStepCombo.Requery

Exit Sub

JobIDCombo_Change_Err:
MsgBox Error$
End Sub"

Any help is greatly appreciated.

 

[Marshall Barton]

I have created 2 forms - 1) Master form for selecting a
person and 2) Sub form for entering 1+ time logs for the
selected individual. On the Sub form, the detail section
can display multiple records. Per each detail record, a
job can be assigned as well as a corresponding job step.
Both the job and job step can be selected from drop-down
combo boxes.

I have written code to update the rowsource for my job
step combo box when the job combo is changed. The problem
is that when a user selects a different job from the combo
box, the row source for EVERY job step combo box is
changed rather than the corresponding detail record.

Code for updating rowsource:
"Private Sub JobIDCombo_Change()
On Error GoTo JobIDCombo_Change_Err

Dim JobID As Long
JobID = JobIDCombo.Column(1)

If JobID > 0 Then
JobStepCombo.RowSource = "SELECT Description FROM
[JobStep] WHERE JobID = " & JobID
Else
JobStepCombo.RowSource = ""

End If
JobStepCombo.Requery

Exit Sub

The issue you're struggling with is that there is only one
combo box for all the records you see. Since each control
only has one set of properties, a continuous form just
paints the screen using those properties, with the nice
exception that bound controls display data for their
corresponding records. What all that boils down to is that
to display a different value on different rows, the control
must be bound to a field in the form's record source, either
directly or through an expression.

With that in mind, the way to get each row to display the
combo box's value is to add a text box bound to the same
field as the combo box. Size and position the text box so
that it covers the text part of the combo box. Set the text
box's Locked property to Yes and use its GotFocus event to
immediately reset the focus to the combo box.

 

[zombeerose]

Thanks Marshall for the help.

-----Original Message-----

I have created 2 forms - 1) Master form for selecting a
person and 2) Sub form for entering 1+ time logs for the
selected individual. On the Sub form, the detail section
can display multiple records. Per each detail record, a
job can be assigned as well as a corresponding job step.
Both the job and job step can be selected from drop-down
combo boxes.

I have written code to update the rowsource for my job
step combo box when the job combo is changed. The problem
is that when a user selects a different job from the combo
box, the row source for EVERY job step combo box is
changed rather than the corresponding detail record.

Code for updating rowsource:
"Private Sub JobIDCombo_Change()
On Error GoTo JobIDCombo_Change_Err

Dim JobID As Long
JobID = JobIDCombo.Column(1)

If JobID > 0 Then
JobStepCombo.RowSource = "SELECT Description FROM
[JobStep] WHERE JobID = " & JobID
Else
JobStepCombo.RowSource = ""

End If
JobStepCombo.Requery

Exit Sub

The issue you're struggling with is that there is only one
combo box for all the records you see. Since each control
only has one set of properties, a continuous form just
paints the screen using those properties, with the nice
exception that bound controls display data for their
corresponding records. What all that boils down to is that
to display a different value on different rows, the control
must be bound to a field in the form's record source, either
directly or through an expression.

With that in mind, the way to get each row to display the
combo box's value is to add a text box bound to the same
field as the combo box. Size and position the text box so
that it covers the text part of the combo box. Set the text
box's Locked property to Yes and use its GotFocus event to
immediately reset the focus to the combo box.