By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,654 Members | 1,840 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,654 IT Pros & Developers. It's quick & easy.

Calculating a value to display in a form based on values from two different tables

P: 2
Hi all,

I have not touched access for a very long time and am attempting to help my nephew with his Video Rental Database. This is a simple three-table relationship database comprising a Video Table, Customer Table and Rental Table.

We have a simple form whose source is set to the Rental Table. We want to calculate the fine on a late video using the number of days the video is late (from Rental Table) multiplied by the cost of the video hire (from the Video Table).

I can get a Query to work this out by selecting the relevant fields from both tables and creating an expression similar to: -
Fine: [Cost per day]*([Date brought back]-[Date Due])

However, I canít figure out how to show this in the Rental Form Ė I just keep getting the #Name? error if I try to link the text box to the query.

If we set the Record Source of the Rentals Form to the Query, I can get the the value to display correctly, but then, of course, we canít edit the other details on the form.

What are we doing wrong?

Feb 26 '07 #1
Share this Question
Share on Google+
3 Replies

Expert 2.5K+
P: 3,072
Create a new field and place:
= [Cost per day]*([Date brought back]-[Date Due])
in the controlsource (or just the textbox)
Your error is caused by the fact that one of the fields is also the name from the control your calculation is in.

Feb 26 '07 #2

P: 2
Yeah, tried that but didn't work. I though that was me being relly silly when you mentioned the names of the lable being the same as the names of the fields (which they were) but altering them didn't make a difference.

As soon as I reference something outside of the table that is used for the Form Record Source, I see the error.

I have partially manaed to get round the issue by creating a new form with subform - so basically refernence ALL fields in the Record Source of the Form.

I'm guessing that this is where I am going wrong - I need to reference ALL the data in the Record Source of the form that I will require, rather than just rely on the single Rentals Table.

Is this right?
Feb 26 '07 #3

Expert 2.5K+
P: 3,072
It's not the label, but the controlname.
Open the properties and look into the Name property under the Other tab.
That's by default (like the label) set to the name of the field from the table and such a field is called "bound".
Place a new field on your form (will be named and labelled e.g. "text1") and place there the "=" command.

Feb 26 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.