Field Formatting (A2K)

[AGMCon]

This is just a small thing that deals with field formatting.

I have a SubForm called: subFormReturned
which contains a field in the footer called; txtQReturned
with the datasource of txtQReturned being; =Sum(NZ([Inv_Returned_Qty]))

The field is then displayed on the main form as =([SubFormReturned].[Form]![txtQReturned])

If there is data in this field in the subform it displays correctly on the main form.
If not, I get the #Error indicator.

It does however display correctly on the subform if there is no data, so the NZ is being lost in the translation.

I thought that the NZ in the original field would have made it a happy camper, but not so.

I'd appreciate any suggestions,

[HansV]

What happens if you change the control source of txtQReturned to

=Nz(Sum([Inv_Returned_Qty]),0)

[AGMCon]

'Morning Hans,

unfortunately nothing.

[Francois]

I can't reproduce your error as you can see in the little attached demo.
Just an idea : have you something in the format property of the textbox on the main form ?

[AGMCon]

Francois,

Very bizarre! I compared both yours and mine and found no differences, almost.
So, I renamed all of your fields to match mine and mine still crashes.
The only difference, including formatting, is that in yours it looks like this =[SubFormReturned]!txtQReturned
and in mine when I tried to use the exact format, it comes back as =[SubFormReturned]![txtQReturned] with the extra square brackets.
I'm obviously missing something obvious, obviously!

[AGMCon]

Francois,

Just to double check my stuff I put together the attached document. Still baffled!

[HansV]

You're not getting any further in this thread or in the one about Inventory Calculation. If I were you, I'd try very hard to create a stripped down copy so that Loungers can look at the problem directly. See <post#=401925>post 401925</post#> for instructions (converting to Access 97 usually reduces the size significantly)

[Francois]

The only manner I can get the error is when in the subform, the textbox containing the quantity has not the same name as the underlying field.
If in the summing box in the footer of the subform, I use the name of the textbox, I get the error. If I use the name of the field, I don't get the error.

[AGMCon]

Francois,

This sounds weird, but I'm glad you got the error as well. I don't feel so out in left field. I'll try your manipulations to see if I can bail myself out. Out of desperation I tried first, importing your tables and forms into my database and vice versa and still got my original errors. Thanks much for looing at this,

[AGMCon]

Hans,

Thanks for the suggestions. I did try to reduce that which I have into a sendable fashion but was not aware of the Access 97 item. I'll have to give that a go. I'm sure there must be a good reason that the Forum won't accept anything larger than a 100K. Quick question while in the neighbourhood and being aware that you are also a Word Expert, is there anyway that a Word document can be "Compacted" and not just zipped?

[Francois]

I had to precise : I get the error in all cases. With or without quantity.
Can you attach a stripped version of your db like Hans propose ?

[AGMCon]

OK, I did try changing the name of the text box to that of the underlying data and got the same error. Correct me if I'm wrong, but I thought that naming your text box the same as your data name was not advisable? Anyway, I'll try and create a cut down version of my system.

[HansV]

The Lounge server is a standard PC (with Linux) with standard broadband connection. If we allowed large attachments, the Lounge would bog down completely.

Word does not have a Compact and Repair option the way Access does (neither does Excel), but then it doesn't need it as much.

[AGMCon]

Hans,

Thanks for the feedback on both counts.

[AGMCon]

Francois & Hans,

OK, this will be one of those believe it or not type scenarious.

The premise of this whole issue was that I wanted to display a subform which contained a continuous form displaying all past transactions in reverse date order so that the last one in was displayed at the top of the list. I placed an =Sum field in the footer of this subform so that I could display a running calculation on the main form. The calculation would show on the main form when sitting on the first record, but as soon as I moved on to the rest of the records, I would get the Error Message.

I've been able to get this to work by, and are you ready for this, placing another subform on the mainform using the same data file, putting the same =Sum field in the footer of this field, having the calculation on the main form refer to this form and made this subform "not visible".

So, I get my continuous form showing past history, my main form calculation showing running totals, no screen space being wasted with a single form subform and all is well.

I have no idea why this works.

Now all I have to do is go and deal with the Average Cost.