Creating form/subform - drop down in main ?

[+Bob+]

I have a master table of categories and a related table with records
that each have a category (one to many).

I would like to use a drop down in the main form (with the categories
listed) and when I choose a category, have the related table records
appear below in a subform.

I can create the main/subform with the Form Wizard but the field type
for the Master field comes out as a text box. How can I rearrange this
to be a drop down box and have it pull the records below?

Thanks,

 

[Graham Mandeno]

Hi Bob

For your subform control, set LinkMasterFields to the name of your combo box
and set LinkChildFields to the name of the related"category" field in your
subform's record source.

 

[+Bob+]

Hi Bob

For your subform control, set LinkMasterFields to the name of your combo box
and set LinkChildFields to the name of the related"category" field in your
subform's record source.

Graham:

What I am still confused about is how to create this scenario. Should
I use the wizard (only creates a text box in the main form) and try to
convert that text box into a combo ? Or should I create a main form
that lets me look through the Category (master) file with a combo box
drop down and then drag-n-drop the other form onto it ? Or perhaps
there is some other technique that will get me where I need to go?

Thanks,

 

[Graham Mandeno]

Hi Bob

You have two ways to approach this: subform or filter. The subform method
is what you are already using, so I didn't mention the filter method, but I
think I would prefer it.

[Note: I am assuming your Category table has two fields: CategoryID
(numeric) and CategoryName (text)]

With the subform, no code is required. Your main form should be unbound
(RecordSource is blank). The wizard is no use for creating unbound forms,
so just create one from scratch. Add a combo box with the following
properties:
Name: cboCategory
ControlSource: (blank - unbound)
BoundColumn: 1
ColumnCount: 2
RowSource: Select CategoryID, CategoryName from Category order by
CategoryName;
ColumnWidths: 0 (this hides the first column)

Now drag-n-drop your subform onto the main form. Set the following
properties for the subform control:
LinkMasterFields: cboCategory
LinkChildFields: <the name of the related "category" field on the
subform>

With the filter method, use your current subform as the main form. Add a
combo box to the form header as above, and add the following code for its
AfterUpdate event procedure:

Private Sub cboCategory_AfterUpdate()
If IsNull(cboCategory) then
Me.FilterOn = False
Else
Me.Filter = "[<name of category field>]=" & cboCategory
Me.FilterOn = True
End If
End Sub

You could also add a "Show All" command button with the following code in
its Click event:

Me.FilterOn = False
cboCategory = Null
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

 

[+Bob+]

Hi Bob

You have two ways to approach this: subform or filter. The subform method
is what you are already using, so I didn't mention the filter method, but I
think I would prefer it.

Thanks Graham. I will give this a shot and get back with any
questions.

Bob