I keep getting #error when I try to use a total from a sub form on my main form (1 Viewer)

[safinch]

I'm trying to use a text box from the footer of my sub form to show in a text box in my main form. I have done this often in the past and have several working examples.

Today I added three such boxes to my main form, th first two worked and when I added the third the message #error appeared in all three taxt boxes, I've checked the names, recreated the forms and I don't seem to be able to do tis in this database.

Others I have on the network are still behaving norrmally - any ideas?

 

[boblarson]

Make sure that none of the text boxes have the same as any of the fields involved in any of the calculations. If they do have the same name, change them. So, if I had a field named QTY and of course the default for Access is, when the form is made, to name the text box QTY just like the field. So you would need to change the text box name to txtQTY or something like that. And then you can refer to the text box in the calculations without a problem.

One other consideration is that any NULLS will cause a problem. So you should also encapsulate the various parts in the NZ function. Something like:

=Nz([Control1],0) * Nz([Control2],0)

etc.

 

[safinch]

Thanks

I'm sure the names aren't an issue as Ive tried changing them several; times and I use the txt prefix whenever I create a test box - I've just tried it on another database and I'm getting the same problem. A colleague thinks it may be an issue with my network profile.

Make sure that none of the text boxes have the same as any of the fields involved in any of the calculations. If they do have the same name, change them. So, if I had a field named QTY and of course the default for Access is, when the form is made, to name the text box QTY just like the field. So you would need to change the text box name to txtQTY or something like that. And then you can refer to the text box in the calculations without a problem.

One other consideration is that any NULLS will cause a problem. So you should also encapsulate the various parts in the NZ function. Something like:

=Nz([Control1],0) * Nz([Control2],0)

etc.

 

[spikepl]

th first two worked and when I added the third the message #error appeared in all three

that does not seem to point at any network profile thing. What is contained in the control source of the involved controls on the subform - the working and the error-causing one, and on the main form?

 

[safinch]

Thanks for your interest

The forms are called frmBorrowMain and frmBorrowsub.

On the subform I have a text box in the detail section calculating fines called txtfine, with a control source of:

=(Now()-[returndate])*0.2

then in the footer of the subform I have another text box called
text20 with a control source of :

=Sum([txtfine])

and on the main form a text box called text30 with a control source of :

=[frmBorrowSub]![text20]

I've done this loads of times before and I can't see what I'm doing differently now!

Puzzled of Coventry!!

 

[spikepl]

So which is working, and which stopped "working", after adding what exactly?

Also, is the name of the control on your main form containing the subform also frmBorrowSub?

Alos what is the value of Now()-[returndate] ? Did you check? For Now is a date & time, whereas returndate probably is just a date?

 

[boblarson]

Just a bit of futher explanation of spikepl's statement about the control on the main form. When referring to subforms you need to refer to the subform control name (control on the parent form which displays the subform) and not the subform name itself, unless the two are exactly the same. For an example of what I mean, check out the screenshot in this quick tutorial:
http://www.btabdevelopment.com/ts/ewtrhtrts

 

[safinch]

Thanks Bob

I had a look at the tutorial and I've tried using this :

=[frmBorrowSub].[Form]![text20]

where frmborrowsub is the name of the subform control name and text20 is a text box on the footer of the sub form but I still get the #error message in the text box on the main form - do I need to include the main form name to qualify the subform?

 

[boblarson]

do I need to include the main form name to qualify the subform?

No, but you might try using this method to get it, just in case -
http://www.btabdevelopment.com/ts/refer2sfrms
And if that doesn't work then perhaps you can post a copy of the database here and we can take a look.

 

[safinch]

Hi

I've tried that but I still get the same error - and I can't attach the database as it's more than 2 mb.

Can I send it to an email address?

Thanks for your help
Simon

 

[boblarson]

and I can't attach the database as it's more than 2 mb.

Is it still more than 2Mb if you first run COMPACT AND REPAIR and then ZIP the file by right-clicking on it and selecting SEND TO > COMPRESSED FOLDER?

 

[safinch]

Hi

Thanks for that - here it is

Regards
Simon

 

[evanscamman]

I'm having this exact same problem with Access 2007. I want the total to appear on my main form. I know I have done this many times before, but I can't get past the #Error this time. I tried everything that Bob listed above - different names for data source and text box, nz to deal with null fields. I don't think the problem is in how I am referencing the subform (I do that all the time), but obviously I'm doing something wrong.

Does anybody have any new ideas for why this won't work?


Main Form: frmOrderInvoice
Sub Form: sfrmOrderInvoice
Text Box in Sub Form: ItemCost
ItemCost.ControlSource = ItemCost2 (not similar names)

Subtotal field in Form Footer of sfrmOrderInvoice: SumCost
SumCost.ControlSource: =Sum(nz([ItemCost],0))

Total field in Parent form: DifSumTotal
DifSumTotal.ControlSource: =[sfrmOrderInvoice].[Form]![SumCost]


Thank you,
Evan

 

[boblarson]

Not sure why it isn't working, so I'd probably need to see the database in order to see.

 

[safinch]

Hi Bob

did you find anything on the database I sent in June?

I thinkl I'd tried everything you suggested

cheers
Simon

 

[lutajuca]

This will work if
text20 => =sum((DateDiff("d";Date();[ReturnDate])*0,2))
and recreate eventualy Control Source ( with wizard ?) for
Text36 => =[frmBorrowSub].[Form]![Text20]

 

[vbaInet]

I don't understand your question. Can you elaborate?

 

[lutajuca]

My question? You mean: ( with wizard ?) This is not an question, it is a possibility, the proposal ...

 

[vbaInet]

My question? You mean: ( with wizard ?) This is not an question, it is a possibility, the proposal ...

Alright. Well, this is an old post.