variable subform values

[AccessQuestion]

I have a main form that is based upon a table that is generated via a
make-table-query..i.e. the values in this table that the main form points to
will vary day to day.

I also have a subform that shows a list of calendar dates in one column and
user entry column.

The way I intend this to work is to have the user enter a qty next to the
calendar date of their choosing (in the subform) for the main form item
displayed.

The business need is this: The main form displays the product number to be
shipped. The subform displays the calendar dates and the user just has to
enter the qty to be shipped for THAT product number (displayed in the main
form)
next to the desired the calendar date. The end result is that I have a list
of dates and qtys that the product will ship on.
Each different product number will have the same list of calendar dates but
the user will enter different qtys for different corresponding dates.

PROBLEM: I can not get the form to link such that the subform values vary
per different product number (displayed on the main form). The subform shows
the same value regardeless of the main form product number.
Also I am afraid that when I run the make-table-query to update the table
for the
main form that the links will be lost.

If anyone can a) understand this all this greek that I just type and b)
could offer advice, I would greatly appreciate it! Thanks in advance for
anyone's help!!
This is an awesome forum and it's helped me a lot in the past!

 

[Tom van Stiphout]

On Wed, 2 Dec 2009 08:03:01 -0800, AccessQuestion

I think one reason you have received good help is because you explain
very well.
There is a simple solution to your problem: the subform control needs
two properties set: LinkMasterFields and LinkChildFields.
Check for example the Northwind Orders form to see how this works.
Essentially it links one or more fields in the parent form to one or
more fields in the child form. In your case I am assuming both forms
have a ProductID, so put ProductID in both properties.
Note also that this automatically restricts the query that's the
RecordSource of the subform to those items with the parent's
ProductID. So your subform query can simply be:
select * from mySubformTable
and does not have to be:
select * from mySubformTable
where ProductID = Forms!myParentForm!ProductID

-Tom.
Microsoft Access MVP