Child field on subform doesn’t autofill same data

B

BigAl

My master form (Invoice) has 2 subforms (Parts) & (Labor) all linked by
“invoice number†fields. SubformParts uses a query linking PartsUsage table
to PartsInventory table by “part_id†field on both; SubformLabor uses
LaborUsage table.
After I open the Invoice form for data entry and populate the “invoice
number†field, only the SubformLabor autofills the same “invoice number†but
not until I enter some data on any field other than “invoice number†on
SubformParts before it autofills the Master’s “invoice numberâ€. I have
checked everything I know (not much) with no avail, what did I miss?
Thanks for any help
 
A

Allen Browne

Your subform should *not* autofill the number until after the new record has
been started.

That indicates that there is something wrong with your SubformParts, where
it is dirtying the record too early. For example it would do that if you had
something in the form's Current event that is assigning a value to a bound
control.
 
B

BigAl

Allen, do you mean to say that there is something wrong with my SubformLabor
(instead of Subform Parts)? Because it autofills soon after I populate master
field, I thought that's the norm. Could SubformParts using query have
something to do with no autofill?
Thanks
 
B

BigAl

Allen, I don't have any On Current Event for either subform. Just for
testing, I changed the SubformParts to a single table instead of a query, it
autofills just like SubformLabor. But I need to use query thou.
Thanks
 
A

Allen Browne

Is this query a single-table query?
If there's more than one table, could you have the ID from the wrong table
in this query, so that it is showing you something different than you think?

Is there something in the Default Value of any of the fields in the table(s)
in the query? Does removing this make a difference?
 
B

BigAl

SubformParts uses a 2-table query (PartsUsage table relating to
PartsInventory table via "part_id" field), PartsUsage table has the "invoice
number" field as the child field linking to master form's master field
"invoice number". PartsInventory table doesn't have "invoice number" field at
all. There is no default value on any field.
 
A

Allen Browne

Okay, you have:

a) main form bound to Invoice table, which has an Invoice Number field
(primary key) which you enter (not AutoNum.)

b) SubformLabor, bound to LaborUsage table which has Invoice Number field as
foreign key.

c) SubformParts, bound to a query that contains the PartsUsage and
PartsInventory tables. These tables are joined on PartID. PartsUsage
contains the Invoice Number (foreign key).

If you open the main form in design view, right-click the edge of the
subform control and choose Properties, on the Data tab of the Properties box
you see the Link Master Fields and Link Child Fields properties. I assume
that both subform controls have [Invoice Number] beside both properties?

I am not sure what to suggest, because the subform should not autofill its
primary key as soon as a new record is started in the main form. This does
indicate a problem with the way the subform is connected to the main form.
You would expect this problem when the main form and subform are bound to
the same table, or when some code is triggering the assignment of the value.
Other than that, I don't know what to suggest.
 
B

BigAl

You had the perfect ficture on my design (simple one) but also puzzled by the
problem, I guess my user can live with this anyway.
Just for giggle, I have sub_total fields on my main form to pull the totals
from each subform with the condition of RecordsetClone.RecordCount=0 or blank
if no record, guess what? it doesn't work for SubformParts total (because
query?) IF the program is run with other Access than Access XP!
Is MS Access great or what? (peeling apple with a butcher knife?)
Thanks for your time and have a Happy New Year.
--
BA


Allen Browne said:
Okay, you have:

a) main form bound to Invoice table, which has an Invoice Number field
(primary key) which you enter (not AutoNum.)

b) SubformLabor, bound to LaborUsage table which has Invoice Number field as
foreign key.

c) SubformParts, bound to a query that contains the PartsUsage and
PartsInventory tables. These tables are joined on PartID. PartsUsage
contains the Invoice Number (foreign key).

If you open the main form in design view, right-click the edge of the
subform control and choose Properties, on the Data tab of the Properties box
you see the Link Master Fields and Link Child Fields properties. I assume
that both subform controls have [Invoice Number] beside both properties?

I am not sure what to suggest, because the subform should not autofill its
primary key as soon as a new record is started in the main form. This does
indicate a problem with the way the subform is connected to the main form.
You would expect this problem when the main form and subform are bound to
the same table, or when some code is triggering the assignment of the value.
Other than that, I don't know what to suggest.
Click to expand...
 
Top