Form & subForm Coordination

[Mark B]

I have a form and subform used for orders. The main Orders form tracks
customer information, and displays the parts order subtotal, taxes, delivery,
etc.

The subform is used to pick part numbers from a Products table, and add them
to an Orders Details table. In addition, unit price, quantity, discount and
taxes are calculated on each part number in the subform.

The subform calculates each items extended price based on (unit price *
quantity * discount). In addition, the subform sums the individual extended
price(s) in the subform footer, to be used in the Orders table as a sum of
the parts purchased on that order.

The problem I have occurs when a program user enters new data in a control
(lets say the quantity of a given item) and exits the subform by mousing to a
control on the parent form without first pressing enter or tabbing off the
updated control in the subform. When this occurs, the parent form “Parts
Total†does not update.

I have tried various events to transfer and update the information. Bottom
line is that the parent form information is not updated unless the user tabs
off or enters off the updated control.

Any advise on how to fix this?

 

[Allen Browne]

Mark, if you are storing the subtotal in the main Orders form, you are
making this much harder than it needs to be.

Instead just show the total on the main form, without storing it in the main
form's table. If you are not sure how to do that, open the Northwind sample
database, the Orders form, and see how it picks up the subtotal from the
subform.

If you are doing that and still not seeing the main form update fast enough,
you can force it with:
Me.Recalc

 

[dee]

I Allen,

I read this with interest. I posted on the 7th, (Complicated form question)
but nobody answered.

I guess my question would be - you can just store things on the form, but
not the table? I think I'm populating my tables with too much data.

My database is a small tutoring school one, but I tend to have tables with
lots of redundant information, such as the PK ID of the StudentName field,
etc. repeating in the various tables, such as ParentMeetings, StudentAwards,
StudentReports, etc., etc.

I have been looking at the Northwind database and see that they basically
seem to have one PK that relates to one FK pretty much in all tables. Is
this the standard, or is it just that the database doesn't contain many
tables?

I see that they repeat the OrderID, for example, very often in the queries,
though. But does that mean the form based on the query populates this field
each time in a table, or is the info saved in the form?

I'm pretty new to this and starting to understand, but obviously need some
coaching and advice!

Thanks for any you may offer!

 

[Allen Browne]

Data is only saved in tables. Forms don't save data.

When you create 2 tables with a one-to-many relationship, the primary table
has a primary key field (such as StudentID). The related table then has a
foreign key field, which is also the StudentID, and the StudentID might
appear many many times in the related table. For example, if student 34 gets
8 awards, there will be 8 records in the StudentAwards table where the
StudentID field contains 34.

You can create a query that combines several tables. In the query, you can
also create a calculated field. For example, in the rows of an invoice you
would have fields named Quantity and PriceEach. Then in the query, you would
calculate the amount by typing this into the Field row:
Amount: [Quantity] * [PriceEach]
You would NOT store the Amount in any table: it will always be a field that
is calculated in the query, and not stored anywhere. That's the important
part.

Here is a really basic article on calculated fields:
http://allenbrowne.com/casu-14.html

To read further on this large topic, search on normalization.

 

[dee]

Thanks for your quick response.

If you have a chance, would you mind referring to my post of the 7th
(complicated form question)? I didn't receive any responses to it. Or,
should I just re-post? Or just give up!?

thanks again!

--
Thanks!

Dee

Data is only saved in tables. Forms don't save data.

When you create 2 tables with a one-to-many relationship, the primary table
has a primary key field (such as StudentID). The related table then has a
foreign key field, which is also the StudentID, and the StudentID might
appear many many times in the related table. For example, if student 34 gets
8 awards, there will be 8 records in the StudentAwards table where the
StudentID field contains 34.

You can create a query that combines several tables. In the query, you can
also create a calculated field. For example, in the rows of an invoice you
would have fields named Quantity and PriceEach. Then in the query, you would
calculate the amount by typing this into the Field row:
Amount: [Quantity] * [PriceEach]
You would NOT store the Amount in any table: it will always be a field that
is calculated in the query, and not stored anywhere. That's the important
part.

Here is a really basic article on calculated fields:
http://allenbrowne.com/casu-14.html

To read further on this large topic, search on normalization.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.

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

I Allen,

I read this with interest. I posted on the 7th, (Complicated form
question)
but nobody answered.

I guess my question would be - you can just store things on the form, but
not the table? I think I'm populating my tables with too much data.

My database is a small tutoring school one, but I tend to have tables with
lots of redundant information, such as the PK ID of the StudentName field,
etc. repeating in the various tables, such as ParentMeetings,
StudentAwards,
StudentReports, etc., etc.

I have been looking at the Northwind database and see that they basically
seem to have one PK that relates to one FK pretty much in all tables. Is
this the standard, or is it just that the database doesn't contain many
tables?

I see that they repeat the OrderID, for example, very often in the
queries,
though. But does that mean the form based on the query populates this
field
each time in a table, or is the info saved in the form?

I'm pretty new to this and starting to understand, but obviously need some
coaching and advice!

Thanks for any you may offer!

 

[Allen Browne]

I didn't see that question.

Repost, but rephrase: sometimes people don't answer if they did not
understand the issue.