Form Problems! Help!

[The Woog]

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

 

[ranman256]

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]

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.

 

[ranman256]

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]

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]

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]

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).