form subform design question

[tom]

I have a calculated control on the main form. let
us call it billID.

On the subform based on a table there a field called BillID. I want the
easiest method to update the BillID on the table which the subform is based
to the BillID
on the calculated control on the main form.

I guess there is probably a gazillion ways to do this,
but what is the easiest, cleanest method. I do not
need efficiency.

 

[John Vinson]

I have a calculated control on the main form. let
us call it billID.

On the subform based on a table there a field called BillID. I want the
easiest method to update the BillID on the table which the subform is based
to the BillID
on the calculated control on the main form.

I guess there is probably a gazillion ways to do this,
but what is the easiest, cleanest method. I do not
need efficiency.

More explanation needed!

Why is BillID a *CALCULATED* field?
Do you want to *update* existing records on the subform, changing the
value of BillID? Or just to ensure that any new records inherit the
calculated BillID?
What is the relationship between the tables on the mainform and
subform?
What are those tables' Primary Keys?
What are the Master Link Field and Child Link Field properties of the
subform?
Where else (if anywhere) is the BillID stored?

John W. Vinson[MVP]

 

[tom]

The user inputs the invoice number and the and a second
control number into the main form. from this using
Dlookup the form finds the BillID which is in a table
that the form does not find its data from.

The subform is linked using a key that creates
a one to many relationship between the main form
and the subform. One of the fields on the table
on which the subform is based is called Billid.
I want this field to update to the calculated
BillID on the main form.



The two tables are linked by a key that has nothing to
do with the abov

 

[John Vinson]

The subform is linked using a key that creates
a one to many relationship between the main form
and the subform. One of the fields on the table
on which the subform is based is called Billid.
I want this field to update to the calculated
BillID on the main form.

One way would be to make the calculated control the Master Link Field
and the BillID field the Child; both link field properties can consist
of multiple fields separated by semicolons. You can for instance set
the MasterLinkField to its current value, a semicolon, and the name of
the textbox containing the calculated bill ID.

I find it confusing that the mainform is using the bill ID (from
another table yet!!) but doesn't actually contain that field. Sounds
like potential integrity trouble to me!!

John W. Vinson[MVP]

 

[tom]

This is an adjustment form, so my idea is to have
a table of adjustment numbers, and then a table
for each adjustment grouped by adjustment numbers.

The billID in the main program is hidden from the user,
so the only way to retrieve it easily is to enter the
invoice number and a second control number to
retrieve the bill.

This is a work in progress, which so far seems to be
working if I manually plug in the bill id into
table after dlookup retrieves it.

One problem with your solution, I think, is that
some records in the main form have no billID
associated with them. If this is not a problem,
I will try your solution on Monday.

By the way the main form is simply retrieving
the BillID, it is not using it. The adjustment
table used by the subform is later used in another query where the bill ID
is sometimes needed.