i'm working on modifying those queries now - the "available" and "unavailable" queries; since we moved those fields to another table. And then I create that subform based on the "available totes" and "available lids" queries, right?
First, your tables are still not correct. You have no link between your transactions table and your transactioncontainers table. You need the transaction id in transactioncontainers.
After that, the Record Source for your subform should be T_Transaction_Containers, not a query. Finally, you have no drop downs on your subform at all.
In T_Transactions you have Trans_Employee which holds the ID number of the employee. You need to do the same thing for TransactionContainers. It needs a field to hold the ID of the Transaction it belongs to. The field you currently have set up is a primary key autonumber, which is fine, it just makes each record in TransactionContainers unique. You need a field in TransactionsContainers that ties each record back to a specific Transaction.
For drop downs, in design view of your form, right on the input select 'Change To' in the dialog that pops up and then 'Combo Box'.
Looks great. I do see one issue--when I add a Tote/Container to a transaction and then drop to a new line to add another one, the ones I just added are still available in the drop down.
I'm not the best with forms, but I do know there is a way to make it so that the combo boxes refresh after each change. I don't know if it requires a requery or a refresh of the combo box and I'm not certain what event to attach that code to, but I know its possible.
You might post that question in the forms section.
To refresh the combo boxes - on the subform in the After Update property I selected Embedded Micro and selected Requery action; and entered both the lid and tote queries. I've attached the database in case it would be helpful to anyone else that's been following along.
