I am creating a spare part management database in Microsoft Access. I have three tables which are ItemTable, EntryTable and TechnicianTable.
ItemTable has PrimaryKey = ItemID and the value is ItemName.
EntryTable has PrimaryKey = EntryID and ForeignKey of ItemID with lookupwizard.
TechnicianTable has PrimaryKey = TechnicianID and the value is Technician ID.
I need to calculate the stock quantity of each items based on its usage or entries in the EntryTable.
Thus, I have created a Item Stock query that calculates the entries of each item based on "In" or "Out" to calculate the total and group by ItemName. Thus the StockQty field as and expression of Sum(IIf([StatusEntry]='In',1,-1)*[quantity]) AS StockQty.
Now, I want to create a search combobox form based on the Item Stock query to filter the ItemName.
I have created a form with subform. The subform linked to the Item Stock query and the main form has the combobox. In the combobox i have linked the row source to ItemName from ItemTable.
Next I wrote a vba code based on youtube tutorial from here: Access: How to Create Search Form Using Combo box Part 1
When i filter the combobox, the subform is updated which is good but the StockQty column becomes #Name? which i guess is name error and could not find the reference. Is there any solution for this?
