The dreaded #Name?

[Saudi Mike]

I'm sure I'm missing something simple here. This is in Access 2010.

I have two databases. One called BaseData that carries the data. Multiple tables with various data sets. that's it. No queries, forms or anything else.

The second (we'll call WorkingData) basically carries my forms, queries and reports. ALL tables in this database on only reference tables to BaseData.

Now, in WorkingData I have a form called frmScoreCard. it is based on a query called qryScoreCard. Information from various fields is reported here from the query. Now I create a field in the form in which I would like to perform a calculation based on the information shown. However, when I go to the 'properties sheet' for the calculation field and enter the Expression Builder is where I hit a wall. In the Expression Building I can see the various data in the form (all from referenced tables in BaseData). When I select any item from the Field List (such as 'tblArchCodeNos.1sCod1s' which then shows as [tblArchCodeNos]![1stCode1s]) and I go to the Form View I see this darn #Name?. (Any expression building results in the same error)

Now this same data is already shown in the form in one of the fields and works fine. What am I missing?

 

[bob fitz]

It may be better to do the calculation in a calculated field in the underlying query.

 

[Ranman256]

in the form, the calc uses the txtbox name, not the field name.

txtArea = txtBox1 * txtBox1

(not [length])

 

[Saudi Mike]

Hi Bob and thanks for the reply! Good idea and much simpler than I was trying to do. Just had to add a few expression columns in the query and wah lah. got it. Thanks!!!

 

[Saudi Mike]

Ranman, The problem may have been because I was trying to use numbers from a table outside of the form so there was no txtbox name.

 

[Saudi Mike]

Ok.. a bit of a new twist. As suggested, I've used calcs in the query and all's been well. Now I have one calc that seems to work in the query but it doesn't show up back in the Table. ?? In particular, I have a table that is referenced in a query. In the query I have a simple equation:

1stDeliverable: tblArchCodeNos.[1stCode1s]+[1stCode2s]+[1stCode3s]+[1stCode4s]

The field 1stDeliverable is in the table in question. If I view the query in data sheet, sure enough, the calc is there and all is good. But I go back to the table and the calc is not reflected.

Ideas?

 

[vbaInet]

Ranman, The problem may have been because I was trying to use numbers from a table outside of the form so there was no txtbox name.

I was just about to mention that before you posted. Good job for spotting the mistake Saudi Mike!

Ok.. a bit of a new twist. As suggested, I've used calcs in the query and all's been well. Now I have one calc that seems to work in the query but it doesn't show up back in the Table. ?? In particular, I have a table that is referenced in a query. In the query I have a simple equation:

1stDeliverable: Nz([1stCode1s],0)+Nz([1stCode2s],0)+Nz([1stCode3s],0)+Nz([1stCode4s],0)

You don't need to store calculated values, it's not a good idea. Just calculate it whenever you need to. I've amended your expression above.

And welcome to the forum!

 

[Saudi Mike]

Thanks VBAInet! Ok.. one more? I've been perusing the forum and it's not the easiest to search. The latest tripup is a form that I open and it opens at the bottom of the form, not the top. Again, this has to something simple I'm missing..

 

[vbaInet]

It's easy to perform a search if you go to the Advanced Search page.

Is it a pop up form?

NB: Ideally, questions asked on a thread should be related to the original post.