Combo Box to select a record and populate fields in a form

[Kathy]

I have created a form where I've created a combo box that has a drop down
list of box numbers where I want to be able to select one box number and have
it populate the other fields on the form.

On the combo box, I have entered the following for "after update", but it
doesn't allow me to select a record and doesn't populate any of the other
fields for that record.

Sub Combo74_AfterUpdate()
' Find the record that matches the control.
Me.Filter = "[BoxNo] = """ & Me.Combo74 & """"
Me.FilterOn = True
End Sub

What am I missing to make this work?

 

[Kathy]

I have created a form where I've created a combo box that has a drop down
list of box numbers where I want to be able to select one box number and have
it populate the other fields on the form.

On the combo box, I have entered the following for "after update", but it
doesn't allow me to select a record and doesn't populate any of the other
fields for that record.

Sub Combo74_AfterUpdate()
' Find the record that matches the control.
Me.Filter = "[BoxNo] = """ & Me.Combo74 & """"
Me.FilterOn = True
End Sub

Well, that code should find the desired record as long as
there is one that has its BoxNo Text field that matches the
BoundColumn of the combo box's selected row. If, as the
name implies, BoxNo is a number type field in its table,
then you should have gotten an error message, in which case
the code should not include the extra quote:
Me.Filter = "[BoxNo] = " & Me.Combo74

But there is nothing in the code to populate anything. Are
the text boxes that you want to populate using expressions
like =Combo74.Column(n) (the common way to display the
other values) or do you actually want to copy the values to
bound text boxes so thery are saved back to the table
(generally not a good idea).

--
Marsh
MVP [MS Access]
.
When I say I want to popluate the other fields, I just want it to pull up the rest of the fields from the same record as the BoxNo that is selected in the combo box. I do not want to copy anything. Are we talking about the same thing?

 

[Kathy]

Kathy wrote:

I have created a form where I've created a combo box that has a drop down
list of box numbers where I want to be able to select one box number and have
it populate the other fields on the form.

On the combo box, I have entered the following for "after update", but it
doesn't allow me to select a record and doesn't populate any of the other
fields for that record.

Sub Combo74_AfterUpdate()
' Find the record that matches the control.
Me.Filter = "[BoxNo] = """ & Me.Combo74 & """"
Me.FilterOn = True
End Sub


Well, that code should find the desired record as long as
there is one that has its BoxNo Text field that matches the
BoundColumn of the combo box's selected row. If, as the
name implies, BoxNo is a number type field in its table,
then you should have gotten an error message, in which case
the code should not include the extra quote:
Me.Filter = "[BoxNo] = " & Me.Combo74

But there is nothing in the code to populate anything. Are
the text boxes that you want to populate using expressions
like =Combo74.Column(n) (the common way to display the
other values) or do you actually want to copy the values to
bound text boxes so thery are saved back to the table
(generally not a good idea).

When I say I want to popluate the other fields, I just want
the BoxNo that is selected in the combo box. I do not
want to copy anything. Are we talking about the same thing?

No, we were talking about two different things. Displaying
the matching record's fields should be automatic as long as
the controls are bound to fields in the form's record source
table/query.

Did you determine if the type of the BoxNo field is Text or
Number?

Remember that it is common for a combo box to display some
text while it's value is a number used as a foreign key to a
table. Compare the combo box's BoundColumn and ColumnWidths
properties. Also check that the field in the combo box's
RowSource table/query corresponds to the field index in the
BoundColumn property.

The BoxNo field was a number. Thanks for all your suggestions. I was able
to get the form working.