Hi, LauraLee,
I assume you are experiencing this behavior in a continuous form.
Unfortunately, this is just the way Access works. The workaround strategy is
to:
- use a textbox rather than a combo box on your main form
- use its OnGotFocus event to open a 2nd auxiliary form that has a single
combo box control
- use its AfterUpdate event to set any control values back on the main form
The following shows the technique, with an added wrinkle. The Row Source of
the combo box on the auxiliary form is determined by a combo box value on the
main form. After the user selects the steel type, the sizes associated with
that type display in the auxiliary form combo box, using the value from the
main form in the WHERE criteria of the Row Source.
‘ Textbox control on continuous form. User has just selected a steel type
in cboSteelType
‘ Open the auxiliary form.
Private Sub txtSteelSize_GotFocus()
On Error Resume Next
DoCmd.OpenForm _
FormName:="frmSelectSteelSize", _
View:=acNormal, _
WindowMode:=acDialog
Me!txtResult.SetFocus
End Sub
‘ Combo box of Auxiliary form
Row Source = SELECT tblSteelSizes.SteelSizeID, tblSteelSizes.SteelType,
tblSteelSizes.SteelSize, tblSteelSizes.LBPerLF, tblSteelSizes.SFPerLF FROM
tblSteelSizes WHERE
tblSteelSizes.SteelType=Forms!frmSteelTakeoff!sbfSteelTakeoff.Form!cboSteelType ORDER BY tblSteelSizes.SteelSize;
‘ After selection, set controls on the main form, and close the auxiliary form
Private Sub cboSteelSize_AfterUpdate()
Forms!frmSteelTakeoff!sbfSteelTakeoff.Form!txtSteelSizeID = Me!cboSteelSize
Forms!frmSteelTakeoff!sbfSteelTakeoff.Form!txtLBperLF =
Me!cboSteelSize.Column(3)
Forms!frmSteelTakeoff!sbfSteelTakeoff.Form!txtSFperLF =
Me!cboSteelSize.Column(4)
DoCmd.Close
End Sub
It’s awkward I know, but I don’t think there’s a better way. AND it works….
HTH
Kevin Sprinkel
