Forms and Subforms

[Shael]

I would like to create a form that has an Occupation drop down box at the top
of the form. The Occupation drop down has 10 values.

When the user selects a value from the Occupation drop down, I would like to
display all records in a table where Occupation = the selected value in the
drop down.

Should I employ a form/subform? If so, do I need to define a relationship?
Can I base the subform on a query and the form on a table? Or should both be
based on 2 different tables?

Any help is appreciated.

 

[Beetle]

Does the information you want to display all come from the same table, or
from two separate tables?

If it comes from two separate tables, then you would most likely use
form/subform.

If it all comes from the same table, then you would have one form, based on
a query, with an unbound combo box in the header or something similar.

As far as wether to base your forms on tables or queries, I prefer to use
queries because you can do things like sort records on a certain field, limit
records based on criteria, etc.

Post back if you have more questions

HTH

 

[Allen Browne]

You don't need a subform.

Make sure the combo is unbound (not bound to any field), and make sure its
Name property is not the same as any field in the form. For example, name it
cboFilterOccupation, not Occupation.

Use its AfterUpdate event procedure to set the Filter of the form. This kind
of thing:

Private Sub cboFilterOccupation_AfterUpdate()
Dim strWhere As String
If Me.Dirty Then 'Save any edits.
Me.Dirty = False
End If
Is IsNull(Me.cboFilterOccupation) Then
Me.FilterOn = False
Else
strWhere = "[Occupation] = """ & Me.cboFilterOccupation & """"
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub

Note that if the Occupation field is a Number field (when you open the table
in design view), you will need to remove the extra quotes:
strWhere = "[Occupation] = " & Me.cboFilterOccupation

 

[Shael]

Actually, I already have a query defined with all the data I need. So if I do
this using a single form based on a query, can I create a single form that
has a header portion that contains a banner and the unbound combo box, and a
datasheet in the detail section? Any time I have created a datasheet in a
form, the datasheet consumes the entire form.

 

[Shael]

If I'm using a single form, and the selection in the combo returns a number
of records, can I display all the records in a datasheet in the form?

You don't need a subform.

Make sure the combo is unbound (not bound to any field), and make sure its
Name property is not the same as any field in the form. For example, name it
cboFilterOccupation, not Occupation.

Use its AfterUpdate event procedure to set the Filter of the form. This kind
of thing:

Private Sub cboFilterOccupation_AfterUpdate()
Dim strWhere As String
If Me.Dirty Then 'Save any edits.
Me.Dirty = False
End If
Is IsNull(Me.cboFilterOccupation) Then
Me.FilterOn = False
Else
strWhere = "[Occupation] = """ & Me.cboFilterOccupation & """"
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub

Note that if the Occupation field is a Number field (when you open the table
in design view), you will need to remove the extra quotes:
strWhere = "[Occupation] = " & Me.cboFilterOccupation

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I would like to create a form that has an Occupation drop down box at the
top
of the form. The Occupation drop down has 10 values.

When the user selects a value from the Occupation drop down, I would like
to
display all records in a table where Occupation = the selected value in
the
drop down.

Should I employ a form/subform? If so, do I need to define a relationship?
Can I base the subform on a query and the form on a table? Or should both
be
based on 2 different tables?

Any help is appreciated.

 

[Shael]

I think I found my answer. I'll try a split form. This will apparently allow
me to maintain the format of the header portion of the form and display a
datasheet in the detail section.

If I'm using a single form, and the selection in the combo returns a number
of records, can I display all the records in a datasheet in the form?

You don't need a subform.

Make sure the combo is unbound (not bound to any field), and make sure its
Name property is not the same as any field in the form. For example, name it
cboFilterOccupation, not Occupation.

Use its AfterUpdate event procedure to set the Filter of the form. This kind
of thing:

Private Sub cboFilterOccupation_AfterUpdate()
Dim strWhere As String
If Me.Dirty Then 'Save any edits.
Me.Dirty = False
End If
Is IsNull(Me.cboFilterOccupation) Then
Me.FilterOn = False
Else
strWhere = "[Occupation] = """ & Me.cboFilterOccupation & """"
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub

Note that if the Occupation field is a Number field (when you open the table
in design view), you will need to remove the extra quotes:
strWhere = "[Occupation] = " & Me.cboFilterOccupation

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I would like to create a form that has an Occupation drop down box at the
top
of the form. The Occupation drop down has 10 values.

When the user selects a value from the Occupation drop down, I would like
to
display all records in a table where Occupation = the selected value in
the
drop down.

Should I employ a form/subform? If so, do I need to define a relationship?
Can I base the subform on a query and the form on a table? Or should both
be
based on 2 different tables?

Any help is appreciated.