#Name in calculated field on form (1 Viewer)

[Corsica]

It sounds as if you don't have the fields from the Table in the Query, hence the Query asks for them when opening. What you should be doing is

1. Create a Query based on the table
2. Include the appropriate fields, including Length, Width and Price
3. Include a calculated field such as QuotedPrice:[Length] * [Width] * [Price]

Now base your form on the Query, not on the underlying table.

Linq ;0>

The field QuotedPrice will now appear in the Field lsit and the calculation will be done after the data isentered.

This didn't work for me. I wish it had! It worked in datasheet form of the query, but not when I linked the calculated field to a text box on a form.

Any ideas, anyone?

 

[isladogs]

Re: Adding a calculated field to a Form

This is a very old thread and it would have been better to start a new thread.
EDIT Now done!

What did you see instead?
An error message? If so what?
#Name? #Type?
Something else?

 

[Corsica]

Re: Adding a calculated field to a Form

I saw this: #Name

I found a way to do what I was trying to do, but I would still like to know why using the calculated fields, that were made in the query, as control sources of text boxes on my form didn't work.

 

[isladogs]

Re: Adding a calculated field to a Form

That error usually indicates that you mistyped something so a field name isn't recognised
Or the control name and field name are identical and the control source starts with an = sign
As you said this works in the query, the second reason sounds more likely.

 

[Corsica]

Re: Adding a calculated field to a Form

Hi, many thanks for your replies.

I've just redone things:
The calculated field in the query is called Height3. The name of the text box on the form is Text136. The control source of Text136 is Height3 (without an =). I didn't type in Height3, I selected it from the drop down list.

 

[isladogs]

Re: Adding a calculated field to a Form

You missed out one important detail. Does it now work?

BTW Its perfectly OK to use the same name for a control as the field as long as you don't use = in the control source. If you use a different name, I strongly recommend using something meaningful like txtHeight3

 

[Corsica]

Re: Adding a calculated field to a Form

Still getting the same error. I would have named it txt_Height if I wasn't just testing.

When I hadn't been just testing, I had a text box called txt_Height2 having control source Height, and a text box called txt_Height having control source Height3. Don't know if that could confuse Access. In form view, the name error was in the txt_Height text box. All of this was on a continuous form.

 

[isladogs]

Re: Adding a calculated field to a Form

I'm not really following this. Can you upload a cut down copy of your dB with anonymised data.

I'm going to move this to a new thread so please leave it a few minutes to reply whilst I do so.

 

[Corsica]

OK, thanks. I will try to do that now. It may take me a while!

 

[Corsica]

It works in my 'database for testing'. I made a table, query and form, and recreated those few things, and I didn't get an error.

(I didn't send you the other database not because of the data that's in it, but because I shouldn't show the design to anyone.)

I've heard that Access files can become corrupted.

Two weeks ago when I had another thing like this happening, I used the compact and repair command in two ways, but that didn't help.

Still, I'll try that now.

 

[Corsica]

The attachment is the DatabaseForTesting, so that you can follow what I've said. The other real database is large and I mustn't pass a copy to anyone. I wish I could!

 

[Corsica]

In the real database the name error is coming up in the second text box. Thank you for all of this. I'm sorry that I can't send you a copy of the database that contains the actual error.

 

[SteveSchapel]

Corsica
Maybe you could start from scratch to build a copy of the problem form in the live database, and see if that works.

 

[Corsica]

I ran that compact and repair command in the two ways that I know, but I'm still getting that error. I found another way of achieving the required result, but having an error come up that apparently shouldn't does not fill me with confidence. I still have a lot of work to do on this database.

 

[Corsica]

Hi Steve, I'll do that. I can do that by copying and pasting the hundred, or so, objects on the form.

 

[Corsica]

Steve, I did that by copying and pasting after renaming the old form, but I'm still getting the error. I'd got my wires crossed though, the form in question doesn't have many objects on it, so I can build it from scratch, one object at a time if necessary. But not now.

I have to close this for tonight. I am already several hours late. I apologise for leaving when you both are trying to help me. I will return to this soon. I'll rebuild the form from scratch starting with the troublesome text boxes, and then report back on this thread.

Many thanks to you both.

 

[SteveSchapel]

Good luck. This is indeed a puzzling problem, and as Colin says, being able to see a cut-down copy of the problem application would help, so it's a shame that's not possible. It will be good to get it sorted.

 

[isladogs]

OK if you can't upload and have already tried compacting, the next thing I would try is to decompile your database. See http://www.fmsinc.com/microsoftaccess/Performance/Decompile.asp

 

[Micron]

I'm having trouble following this, especially since it seems the original post isn't part of it.
If performing aggregate functions on a form recordset, it is usually required to use a Domain aggregate function (e.g. DSum, DCount, etc. and not just Sum or Count) and reference the fields, not the control names. Problem is, I can't recall at the moment if the error generated in those cases is #Name or #Error or something else. Given my uncertainty, maybe that won't help.

Posting one form and the supporting table/queries would be revealing too much about design? Or is the issue data related? There is code available here to randomize data if you can make use of it.

 

[SteveSchapel]

Hi Micron
As I understand the problem here, Corsica has a query with a calculated field. In the query datasheet, the calculated field works correctly. However, in a form with this query as the record source, a textbox which is bound to the calculated field shows #Name.
Something peculiar is going on.