How To Filter Combo Box Items And Display First Item

[Guest]

I have two tables, say, TblA and TblB. There is a one-to-many relationship
between TblA and TblB. I have a form where I can navigate between each
record in TblA. (So, that works fine.) In the form, I have a combo box
where I want to display the records in TblB that are associated with the
current record from TblA. However, the combo box displays ALL the records in
TblB, not just those to the current record in TblA. I’m sure there is an
easy solution, but I can’t seem to figure it out.

Also, is there a way to have the first returned record from TblB displayed
in the combo box display? Right now, the combo box appears blank. However,
when I click the down arrow, I see the records.

Any advice would be greatly appreciated. Thanks in advance.

 

[John W. Vinson]

I have two tables, say, TblA and TblB. There is a one-to-many relationship
between TblA and TblB. I have a form where I can navigate between each
record in TblA. (So, that works fine.) In the form, I have a combo box
where I want to display the records in TblB that are associated with the
current record from TblA. However, the combo box displays ALL the records in
TblB, not just those to the current record in TblA. I’m sure there is an
easy solution, but I can’t seem to figure it out.

Also, is there a way to have the first returned record from TblB displayed
in the combo box display? Right now, the combo box appears blank. However,
when I click the down arrow, I see the records.

Any advice would be greatly appreciated. Thanks in advance.

Do you really want a combo box? If you want to be able to see all the fields
in TblB associated with each record in TblA, and edit them, a continuous
Subform would be a more appropriate tool.

That said... if you do want a combo box to be able to *select* (but not edit)
one associated record from TblB (and presumably use VBA code to do something
with that selection), base the combo box on a Query referencing a control on
the form bound to the linking field:

=Forms![yourformname]![controlname]

as a criterion on the linking field.

If this still shows blank, please post the following properties of the combo:

RowSource (post the SQL view)
Control Source
ColumnCount
ColumnWidths

John W. Vinson [MVP]

 

[Guest]

Thanks, John. For this situation, I have many other fields that I am
displaying on the form and am trying to display muliple dropdown lists
grouped together, hence, not being able to use subforms since they take up so
much room on a form, from what I've seen. So, just displaying the items in a
dropdown list is what I want, or at least that effect. The user will use a
different form to input or modify any items in the list. I know this is
different than the usually usage of the combo box. Thanks again for your
help.

I have two tables, say, TblA and TblB. There is a one-to-many relationship
between TblA and TblB. I have a form where I can navigate between each
record in TblA. (So, that works fine.) In the form, I have a combo box
where I want to display the records in TblB that are associated with the
current record from TblA. However, the combo box displays ALL the records in
TblB, not just those to the current record in TblA. I’m sure there is an
easy solution, but I can’t seem to figure it out.

Also, is there a way to have the first returned record from TblB displayed
in the combo box display? Right now, the combo box appears blank. However,
when I click the down arrow, I see the records.

Any advice would be greatly appreciated. Thanks in advance.

Do you really want a combo box? If you want to be able to see all the fields
in TblB associated with each record in TblA, and edit them, a continuous
Subform would be a more appropriate tool.

That said... if you do want a combo box to be able to *select* (but not edit)
one associated record from TblB (and presumably use VBA code to do something
with that selection), base the combo box on a Query referencing a control on
the form bound to the linking field:

=Forms![yourformname]![controlname]

as a criterion on the linking field.

If this still shows blank, please post the following properties of the combo:

RowSource (post the SQL view)
Control Source
ColumnCount
ColumnWidths

John W. Vinson [MVP]