Cascading ComboBox I Just Cant Do that!

[snirben]

This is driving me mad!

I have been trying for 3 days now to work out how to do this with no success.

I have a SubForm in DataSheet View.

And a Table of "Products".
1. ProductID
2. Type
3. SubType
4. Weight (Only 1 option for each SubType)(need to fill auto)

the table look like this:

ProductID|Type|Subtype|Weight
----1-----Iron--Steel----5
----2-----Iron--Wood-----6.7
----3-----Gold--Dark-----55
 
 

In the SubForm i have this 4 as Combobox.

i want to do this:

Pick Type
and then the Subtype will requry depends on the type i choose
and after i choose the subtype the Weight and the ProudctID will Fill in Auto.

please help me.

[PhilOfWalton]

I don't think you can the way your data is set out.

What you can do is build a form filter.
So create 2 combo boxed in the foot of the subform (I would personally use a continuous subform rather than a datasheet) with both single columns, one bound to Type and the other to SubType.

The rowsource for the first combo (Combo1) would be something like

SELECT DISTINCT [Type] FROM Products ORDER BY [Type]
 

The second Combo would be something like

SELECT DISTINCT [SubType] FROM Products 
WHERE SubType = " & Chr$(34) & Combo1 & Chr%(34)
ORDER BY [SubType]
 

On the AfterUpdate of Combo1 you need to do

Combo2.Requery
 

Then build your form filter

Phil

[snirben]

ok lets remove the ProductID

i have just
1. Type
2. SubType
3. Weight (Only 1 option for each SubType)(need to fill auto)

can it be done with one table?

[PhilOfWalton]

Can I suggest that you tell us what you are trying to achieve. I know it's hard, but forget what you've got so far, let's have a bit of information on the purpose of your Db.

Anything can be done!!

Phil

[snirben]

ok, i will try to explain,
I build in access DB to my parent company,
its Steel Company.
my tables are(Not Full but the importent stuff is in):
TblProudcts

ProductID|Type|Subtype|Weight
----1-----Iron--Steel----5
----2-----Iron--Wood-----6.7
----3-----Gold--Dark-----55
 

TblCustomer

CustomerID|Name|-------Phone|
----1------Moshe LTD---54545
----2------Iron LTD---45455
----3------Gold LTD---45454
 

TblSuppliers

SupplierID|Name|----Phone|
----1------NY LTD---54545
----2------Ir LTD---45455
----3------Go LTD---45454
 

Tblproject

ProjectID---ProjectName---CustomerID(FK)-----DateStart-----DateEnd
---3000-----Wall of steel-----2-------------28/01/2017--31/01/2017
---3001-----Iron Sky----------3-------------29/01/2017--31/02/2019
 

TblBID

--BidID--SupplierID(FK)--ProjectID(FK)------Date--
--1200------4---------------3000-------28/01/2017
 

TblBidDetails

--BiddetailsID--BidID(FK)--ProductID(FK)--Quantity--Weight--Price
-------1--------1200--------1--------------3---------5-------5$--
-------2--------1200--------37-------------5---------35------10$-

TblOrder

--OrderID--SupplierID(FK)--ProjectID(FK)------Date---BidID(Linked)
--4700--------4----------------3000-------28/01/2017--1200--------

TblOrderDetails

orderdetailsID--OrderID(FK)-ProductID(FK)--Quantity--Weight-Price
-------1--------4700--------1--------------3---------5-------5$--
-------2--------4700--------37-------------5---------35------10$-

TblCerM-When i Recive the order i need to create shipping certificate

--CerMID--SupplierID(FK)--ProjectID(FK)-----Date---OrderID(Linked)
--9700--------4----------------3000-----28/01/2017--4700--------

TblCermDetails

CermdetailsID--CermID(FK)-ProductID(FK)--Quantity--Weight-Price
-------1--------9700--------1--------------3---------5-------5$--
-------2--------9700--------37-------------5---------35------10$-

TblInvoice

--InvoiceID---Date-------Total-
--13000-------28/01/2017--5870$
 

TblInvoiceDetails

--InvoiceDetailsID--CermID-OrderID--Total.
-------------1------9700---4700-----50$

So this is how thay work:

Thay Create a project, Give it a name.
now thay need to Order Products from suppliers so thay make a
Bid and enter the Proudcts thay need. after that thay send the bid to the suplier and get back the supplier Bid to the items they ask with prices.
now thay create in Order thay "Link the Bid to the order" and all the items that in the bid Auto insert into the order Form.
Thay Create the order and send it to the suplier, now thay will recive the Proudcts in parts. 30% of the order and the rest is 70% of the order will arrive the day after or so.
they recive it with a CerM Doucment.
now thay go to the Project and open a new CerM, thay link the order to the Cerm and all the items that in the Order Auto insert into the Cerm Form.
But.. only 30% of the order is arrive. so i need to make in option to change the CerM, and he will know that.
now when i open a new Cerm and link the Order he will show me only the 70% Left.

after that in the end of the mounth i create invoice

in the invoice i can add CerM and Orders
when i add Cerm he will know that 3 more Cerm is linked to the Same Order as the first Cerm. and he will ask me if i want to add the rest of the Cerm, and if i have order with lets say 30% with out CerM he will tell me that Order didnt arrive Complletly do you want to Procced?

lets start with that i think its inaf.

by the why i need the proudcts to add as a casacde combobox, i pick type and then pick subtype(Filterdform the type) and then he will add the Weight Auto.

Thank you!

[PhilOfWalton]

Sorry for the delay in replying.

I think there are 2 options.
The simple one is to have a single combo box showing both Type & SubType which will give you your weight.
As a combo will only show the first value, I put text boxes over it with exactly the same formatting bound to the different columns. Th combo box is sent to the back

Here is an illustration,

On the final design the DDECo & DDErrorDescription are moved exactly over the Combo box.

The second alternative is to split your product table into

ProductID        1
Product Type     Iron
 

and a Second Table of SubTypes

SubTypeID        1
SubType          Steel
ProductID        Foreign Key
Weight           5
 

Then use cascading combo boxes.

Phil

[snirben]

I didn't understood the First One so well, my view is Datasheet,
I will try the second one,
but I don't need any vba code? to requry the combo box?

so I need a table of Proudcat ID with a list of the types
and a Subtype Table With Proudcat ID (FK)?

and you have idea about that: how can I make it happen? and lead?
"
in the invoice i can add CerM and Orders
when i add Cerm he will know that 3 more Cerm is linked to the Same Order as the first Cerm. and he will ask me if i want to add the rest of the Cerm, and if i have order with lets say 30% with out CerM he will tell me that Order didnt arrive Complletly do you want to Procced?"

[PhilOfWalton]

So the RowSource for the first Combo (CboProductType) is

"SELECT ProductType, ProductID FROM TblProducts ORDER BY ProductType;"
 

So the RowSource for the second Combo (CboSubType) is

"SELECT SubType, SubTypeID FROM TblSubTypes WHERE ProductID = " & CboProductType & " ORDER BY SubType;"
 

Then on the AfterUpdate of CboProductType you need

Me.CboSubType.Requery
 

Hope that helps. It's difficult to be sure without seeing the database exactly what the problems are

Phil