Text box input based on a combo box selection

[Opal]

I have an inventory input form that has a combo box from which the
user selects the shift for which the inventory is being taken. On
this form I also have a sub form from which separate data is gathered
from the user, but the shift information is also needed. The form and
subform are bound to separate tables. I am using a subform to gather
the additional information to a separate table because the results are
used in a separate calculation. I only want the user to select the
shift information once. How can I populate the "shift" text box on
the subform with the same information selected from the "shift" combo
box on the main form?

I searched through some posts and found a macro that was supposed to
produce the desired results, but the post was 7 years old and when I
tried it I got an error. I would prefer to use VBA than a macro, but
not sure how to get it to work. Any suggestions?

 

[Morris]

I have an inventory input form that has a combo box from which the
user selects the shift for which the inventory is being taken. On
this form I also have a sub form from which separate data is gathered
from the user, but the shift information is also needed. The form and
subform are bound to separate tables. I am using a subform to gather
the additional information to a separate table because the results are
used in a separate calculation. I only want the user to select the
shift information once. How can I populate the "shift" text box on
the subform with the same information selected from the "shift" combo
box on the main form?

How about building the "OnChange" event?

Private Sub cmbShiftPattern_Change()

Me.frmSubForm.Form.txtSubFormShift = Me.cmbShiftPattern.Value

End Sub

 

[Ken Snell \(MVP\)]

How about building the "OnChange" event?

Private Sub cmbShiftPattern_Change()

Me.frmSubForm.Form.txtSubFormShift = Me.cmbShiftPattern.Value

End Sub

It's better to use the AfterUpdate event of the combo box, not the Change
event, for this type of purpose. Change event occurs if the user manually
types a character in the combo box's "textbox" display, which would mean an
incomplete value being written to the textbox in the subform.

 

[Opal]

It's better to use the AfterUpdate event of the combo box, not the Change
event, for this type of purpose. Change event occurs if the user manually
types a character in the combo box's "textbox" display, which would mean an
incomplete value being written to the textbox in the subform.
--

Ken Snell
<MS ACCESS MVP>- Hide quoted text -

- Show quoted text -

So Ken, you are recommending the same Private sub, but in the After
Update event?

 

[Opal]

It's better to use the AfterUpdate event of the combo box, not the Change
event, for this type of purpose. Change event occurs if the user manually
types a character in the combo box's "textbox" display, which would mean an
incomplete value being written to the textbox in the subform.
--

Ken Snell
<MS ACCESS MVP>- Hide quoted text -

- Show quoted text -

I am getting a compile error:

Private Sub cboShift_AfterUpdate()
Me.1ADieInLineTablesubform.Form.TxtSubfrmShift = Me.cboShift.Value
End Sub

The "Me.1A is highlighted and the message states Compile error:
Expected: =
Is something missing?

 

[Ken Snell \(MVP\)]

Yes.

--

Ken Snell
<MS ACCESS MVP>

So Ken, you are recommending the same Private sub, but in the After
Update event?

 

[Ken Snell \(MVP\)]

Is "1ADieInLineTablesubform" the actual name of the subform control on the
main form (open main form in design view, click on top border of subform
control, open Properties window, click on Other tab, and see what is in the
Name field). If not, use the name from the Name field in the code line.

 

[Opal]

Is "1ADieInLineTablesubform" the actual name of the subform control on the
main form (open main form in design view, click on top border of subform
control, open Properties window, click on Other tab, and see what is in the
Name field). If not, use the name from the Name field in the code line.

--

Ken Snell
<MS ACCESS MVP>









- Show quoted text -

Copied from the name field in the properties box:

1ADieInLineTablesubform

 

[Opal]

Is "1ADieInLineTablesubform" the actual name of the subform control on the
main form (open main form in design view, click on top border of subform
control, open Properties window, click on Other tab, and see what is in the
Name field). If not, use the name from the Name field in the code line.

--

Ken Snell
<MS ACCESS MVP>









- Show quoted text -

It doesn't like the "1A..." in the subform name. I changed it to
"OneA..." and now it works.

 

[Ken Snell \(MVP\)]

It doesn't like the "1A..." in the subform name. I changed it to
"OneA..." and now it works.

ACCESS does not like names that begin with a number. But, you should be able
to use the original name if you enclose it in [ ] characters:
Me.[1ADieInLineTablesubform].Form.TxtSubfrmShift = Me.cboShift.Value

Or this also should work:
Me![1ADieInLineTablesubform].Form.TxtSubfrmShift = Me.cboShift.Value

 

[Opal]

It doesn't like the "1A..." in the subform name. I changed it to
"OneA..." and now it works.

ACCESS does not like names that begin with a number. But, you should be able
to use the original name if you enclose it in [ ] characters:
Me.[1ADieInLineTablesubform].Form.TxtSubfrmShift = Me.cboShift.Value

Or this also should work:
Me![1ADieInLineTablesubform].Form.TxtSubfrmShift = Me.cboShift.Value
--

Ken Snell
<MS ACCESS MVP>- Hide quoted text -

- Show quoted text -

Thank you for all your help, Ken. It is appreciated.