The Woog

New Member
Joined
Apr 22, 2015
Messages
13
Hello,

I have created a database with multiple tables in Access 2013. i have had no problems getting the relationships for the drop boxes set up. Where i am struggling is getting a unit price from one table, to be multiplied with a entered quantity in a fox text box to create a total that would be recorded into the main table. hopefully i can explain this right.

I have a table named "universal log" with a form named "Universal Log Form" which records information into the before mentioned named table. The "universal log form" has a text box titled "part number". When a part number is typed into the box it pulls information from a table named "masterpricelist." It is taking the material description and the unit price and populating it into boxes named "material description" and "unit cost" on the "universal log form" respectively. This is all working perfectly.

My problem comes in when i try to get a calculation for the "total cost" This used to work when the unit cost had to be entered into the form manually. Then it was just a simple calculated default value that took the value entered into the "QTY (pcs)" box and the "unit cost" field and multiplied them and the result was placed into the user form field "total cost".

My question is how do i get the auto populated value from the "unit price" form box to multiply with the manually entered "QTY (pcs)" value and record onto the user form for the "total cost" which would then be recorded into the total cost field of the "universal log" table? It is giving me issues because i can not create a default value in the "universal Log" that uses a field existing in a different table, in this case the "masterpricelist" table.

Please help, this has been driving me crazy and I'm due to present this early next week on 6/22/15
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

ranman256

Well-known Member
Joined
Jun 17, 2014
Messages
1,957
You need a query "qsUniversalLog"
The form:
"Universal Log Form" needs this query as a datasource ,Instead of the table.
The query will have all the fields:, * (the asterisk), plus the total field formula:
[cost]*[Qty] as Total

The total is calc'd instantly.
select *,
[cost]*[Qty] as Total from [UNIVERSAL LOG]

 

The Woog

New Member
Joined
Apr 22, 2015
Messages
13
You need a query "qsUniversalLog"
The form:
"Universal Log Form" needs this query as a datasource ,Instead of the table.
The query will have all the fields:, * (the asterisk), plus the total field formula:
[cost]*[Qty] as Total

The total is calc'd instantly.
select *,
[cost]*[Qty] as Total from [UNIVERSAL LOG]



I believe i understand what you're saying but not exactly how to do it. I learned to use access to build this log. I had created it in Excel and i am recreating it in Access and finding things I can do that i couldn't in Excel and this is one of them. Could you give me a bit more of a detailed explanation please? I'm not sure if i shoul be using SQL or the Design view. Thank you.

[edit] i tried to insert a screen shot but that didn't work.
 
Last edited:

ranman256

Well-known Member
Joined
Jun 17, 2014
Messages
1,957
start a new query
bring in your table: "Universal Log"
dbl-click the Asterisk , so it appears in the query grid
in a blank col, add this:
[cost]*[Qty] (or whatever you call your fields)
save the query as: qsUniversalLog

in the form UNIVERSAL LOG FORM
in the property of DATASOURCE, change UNIVERSAL LOG to
qsUniversalLog


 

The Woog

New Member
Joined
Apr 22, 2015
Messages
13

ADVERTISEMENT

I followed your instructions to the T. The only difference is there was no "Datasource" property, only control source and when i used the "qsUniversalLog expression 1" it did not work. it gave me a "#name?" value. Also i don't understand how this will multiply the "unitprice" retrieved from my "masterpricelist" table and the "QTY (pcs) entered manually in on the user form. i appreciate your help.
 

The Woog

New Member
Joined
Apr 22, 2015
Messages
13
start a new query
bring in your table: "Universal Log"
dbl-click the Asterisk , so it appears in the query grid
in a blank col, add this:
[cost]*[Qty] (or whatever you call your fields)
save the query as: qsUniversalLog

in the form UNIVERSAL LOG FORM
in the property of DATASOURCE, change UNIVERSAL LOG to
qsUniversalLog

Ranman,

Thank you for your help, I found the field you were talking about. I have one last question on this issue before i can call it complete. Is there a way to rename the field from "expr1" to "Total cost"? If I change the name in the query column it stops calculating the total cost.
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
2,019
Office Version
  1. 365
Platform
  1. Windows
replace "Expr1:" with "Total_Cost:" or "TotalCost:" without the quotes but do not remove whatever comes after the Expr1 that you have.
Expr1: or LName: are field alias names.

Never use spaces or special characters (except for the underscore _) in field or object names (not "Total Cost" as you wrote).
 

Watch MrExcel Video

Forum statistics

Threads
1,123,259
Messages
5,600,575
Members
414,390
Latest member
plimbu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top