Cascade Comobox.... i have trying for 2 weeks now! Please Help me.

[snirben]

Hi, so i try everything! Nothing works for me always Problems xD

Let me Explain.

I have proudcts ,
I want to add proudcts by Combobox.
so first i want the user to pick Catagory and then MainType And then SubType and then Weight.

Catagory
MainType
SubType
Weight

So i devide my Proudct into 3 Tables
Lets say Catagory=Parit.

tblParit

ParitID|Parit
---1---|-Gold-
---2---|-Iron-

TblMaintype

MainTypeID|--MainType---|ParitID
----1-----|-Gold Sword--|--1----
----2-----|-Gold Hand---|--1----
----3-----|-Iron Door---|--2----

TblSubType

SubtypeID|SubType|Weight-|MainTypeID
----1----|-Long--|--33---|---1-----
----2----|-Short-|--45---|---1-----
----3----|--Half-|--67---|---2-----
----4----|-Finger|--32---|---2-----
----5----|-All---|--11---|---2-----
----6----|-Blue--|--88---|---3-----

So i create new form
Inside i have a sub form.
inside the subfrom i put 4 Combobox

1. CboParit

SELECT TblParit.ParitID, TblParit.[parit] FROM TblParit GROUP BY TblParit.ParitID, TblParit.[parit]; 

With AfterUpdate:

Me.CboMainType.Requery
Me.CboMainType = Me.CboMainType.Column(0, 0)
    Me.CboMainType.SetFocus
    Me.CboMainType.Dropdow

n


2. CboMainType
SELECT TblMainType.MainTypeID, TblMainType.MainType FROM TblMainType GROUP BY TblMainType.MainTypeID, TblMainType.MainType, TblMainType.ParitID HAVING (((TblMainType.ParitID)=[Form]![Cboparit])) ORDER BY TblMainType.MainType;

With AfterUpdate:

Me.CboSubType.Requery
  Me.CboSubType = Me.CboSubType.Column(0, 0)
    Me.CboSubType.SetFocus
    Me.CboSubType.Dropdown

3. CboSubtype
SameIdea
4. CboWeight
SameIdea

the subform is Datasheet.
My problem is when i choose new record it requery all of the recoreds and all vaules are gone.

Gif For Exemple:

[PhilOfWalton]

Unless I am totally misunderstanding things, I assume that once the type & subtype are selected, the weight is known.

So MainType = Gold Hand, SubType = Finger will give weight = 32

What am I missing?

Phil

[snirben]

Yes you Right,
i try do that in a Datasheet view, but everytime i select a Subtype or Maintype, all the data in the previous column get Requrey and get blank.
there is a way to do it in datasheetview?

[PhilOfWalton]

Sorry, I never use datasheets, only continuous forms (which are pretty similar providing the information can be placed on one line.

Your clever little GIF file appears to be working perfectly, except you don't need a dropdown for the weight.

It had me foxed for a moment till I saw the writing was in Hebrew and everything went from right to left.

What is the control source of the SubType Combo?
It should be something like

SELECT SubType, SubTypeID FROM TblSubTypes WHERE MainTypeID = " & CboType
 

I am assuming that the Type Combo Box is bound to MainTypeID

On the AfterUpdate of the Type Combo Box you need

CboSubTye.Requery
 

Phil