Solved: #Name error with textbox reference to query

Status
This thread has been Locked and is not open to further replies. The original thread starter may use the Report button to request it be reopened but anyone else with a similar issue should start a New Thread. Watch our Welcome Guide to learn how to use this site.

bmclachlan

Thread Starter
Joined
Jun 29, 2009
Messages
11
I am receiving a #Name error in a text box on a form that is referencing a query.

I have a transaction table which collects various $ costs, provider, date, and description. There can be many transactions for the same provider on the same date. So, a query "qry_TransNet" has been set up to total $ transactions by provider, by date. The query has three fields-- date, provider,TotAmt. The TotAmt field sums the various costs associated by provider, by date.

In a separate form based on another table called "medical" , there is a text box that contains a formula:

=[qry_tranAmtnet]![TotAmt]

This text box should display the entry in the query that is related to the user input, on the form, for date and provider. No matter what I do it seems that the only response from this text box is #Name?

I have made certain that the fields date & provider in the table "medical" are related to the fields date & provider in the query "qry_TranAmtNet".
 

OBP

Joined
Mar 8, 2005
Messages
19,896
BM, if the [TotAmt] field is in the Query and the Query is the Record Source of the Form then the [TotAmt] field should be in the Firm's "Field List" and you should be able to select it in the Field's Record Source Dropdown list.
You should not need any = sign or reference to the query.
 

bmclachlan

Thread Starter
Joined
Jun 29, 2009
Messages
11
Thanks for your help! I just learned something about associating queries and tables on the form. I had not realized how easy it can be but you put me in the right direction.

Bill
 

bmclachlan

Thread Starter
Joined
Jun 29, 2009
Messages
11
OBP - I thought this had been solved but on further inspection find that by creating a form that has a field list composed of both the firlds from a table AND the query, actually combines all records in both the table and the query.

All I want is to get that one [TotAmt] entry - and there will only be one - that is defined by Provider & date to display it on the form.

Bill
 

OBP

Joined
Mar 8, 2005
Messages
19,896
Bill, I always use a Query for all of my Forms. If you can't create a query of the table data and add the [TotAmt] to it then you can always create a Subform from the [TotAmt] query and make it just show the one field .
I would need to see your data structure to advise the best way to do it.
 

bmclachlan

Thread Starter
Joined
Jun 29, 2009
Messages
11
OBP -

This is very frustrating. I have spent way too much time trying to figure this out and seem to be getting nowhere. I have prepared a brief one-page issue log of what I am faced with hoping that there is sufficient information here that you could see what I am doing wrong. If I am missing information please advise.

I certainly appreciate your help.

Bill
 

Attachments

OBP

Joined
Mar 8, 2005
Messages
19,896
Bill, I would suggest that you send me a zipped copy of the database in Access 2000/2003 format. It doesn't need any data in it.
 

bmclachlan

Thread Starter
Joined
Jun 29, 2009
Messages
11
Yes - the Medical form requires input from the user for patient, visit description, amtpd, datepd, account, refnbr, notes and attachments (not shownon the 2002-03 version).

Bill
 

OBP

Joined
Mar 8, 2005
Messages
19,896
OK, then you can't combine the table & Query in another query. So the easiest way to show it would ba as a small Subform. I will create one so that you can see what I mean.
 

bmclachlan

Thread Starter
Joined
Jun 29, 2009
Messages
11
Bill, take a look at this version.
Will you be wanting to show a balance as well?
Thanks a lot for solving a problem that was very frustrating for me. So I see that you have solved this issue by creating a sub forum based on the query and that is perfectly acceptable. I guess I still do not understand why one cannot access one row of a query directly in a calculated text box like I was trying to do. I guess I need to do more research on the issue in my training.

Thanks again for your help. This site is a great resource.

Bill
 

OBP

Joined
Mar 8, 2005
Messages
19,896
Bill, the query is not "Open" to access that data, you can use VBA and a Recordset to do it though. But the subform is the simplest method.
 
Status
This thread has been Locked and is not open to further replies. The original thread starter may use the Report button to request it be reopened but anyone else with a similar issue should start a New Thread. Watch our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Top