DLookUp Function to get value and place in another field?

[louisa]

Hi All,

What i am trying to get is a value from one field in one form to another field in another form. The field has a control source of =Sum([TotalPrice]) the data is being saved on the form itself and not in a table.
I have another form which will work out commission based on various information one being the TotalPrice of the equipment. Instead of entering this value twice i want it so that the total value is put in the other form automatically.
I was advised maybe the DLookUp function would work however you have to enter the field name and table name. The value is saved on the form and not in a table. Is there a way i can do this. I am a begineer at this so as much info would be greatly appreciated.

 

[jzwp22]

You can create a query that calculates the total price and then reference that in the DLookup() function. That query would need to be based on the same table or query that provides the records for your form that calculated the total price.

 

[louisa]

That sounds really complicated for me, i am a novice so is there any easy way?

 

[jzwp22]

As you move forward with your database, you will need to use queries. For certain things, queries can be somewhat tricky, but it sounds like you need a fairly simple totals or aggregate query. We can help you out, but we need to get more information about the tables and fields you have in your database. Let's start with your form that does the total price calculation. Can you provide the table name and the fields within that table and tell us which field is totaled to get the total price?

 

[louisa]

I have attached a sample database, all fields and forms have the same names in my DB,
If you open Contacts1 form it has 3 tabs, the second tab equipment has a Total Box in the form footer which will total all the amounts entered and i want that value to be displayed in the commission tab under TotalKitCost.

 

[jzwp22]

Your current tables are not logically related, so I am guessing that your forms will probably not work correctly. Could you please explain what business process you are trying to model with the database? It appears that you have contacts and they buy stuff from you...?

 

[The_Doc_Man]

There are two ways to do this.

First, if the form holding this data is open, you can reference it by its name from the system's Forms collection. If the form is called A and the control is called B, you can get the value with Forms("A").Controls("B") (because .Value is the default for this syntax.) If form A is not open, this cannot be done.

Second, look at the source of the control for B and the data source for A. The .Recordsource for A will be a table or query. The .Controlsource for B will be your equation (formula) as you described. Make summation query (which you can look up in Access Help) based on the same recordsource used by A and the summed field will be the one referenced in B. That works any time.

 

[louisa]

We have customers indeed that buy from us, items are purchased on a leased basis so if we sell for example 10 tvs at the cost of £100 per month for 36months in the equipment form will be the cost price so when it comes to work out commission we deduct the cost of the equipment from the total price and work out commission after but instead of copying the total cost from the bottom of the form and typing it in again into the commission form i just need a way for it to duplicate the figure into the other form. Apologies as it was a dummy DB i didnt setup the relationships or put much data into it. My current DB has the correct relationships between the forms.

 

[jzwp22]

Did you forget a table? I do not see a table that relates the contact (customer?) to the items they are buying/leasing. Something like this


tblContactLeaseItems
-pkContactLeaseItemsID primary key, autonumber
-fkContactID foreign key to your contacts table
-fkEquipmentID foreign key to your equipment table


I don't know what the purpose of the KitCost table is but I would definitely recommend not having the -25% field name. The general rule is that there should be no spaces or special characters in your table or field names.

Also, the total field in your equipment table, should it be just an item cost? A total implies a calculated value which is generally not stored in a table but rather calculated when needed (via a query).