Form Field based on query giving #NAME?

[weilerdo]

Hello all, I am hoping to get some help with a head scratchier. I have a Form "Inventory Details" that the control source is a Table "tbl_inventory" on the Form I have an unbound text box "Text6" that I want to be able to visually display a calculation that is based on another Table "tbl_transaction" The Query is "qryTest" which works outside the form with no issue and returns the value. I do not want or need to store the value in a table as its mean to show the current in stock quantity for a given part. I assume it must have something to do with the form not being based on the same table as the query put I am lost as to why I would be getting #NAME? because I have no fields or controls with the same name. I have attached a stripped down version of the database in hopes someone can see my error. Thanks in advance for your support.

[xps35]

You cannot refer to a field in a query like you did. You need to refer to fields in de recordsource of the form.
I created a new query (qryInventoryDetails) connecting tbl_inventory and qryPartsBalance and used that as the recordsource of the form.

BTW: you should create relationships on primary keys. So the relationship between tbl_inventory and tbl_transaction must be on (inventory) ID.

[ano]

replace the sql :

=[qrytest]![Balance]

by:

=DLookUp("Balance","qryPartsBalance","[Part_Num] = '" & [Part_Num] & "'")

[ano]

technical and logical you create lots of problems with that statement.
A) you never put the result from a calculation in a database
B) if you do put a result then there are a lot of thing u need to think of like :
1) never change fields used in a calculation , meaning only add records
2) a date is needed to compare old result to add newer only
3) stock also needed to decrease by ordered, if only by sold then u have no stock to sel next order
this are just few things that came up in my mind there are more things to think ...

[weilerdo]

This was perfect, thank you both for your help.