Pulling information from a Table into a Form

[KMSER]

I am new to Access so bear with me.



I have three Tables and three Forms: Job ID, Tool ID, and Tool Log ID. On the Tool Log Form I want to be able to Log tools in and out. On the Log Form I want it to show 1) Job Name 2)Tool Name 3)Tool Quantity 4)Check-out Date 5)Check-in Date. I have not had a problem getting it to pull the information from the other IDs for 1, 2, 4, and 5, but when it comes to 3, the Tool Quantity, I want to make sure that the value in the Log Form does not exceed the value in the Quantity Owned Field located in the Tool ID Table/Form. In other words, I do not want to allow us to accidentally check-in (10) ladders when we only own (9). How do I go about doing this?

Originally I had just included a Quantity Owned Field in the Log ID, and planned to create a validation rule for the check-in/out quantity that said not to exceed the value in Quantity Owned. In order to do that I tried to pull the value from the Quantity Owned Field in the Tool ID table into the Log Form by using an expression in the Control Source of the Quantity Owned Box. I'm not sure if I just was not getting the expression right, or if it is not possible to pull that information from a table onto a form, or something else completely. Advice?

[June7]

Trying to save aggregate data to table is tricky. Ideally, this should be calculated from records of increases and decreases, commonly done with an inventory database. Instead of tracking quantity, you could have a record for each asset (means physically tagging each item, which you should do anyway if you are going to make employees accountable for them) and if asset is checked out it would not be available to another user. Sounds like the Lending Library template. Check it out http://office.microsoft.com/en-us/te...010206883.aspx

[cplmckenzie]

examples of your work always provides better clarity than words alone ever will.

without that most answers are a shot in the dark ... at best.

cplmckenzie