Use a subform?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have been trying to figure this out, and it seems so simple.

I have Table A with a product series ID (for a group of products).
I have Table B with the various products for each group, with a field that
relates to the "product series ID" in Table A.

I would like the user to have a a selection box at the top of the form from
which they could select the desired Product Series (a combo box would be
nice). Once the Product Series has been selected, I would like all available
products in that series to show up as a continuous form or datasheet view on
the same form. They would then double-click on the desired product to open
another window.
I can't seem to get the first part going. Is this a use for a subform?
Thanks ahead!
 
You could use a mainform and subform or you could use just a mainform with a
continuous form view.

You could:
1. use a mainform and subform with a datasheet view on the subform
2. use a mainform and subform with a continuous view on the subform
3. use a single form that is continuous view.

Assuming a single form with continuous view:
Create a combo box (called ComboBoxName) in the form header whose row
source is something like "SELECT ProductSeriesID FROM TableA"
In the after update event of the combo box change the Record source of the
form to something like:

Me.RecordSource = "SELECT * FROM TableA WHERE ProductSeriesID = " &
me.ComboBoxName
(Assuming Product Series ID is numeric).


If you used a subform, essentially do the same thing:
Create a combo box called ComboBoxName on the main form using the same row
source as above.
Create your subform.
In the after update event of the combo box change the recordsource of the
subform.
Me.frmInvoiceMain.Form.RecordSource = "SELECT * FROM TableA WHERE
ProductSeriesID = " & me.ComboBoxName
 
Be aware, that if you use either of the above approaches to create a
form that you are going to add records with, YOU will be responsible
for the code in the beforeinsert event to load the ProductSeriesID
into the created record.

Another approach.

create main form with combo with the base query as Bill indicates.
Create subform for the table B records (the Product SeriesID must be
selected but does NOT have to show on the form.)
In the Data Tab of the properties for the subform put the combobox
name in the parent space and the productseriesID name in the child
space. (The wizard will not work, however you can enter the fields
manually.)

You wil now have a parent child relationship AND if you add records
using this subform, the ProductSeriesID field will be automatically
maintained.

Ron
 
I was just checking in to reply to Bill that his single form method worked
like a charm. I do not intend users to have the ability to add any records
from this form. So I'm assuming if that's the case, that the above should
work fine?

If records are to be added by specific users, that will be done through a
separate form for maintenance users only.

I cannot express how thankful I am for your help! Thanks so much Bill & Ron.
Louise
 
Back
Top