Pass a value from a query to a form

[cwwaicw311]

Hi,

I have got 10 Item and each item have their own InvIN, InvOUT and Balance. I use 10 query to divide all 10 of the item and calculate the balance of each of the item in their respective query.



N in the form i have created a textbox to show the Sum of the Balance when one of the item is choose from my Item Combo box in the same form.

For example: Item A have 3 record in it balance 10 ,20 and 30. So in the form i choose Item A from my Item Combo box. Thus, the Sum of the Balance textbox in my form would show 60.

How do i do it...??

[maximus]

Dim intStock as integer 'Please use Long if the numbers expected is Greater than 32,767
Dim intInvIn as integer
Dim intInvOut as integer

intInvIN=iif(isNull(DSum("[InvIN]","Transaction","[InventoryID]="& Me.Combobox1)),0,DSum("[InvIN]","Transaction","[InventoryID]="& Me.Combobox1))

intInvOut=iif(isNull(DSum("[InvOut]","Transaction","[InventoryID]="& Me.Combobox1)),0,DSum("[InvOut]","Transaction","[InventoryID]="& Me.Combobox1))

'I have assumed that InvIN and InvOut is in the same table. This will work if you maintain seperate tables just change the table name


intStock=IntInvIn-intInvOut

Me.TextBox1=intStock

[maximus]

is your problem solved then please mark the thread solved.

[cwwaicw311]

Hi,

Where should i put this code at...?? After update event on my Item Combo box...?? I have try it on After Update event for my Item Combo box but not working...

[maximus]

I am attaching a sample mdb file I have attached the above code to the AfterUpdate event of a combobox. Check out the Stock Form.If your problem is solved mark this thread solved.

[maximus]

checked out the sol

[cwwaicw311]

I still cant able to do it... I have attached my database here. Hope you could find out what is the problem.

I have done some changes to my work so it would be a bit different. I use Inventory Tracking System Main Menu to choose 1 type of InventoryType where onclick it will open the Inventory Tracking Table Form(Transaction) and the InventoryType value would bring to the InventoryType textbox rather than a combo box.

Thanks...

[maximus]

please save the database in access 2000 format and upload coz I am in the office and we use Access 2000

[cwwaicw311]

Attached is 2000 format.

[maximus]

I have addressed most of the Issues that you had:
1) Machines Pertaining to a particulat Inventory will be available for selection.
2) Stock will be displayed at the Form Header.
3) Stock is Update immediately after a transaction.



I have made some changes to your database, Added a few fields and changed the names of a few. In the Inventory_Tracking_Table_Form Form I have put a Insert Button. It uses SQL to Append Data to the table. Type data in your form and Click Insert Button. This Button also updates the stock for the inventory.


Suggestion: I think you should record Issue and Received Inventory in a seperate tables. I assume Issue is purchase of Inventory. Inclusion of re-order level is also necessary. You need to generate opening and closing stock also. if this database is related to process or contracts then you need to tag them. User should be able to see how much inventory is going in a process, Inventory used and un-used inventory returned.

if this solves your problem mark the thread solved.

[cwwaicw311]

Hi,

Thank you very much... this solve the problem i faced. According to your suggestion, I was thinking about when one of the inventory reach it minimum balance I wanted the Item Name, date and few other info to display at Inventory_Tracking_System_Main_Menu as a reminder. I able to use query to show it when reach limit. However, when the item is replenish i cant make the record disappear since i dun need to remind them to replenish the item.

[maximus]

I think you should mark this thread solved. As for the minimum stock balance prompt I will post it in the other thread that you have posted in queries. Plz mark the thread solved.

[maximus]

can you give me eact what details you want to show. Have you thought of splitting the Inventory_Tracking table to record issue and Receive inventory seperately

[cwwaicw311]

Hi,

The information i wan to show in Inventory Tracking System Main Menu is the inventorytype, date, operatorname and balance when the balance of the inventory reach the minimum unit.

I have not thought about splitting the 2 table before, but if they split, would it affect the way where i want to enter the data in only 1 form...?? If it does not affect and it would make the database work smoothly then splitting the table would not be a problem.

Thank.

[maximus]

No it won't be a problem believe me life is going to be much easier for you. as for your minimum balance prompt I have already worked it out need to test a bit to see if its working OK will it be a problem if I post it on Monday. more over I wanted to ask you this database be a single user or a multiuser application.