How to assign a selected value in a combo box to a table field.

[Mohamed osman]

I'm designing a small stock control system with 2 tables: Item table composed of: itemid, itemname and quantityonhand. Transaction table composed of: TransID, itemid, TranCode (IN, Out), quantity and date of transaction. I designed a form consisting mainly of a combobox based on the Item table. The combo box displays the ItemName of all items in the table and the ItemID which is hidden. then there is a textbox where a user enters the Quantity and another for the date and a third for the Trans code. The system should work like this: User selects an item from the combobox, enters quantity, date and trans code. A record is added to the transaction table, and the corresponding record in the Item table is updated by either adding to or subtracting from the quantityonhand depending on the Trans code.
My problem is I failed to write the correct code for linking the selected item from the combobox to the corresponding record in the Item table.

[liimra]

I assume that you already created One-to-Many relationship between the two tables based on ItemID.
(ItemID in ItemTable is Unique but can be used many times in the Transaction Table as you will sell and buy the same item many times).

If the above is true, you have to create a form based on the Transaction table and not on the Item Table. All fields contained in the transaction table should be there. The query for the combobox you create should contain two columns: ItemName which will be displayed to be chosen and ItemID which will be hidden. On the AfterUpdate Event, Embed a Macro with SetValue action so it sets the value for the ID field (Transaction Table). This can be done via VB but in Macro case: the Item = ItemID & The Expression would be = [ComboBoxName].[column](2).

The above should solve the linking problem.

For entering the calculation (+/-) in the Item Field, I am afraid this is not possible. Why should you save calculations, when you are able to display them at any time using Queries,Forms, & Reports. But it doesn't stop here. In order to have the running quantity (Balance on Hand), you have two solutions of which I know. The first is to have the Quantity in the Transaction table recorded as (+) when you purchase and (-) when you sell; and then you use DSum. The second is to have two transaction tables, one for sales & one for purchases and this what I would recommend. In this case, you use DSum(Purchases where ID is ..) - DSum(Sales where ID is...).


Hope this helps,
Regards,
Ali

[Mohamed osman]

Thank you Mr. Ali, will try it.

[liimra]

Did you work it out?

Regards,
Ali