R
Robbhat
Hi Community...I need some help with this select case VBA. I have the
following VBA in On_Current of a Subform...If, The Case is a certain value in
two fields (Plan_Type [Text] and Plan_Iss_No[Number]) in the Parent of a
Parent of the Subform then a Combobox in the subform (Combo24) displayes the
strList according to the values to choose from.
As it can be seen from the second case statement,
The Items on the List stay the same if the Plan_Type is S1 and the Issue no
is 1 or 2 (or 3 or 4 etc). The idea is this:
In Year 1 (Plan Issue No 1), a customer with a Plan_Type of S1 will be sold
goods A,B,C etc at a certain cost say A for £100; B for £200, C for £300 etc.
In Year 2 (issue no 2), The Plan_types are the same (S1, S2 etc) and the
goods are the same (A,B,C), the only thing thats different is the value of
the goods which maybe 10 to 20% higher. Thus, the prices of the goods are
reset (issued) under Issue No 2.
So, what I want my subform to display (In Field Cost) is the corresponding
prices of the goods A,B,C depending on the Issue No on the Mainform (Parent
of a Parent of the subform).
How can this be done if for eg the costs of A,B and C are 20% higher under
issue 2 (A= 120;B=240;C=360 etc)
Select Case Form_Fo_Main_Treatment.Plan_Type.Value &
Form_Fo_Main_Treatment.Plan_iss_No
Case "S0"
strList = "A;'ItemA';B;'Item B';C;'Item C';D;'Item D';E;'Item
E';F;'Item F'"
Case "S1" & "1", "S1" & "2"
strList = "A;'ItemA';B;'Item B';C;'Item C';D;'Item D'"
Case "S1*" & "1", "S1*" & "2"
strList = "A;'ItemA';B;'Item B'"
'Etc
End Select
Me.Combo24.RowSource = strList
Me.Combo24.Requery
Thanks very much
rob
following VBA in On_Current of a Subform...If, The Case is a certain value in
two fields (Plan_Type [Text] and Plan_Iss_No[Number]) in the Parent of a
Parent of the Subform then a Combobox in the subform (Combo24) displayes the
strList according to the values to choose from.
As it can be seen from the second case statement,
The Items on the List stay the same if the Plan_Type is S1 and the Issue no
is 1 or 2 (or 3 or 4 etc). The idea is this:
In Year 1 (Plan Issue No 1), a customer with a Plan_Type of S1 will be sold
goods A,B,C etc at a certain cost say A for £100; B for £200, C for £300 etc.
In Year 2 (issue no 2), The Plan_types are the same (S1, S2 etc) and the
goods are the same (A,B,C), the only thing thats different is the value of
the goods which maybe 10 to 20% higher. Thus, the prices of the goods are
reset (issued) under Issue No 2.
So, what I want my subform to display (In Field Cost) is the corresponding
prices of the goods A,B,C depending on the Issue No on the Mainform (Parent
of a Parent of the subform).
How can this be done if for eg the costs of A,B and C are 20% higher under
issue 2 (A= 120;B=240;C=360 etc)
Select Case Form_Fo_Main_Treatment.Plan_Type.Value &
Form_Fo_Main_Treatment.Plan_iss_No
Case "S0"
strList = "A;'ItemA';B;'Item B';C;'Item C';D;'Item D';E;'Item
E';F;'Item F'"
Case "S1" & "1", "S1" & "2"
strList = "A;'ItemA';B;'Item B';C;'Item C';D;'Item D'"
Case "S1*" & "1", "S1*" & "2"
strList = "A;'ItemA';B;'Item B'"
'Etc
End Select
Me.Combo24.RowSource = strList
Me.Combo24.Requery
Thanks very much
rob