Using VB to change the value in a combo box

[Noobie]

Hey hey,



New here and new to both access and VB. I not sure am i thinking this a bit unlogic or complicated. But I have a form whereby I have a field called Material and another field call ProcessMach.

1 type of material can go into different ProcessMach to start processing.

I want to do all transaction in 1 form so hence, my problem is that when a material is choose the combo box ProcessMach value also change according to the type of material chosen.

Example . Material A will oni go to machine X n Y
Material B will oni go to machine U n V

Is it possible or I am doing something imposible. Thank.

[maximus]

Let me get you straight your problem is:
You have Two Combo Boxes 1) Products 2) Machines
You want Machines ComboBox To Display Only machines meant for a particular Product. I quote your example:

Example . Material A will oni go to machine X n Y
Material B will oni go to machine U n V

I have attached a simple mdb that will give you a practical glimpse of what I am about to explain:

I have created three tables:

Machine:{Machine_id (Auto number PK),Machine_Name}
Products:{Product_id (Auto Number PK), Product_Name}
Product_machile_link:{entry_id (Auto Number PK), Products_Id (Number linked to Product_id tblProducts),Machine_id (Number linked to Machine_id tblMachile)

Now I have Created a form9 with two comboboxes:
Combo0, Record Source Type:Table/Query, Row Source: SELECT [Products].[Products_Id], [Products].[Product_Name] FROM Products;

in the AfterUpdate Event of the Combobox the following Code: DoCmd.Requery "Combo2"

Combo2,Record Source Type: Table/Query,
Row Source: SELECT [Product_machine_link].[Machine_id], [Machine].[Machine_name], [Product_machine_link].[Products_Id] FROM Machine INNER JOIN Product_machine_link ON ([Machine].[Machine_id]=[Product_machine_link].[Machine_id]) AND ([Machine].[Machine_id]=[Product_machine_link].[Machine_id]) WHERE ((([Product_machine_link].[Products_Id])=[Forms]![Form9]![Combo0]));

Now Product A is Linked X and Y and Product B is Linked to U and V. when you select Product A in Combo0 only X and Y is Displayed in Combo2 and when you select product B combo2 Shows only Machine U and V

if this solves your problem mark this thread solved.

[Noobie]

Hi, thank for the example... Can i also use the way u show me on this scenario...??
I have 4 table, 1 for Staff, 1 for transaction, 1 for machine, and 1 for Inventory

Machine:{MachineID(PK), MachineNumber}
Inventory:{InventoryID(PK), InventoryType,}
Staff:{StaffID(PK), Shift}
Transaction:{TransactionID(PK), Date, InvIN, InvOUT, Remarks, OperatorID(FK), InventoryID(FK),MachineID(FK)}

I have a form whereby i take = Date, InvIN, InvOUT, Remarks, InventoryType, MachineNumber, StaffID

so when i change InventoryType combo box the MachineNumber Combo box also will change like ur example...??

[maximus]

Sure why not!!! from your problem it seems that you have just started building your database. Now we have to do some thing by which access will be able to relate Machines with Inventory. My Suggestion is another table that will link Machine with Inventory:

Machine_Inventory_Link{Entry_id(Auto Number,PK), Inventory_id(Linked to InventoryID tblInventory use Lookup wizard), MachineID(Linked to MachineID tblMachine use Lookup wizard)}

Now in the Machine_Inventory_Link Table Link your Inventory with Machines.


Machine:{MachineID(PK), MachineNumber}
Inventory:{InventoryID(PK), InventoryType,}
Staff:{StaffID(PK), Shift}
Transaction:{TransactionID(PK), Date, InvIN, InvOUT, Remarks, OperatorID(FK), InventoryID(FK),MachineID(FK)}

"I have a form whereby i take = Date, InvIN, InvOUT, Remarks, InventoryType, MachineNumber, StaffID"

Now from the above statement I assume
1) You have Created Two ComboBoxes
2) You have the Row Source Of one Set to Inventory Table and the other Machine table
3) The Values you select in the ComboBox is saved in the Fields InventoryID(FK), MachineID(FK) of Transaction table.

Now SELECT [Inventory].[Inventory_Id], [Inventory].[InventoryType] FROM Inventory; should be yor Row Source for Inventory ComboBox
Now Row source For Machine ComboBox will be:

SELECT [Machine_Inventory_link].[Machine_id], [Machine].[MachineNumber], [Machine_Inventory_link].[Inventory_Id] FROM Machine INNER JOIN Machine_Inventory_link ON ([Machine].[MachineID]=[Machine_Inventory_link].[Machine_id]) AND ([Machine].[Machine_id]=[Product_machine_link].[Machine_id]) WHERE ((([Machine_Inventory_link].[Products_Id])=[Forms]![Name of your Form]![Name of Inventory Combo]));


Don't be confused by this go to Row source Create a select Query using Machine_inventory_Link and Machine table.Select MachineID and MachineNumber from Machine Table and Inventory_Id from Machine_Inventory_Link Table and in its Criteria Type the following code [Forms]![Name of your Form]![Name of Inventory Combo]

Remember the most slight but the most important piece of Code in the AfterUpdate Event of the Inventory ComboBox type

Docmd.Requery"Name of Machine Combo"

This will do what you want. I you cant manage to do it please zip your database and upload I will be happy to do it for you.
Mark this thread solved it this solves your problem.

[Noobie]

Hi,

Thank a lot, I have manage to do it. Would you mind helping me on another problem on the same database which i have been trying a long time. I have been trying to do Running Sum on my query but do not succeed.

I have InvIN and InvOUT as mention above. So i would like to create a Running Sum in my query whereby Running Sum = (InvIN MINUS InvOUT) according to the date & time(General Date format).

InvIN InvOUT RunningSum
So when 3/18/2010 10.22AM 1000 0 1000
So when 4/18/2010 11.00AM 0 100 900

I hope you could help me solve this problem. And thank again on the combo box problem. ^^

[maximus]

Here is what I have done, I my example I have a Table7{ID(Auto Number PK), Recored_Date, InvIN, InvOUT} Now I have a Query Query10. This What I have done:
1) Created a alias of Recored_Date As MyDate
2) Created A Calculation Field RunningSum with the follwoing expression:

RunningSum: DSum("[INvIN]","Table7","[Recored_date]<=" & "#" & [MyDate] & "#")-DSum("[INvOUT]","Table7","[Recored_date]<=" & "#" & [MyDate] & "#")

Date is General Format as requested. You can get really creative with this and run the RunningSum for Groups Like Month or Year, well thats for another day.

Mark this thread solved if this solves your problem.

[maximus]

checked out the solution

[maximus]

were you able to create your running sum in your query.