Combo Box criteria

[JenK]

I have a form in which I track our company owned tools for inventory
purposes. Most of the tools get assigned to our employees. I have created a
form that tracks the tools allocated to each employee.

The way the form is currently set-up is I have the form with the employee
info, attached is a subform for the assigned equipment. The equipment is
stored in the equipment form along with serial numbers. I have an [EquipItem]
menu from which the equipment is selected and then assigned to the employee.
What I want to be able to do with this box is have only un-assigned
equipment show up when I go to enter a new equipment for a new employee, but
I still want to be able to see the equipment assigned to each employee. How
can I accomplish this do I need some other box?

I hope this makes sense.

 

[KARL DEWEY]

You talk forms but nothing about tables.
Do you have the following tables --
Equipment
-EquipID autonumber - primary key
-Type
-Size
-Serial
-PurchDate - DateTime
-Price

Employee
-EmpID - autonumber - primary key
-LName
-FName
-MI
-Hire - DateTime
-etc.

Assignment
-AssignID - autonumber - primary key
-EquipID - number - long integer - foreign key to Equipment table
-EmpID - number - long integer - foreign key to Employee table
-DateOut - DateTime
-EstRet - DateTime
-DateIn - DateTime
-Notes - Text

The Equipment table needs to have a one-to-many relationship with the
Assignment table. The same for Employee.

You may have 4 of the same type and size but they each have different
EquipIDs.
Your query for the Issue form will check the Assignment table to see if any
that have DateOut also have DateIn. For a given type and size we count
on-hand minus DateOut without DateIn.
Totals query - Equipment table
Type Size EquipID
Group By Group By Count

Totals query - Assignment table
Type Size EquipID
Group By Group By Count
WHERE DateIn Is Null

Balance query
Type Size EQCount-AsCount