MS access form issue

[likemike973]

I am using Access 2003. I am trying to bring a calculated value from a query to a form. I would like to have the query bring the value back to the form as read only.

Help!

[Scott Price]

You can 'bind' a text box to this query field just as normal, then in the properties for the text box, set Enabled to No and Locked to Yes. Your users will be able to view the data in the text box but not change it.

Regards,
Scott

[likemike973]

Forgive me for being a newb, how do you bind? I tried searching online and vb stuff is beyond me. Is there a way thru expression builder or the properties to do it?

Thanks Again,
Mike

[Scott Price]

A control is said to be 'bound' when it's Control Source (in form design view, double click the control, or right click and select properties>All tab) is set to a field in a table or query. On this same tab you will find the Enabled and Locked properties that I referred to also.

The same control will show to be 'Unbound' in form design view if it's Control Source is set to nothing.

Regards,
Scott

[likemike973]

OK now I understand bound and unbound, I have actually been doing it just did not know the term. Thanks.

I created a Text Box and tied it to a control. In this case =(qrynetprofit![Net Profit]), and when I go to the form view it show #NAME? error in the field where the Net Profit value should be. Have you seen this before?

Thanks
Mike

[Scott Price]

Is this field part of the Record Source for your form? If you are attempting to pull this from one table or query while the rest of the form is based on a separate query or table you'll have this problem.

In this case you'll need to include this value in the Record Source for the form, by creating a separate query that includes it.

Otherwise, it's likely from a calculated value that isn't being calculated yet.

Regards,
Scott

[likemike973]

Thanks for the help so far. I have attempted a few things on my own. The information is coming from the Record Source, but like you stated it hasn't calculated the fields yet. Is there a way to make it calculate, by refreshing? or maybe using another query to calculate.

Thanks for the help so far.

Mike

[Scott Price]

The #Name? error will come if the field you are attempting to bind the control to is not in the query/record source for the form, in other words, it doesn't have a valid control source...

Otherwise it will show #Error, when the calculation hasn't been performed yet or can't be performed for some reason.

When looking at the form in design view, is there is a small green triangle covering the upper-left corner of the text box?

If so, the problem is likely because the control source is not, in fact, part of the record source for the form, either because it's from another query, or because the control source is mis-spelled.

Are you sure that it's the #Name? error showing?

Regards,
Scott

[Scott Price]

Just looking at your earlier post again. You show the control source to be =[FieldName]. Delete the equal sign and try again.

The = sign tells Access that you are calculating within the text box itself. To do a simple binding to a query field, the = sign is not needed.

Regards,
Scott

[likemike973]

I checked out the small green block, and yes you are correct, like you stated before I am using the wrong source. My form is putting information into a table, tbl1 and I need one piece of calculated information from a query, qryprofit, to go back to the form to display a calculated value. Is there a way to get the one piece of information from the query back onto the form while maintaining the integrity of the form based on tbl1?

I made a input form to help me calculate fees, etc on Ebay for myself and a friend. It has been challenging but also rewarding. I enjoy, most of the time, these problems. I would ultimately like to input on the form a list of fees, and costs associated with a particular product and have at the bottom the form give the Net Profit from the query.

Yes the error is #NAME?

Thanks

[Scott Price]

Not being able to see your database, I'm not able to say which is the better way to go, but you have two options:

First option is to include your base table in the query you are using to calculate the information. This is probably the best option. Just right click on the query in design view and choose 'Show Table', choose the table you are putting the information into, and then save the query and run it to make sure that all the required records show up.

You'll then have to go back to the form in design view, change it's record source from the table to the query and re-bind each control on the form to the corresponding field passed through from the query.

Second is to do the calculation in the text box itself instead of the query. This would involve using the = sign and whatever calculation you have, i.e. =DSum() etc. etc.

There isn't a great deal of difference between the two options in terms of performance, but the first is likely just a bit faster in displaying the calculated value in the text box.

Remember to make a backup copy of your query before changing it! Then if something goes wrong and you can't figure out how to fix it, you can just revert to the backup!

Regards,
Scott

[likemike973]

I'll give it a try.

Thanks again
Mike

[likemike973]

I tried the first option. It works great! I will have to rebuild the form, but that is part of the learning curve.

Thanks for all the help,
Mike

[Scott Price]

Not a problem, and glad you got it figured out!

Thanks for posting back letting us know that it worked.

Regards,
Scott