Text box input based on a combo box selection

  • Thread starter Thread starter Opal
  • Start date Start date
O

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?
 
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?
Click to expand...

How about building the "OnChange" event?

Private Sub cmbShiftPattern_Change()

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

End Sub
 
Morris said:
How about building the "OnChange" event?

Private Sub cmbShiftPattern_Change()

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

End Sub
Click to expand...


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.
 
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 -
Click to expand...

So Ken, you are recommending the same Private sub, but in the After
Update event?
 
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 -
Click to expand...

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?
 
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.
 
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 -
Click to expand...

Copied from the name field in the properties box:

1ADieInLineTablesubform
 
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 -
Click to expand...

It doesn't like the "1A..." in the subform name. I changed it to
"OneA..." and now it works. :-)
 
Opal said:
It doesn't like the "1A..." in the subform name. I changed it to
"OneA..." and now it works. :-)
Click to expand...

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
 
It doesn't like the "1A..." in the subform name. I changed it to
"OneA..." and now it works. :-)
Click to expand...

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 -
Click to expand...

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