I have a form that displays and allows input of orders for my screen printing company. The subform displays the line items. On the main form, i have a field whose control source is a calculated field in the footer of the subform (it adds up the # of products on the line items). In the past, when a new invoice was started, that field would show 0 until line items were added. The line items are added via a popup form (there is much info that goes on a line item that doesn't display on the main order page). I have had a problem with my salespeople adding line items by typing directly into the new record displayed on the subform, which is FORBIDDEN!! So I recently set the allowadditions property of the subform to False. I still want them to be able to edit field on existing lines, just not add new ones. "Problem solved!" he says to himself. Except now when a new invoice is started, my little total box says #Error instead of 0. This isn't just annoying; it causes a runtime error when we try to use the popup to add line items.
I have tried to explain this thoroughly, but it was probably more confusing than anything. Can anyone help me with this little quandary?

Is it possible to post a cut the forms and some dummy data so we can have a look?

One question: Why is it FORBIDDEN (thunder and lightening effects added for mood - use your imagination) to add directly to the subform?

Getting #Error means that it can'tcomplete the calculation for some reason eg not all values are there.

Second Question: What exactly is the runtime error?

Haz

Answer 1) there are many more pcs of data that need to be added to each line item than can (or should) be displayed on the subform itself.
Answer 2) "You entered an expression that has no value". It happens because before you can add line items, Access has to save the main record. My form shoves the calculated value displayed onscreen (in the tbTtlPcs box) into the field that saves the number of pieces (nTtlPcs). It hangs here -

If Nz(Me.tbTtlPcs, 0) = Nz(nTtlPcs, 0) Then

nTtlPcs is null at this point, but tbTtlPcs "has no value". It kills me that null and "no value" aren't the same!
It also kills me that if there are no line items, but there is an EMPTY ROW OF BOXES (when allowadditions is set to true) it behaves differently!



how would I do this?

Thanks!

Here's a screenshot...

Re: answer 1 - thats what i figured. naughty sales persons

To answer your question grab a copy of the database and take out everything that doesnt relate to this problem and for data security add a few dummy records that can be played with (2 or 3) rather than give us real data and then zip it up. When making your next post make sure the "preview my post andor attach a file is clicked" and then on the next screen attach the zip file.

Boy Howdy, if I can avoid doing that I sure would like to. The database is split into front/back end. Also, that form relies on so many tables it would take me a long time to do what you're suggesting. Check out my attached relationships (should I be proud or ashamed? )

This does not exactly answer your question but you have repeating groups in your subform, which you should not. SM, MED, LG, etc should not be fields in a record but records in a related table. Adding a line item is easy if they are records and not fields.

You talk about saving a total and this is something that you do not do. You calculate totals as you need then and never store them. If a record changes the total can be wrong....

I do not have a speciific suggestion for you but you can use code where you do your calculations to see if the table/query that is the Record Source of the subform has records and if it does not you can show 0 in an unbound control on the form.

One last thing and that is that if your structure is correct your users should be able to enter data easily into your subform(s) and you should not have to popup another form for data entry.

Just my 3 cents worth.....

Jack

Okay fair enough. I just saw your screenshot of the form itself i can see form that its would be a hard ask.

Well its common practice not to store calculated totals in a table. Do you really need to store the number of pieces at all? When you can run the calculation whenever you want.

But for the moment we will run with the need to store. What we need is a way to see if there are any records in the subform to start with.

If we clone the recordset that runs the subform and check it for records relating to that invoice number. If we dont find any then we just set tbTtlPcs= 0 before this line:

If Nz(Me.tbTtlPcs, 0) = Nz(nTtlPcs, 0) Then

it should no longer hang

Haz.

Jack, please explain how these are repeating groups. For my mind each should be a different order line eg small on one line, medium on another etc. But i dont see how they should be in related table.. at least not yet i dont.

edit -
Penny drops!! oh i see what you mean (time for lunch i think). Its a radical change to his design and data though (considering he already has 12000 records.

Edited by: hazpak on Tue Mar 15 19:22:24 EST 2005.

Edited by: hazpak on Tue Mar 15 19:24:38 EST 2005.

Haz -

Yep, if he has 12,000 records then he has a problem with restructuring his database, but I only thought it right to let him know that some of his problems are because the data is not normalized. Now and forever he is going to have to find work-arounds to the normalization problem and it usually gets worse as the db evolves, not better.

I'm glad the penny dropped and that it is lunch time for you as it is grog time for me! The sun is over the yardarm so I am off to the wine cellar....

Jack

Hey guys -
thanks for the interest in my problem. Regarding normalization, there are a few reasons I have it set up like that...
S,M,L, and XL are always the same cost and price, and almost every record that has product on it has more than one of these sizes (i.e. 6S, 12M, 12L, 24XL).
Products over XXL are normalized; they get their own record. Instead of being "50/50 Tee" they are "50/50 Tee - 4X" and are the only size on their line.
There is only one color per record, as there are potentially infinite colors available. However, there are really only a very few possible sizes. Not sure what the functional advantage would be; I've already got 40,000 line items; I would probably end up with close to 100,000 "subline" items, each with a primary key, the line item key, the size name, and the size quantity. To me that seems "over-normalized".
As for saving the total, on occasion we assign a different total to an order than what the computer would come up with, for scheduling or pricing purposes.
Overall, flawed though it may be, it has worked for us since 2000.
Haz and Jack, thanks for the suggestion regarding checking for records prior to the break. I got so fixated on the lunacy of the allowadditions property making a difference that I didn't just think to work around it.

As for the popup form to add a line item, I set it up that way partially because if all that info was shown in the subform you wouldn't be able to see more than one or two line items at a time, and it would be harder to see at a glance if you had missed any items while entering them. Also, there are calculated fields and programatically filled combo boxes, and in Access 97 at least, that was hard (impossible?) to pull off with continuous forms.
I do appreciate the suggestions and critique of the database, and welcome your response to these points as well.
Thanks guys!

Having thought about it some more. It is logical that you would get an error as you have even if it is lunacy. When allowadditions is turned off you don't get the placeholder record (thats what i can the blank ready to enter record). If the placeholder record was present while it has no values to count at least it have something to count even if it is nothing. With no placeholder there is nothing to count at all therefore the field has cannot have a value.

I hope that makes sense somehow as i think its time for me to go join Jack at the nearest wateringhole aka the pub!!

In regards to the price being stored in your case its a victory of a business rule over best practice. Sometimes they just aren't compatible.

With regards to calculated fields and programmed combo boxes in a continuous form i feel your pain. but I dont think its impossible just requiring of a bit of programming and a lot of refreshes.

Anytime,
Haz.

John -

We can't argue with success. If the db has worked flawlessly for you for 5 years then there is certainly no reason to change it. The fact is that your data is not normalized and though normalizing the data makes for more records that is the nature of a relaional database. I thougth that you db was a work in progress so that is the reason for my comments on normalizing your data. Now you know about normalization and when you create your next database you will know that repeating groups are frowned upon.

Continued success!!

Jack