Urgent VBA help

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need urgent help to solve a VBA problem. What is the syntax of the Select
case statement for the following:

I have to fields in a subform...One is a combo box which has values A,ItemA;
B,ItemB etc

The other is a Cost field which is a combo box with number values
corresponding to each item....£100 and £120 for A, £200 and £240 for B etc.

What I want the Selec Case statement to do is to connect these two fields
according to criteria on the Mainform :

The mainform has 2 fields: Plan_Type (values are S1,S2,3 etc) and Issue_No
(values 1,2,3 etc)

The case statement I have got is:

Plan_Type Issue No Or Plan_Type Issue No
case S1 & 1 , Case S1 & 2

StrList = "A;'Item A';B;'Ite B'" etc


Plan_Type Issue No Or Plan_Type Issue No
case S2 & 1 , Case S2 & 2

StrList = "c;'Item C';D;'Item D'" etc

The strList is then displayed in the combobox according to the criteria

What I need is a code which is something like this:

When Plan_Type = S1 and ssue_No = 1, then StrList = "A;'Item A';B;'Ite B'"
etc and Cost field = 100 when Item is A, Cost Field = 200 when Item is B

When Plan_Type = S1 and ssue_No = 2, then StrList = "A;'Item A';B;'Ite B'"
etc and Cost field = 130 when Item is A, Cost Field = 240 when Item is B

How can I do this in a Case Statement please?

Please Help

rob
 
Select Case True
Case Plan_Type = "S1" AND Issue_No = 1
StrList = "A;'Item A';B;'Ite B'"
Select Case Item
Case "A"
Cost_field = 100
Case "B"
Cost_field = 200
Case Else
MsgBox "Unexpected Value. Setting Cost_Field to default of
100"
Cost_field = 100
End Case
Case Plan_Type = "S1" AND Issue_No = 2
StrList = "A;'Item A';B;'Ite B'"
Select Case Item
Case "A"
Cost_field = 130
Case "B"
Cost_field = 240
Case Else
MsgBox "Error: Unexpected value for 'Item'. Setting
Cost_Field to default of 130"
Cost_field = 130
End Case
Case Plan_Type = "S2" AND Issue_No = 1
'..........yada, yada
Case Else
MsgBox "Error: Unexpected 'Plan_Type' and 'Issue_No' combination"
' Do something?
End Select


HTH,
 
Hi George Nicholson....Your help means a lot to me...Your code is almost
working...But the Cost Field is not updating when a change or input is made
on the Combo24 comboBox, which holds the values A,B,G,D etc values. My code
is as follows.

I have put a requery on the VBA and the following requery codes in the
On_Change and After_Update Events on Combo24...Doesnt make any
difference...Please help me with this:

Me.Cost.Requery


THE CODE

Private Sub Form_Current()

'Multiple value code: Select Case Form_Fo_Main_Treatment.Plan_Type.Value &
Form_Fo_Main_Treatment.Plan_iss_No

'Case "S0" & "1"


Select Case Form_Fo_Main_Treatment.Plan_Type.Value

Case "S0"
strList = "A;'A';B;'B';C;'C';D;'D';E;'E';F;'F';G;'G';H;'H'"
Select Case Me.Combo24 & Form_Fo_Main_Treatment.Issue_No.Value
Case "A" & "1"
Me.Cost = 100
Case "A" & "2"
Me.Cost = 130

Case "B" & "1"
Me.Cost = 200
Case "B" & "2"
Me.Cost = 230

Case "C" & "1"
Me.Cost = 300
Case "C" & "2"
Me.Cost = 330

Case "D" & "1"
Me.Cost = 400
Case "D" & "2"
Me.Cost = 430

Case "E" & "1"
Me.Cost = 500
Case "E" & "2"
Me.Cost = 530

Case "F" & "1"
Me.Cost = 600
Case "F" & "2"
Me.Cost = 630

Case "G" & "1"
Me.Cost = 700
Case "G" & "2"
Me.Cost = 730

Case "H" & "1"
Me.Cost = 800
Case "H" & "2"
Me.Cost = 830



End Select

Me.Cost.Requery

End Select

Me.Combo24.RowSource = strList
Me.Combo24.Requery
Me.Cost.Requery


End Sub
 
Hia George...Your code has helped me immensely and it almost works...I owe
you a debt of gratitude...But, one more thing is needed...the Cost Field is
not updating when a change or input is made on the Combo24 comboBox, which
holds the values A,B,G,D etc values. My code is as follows.

I have put a requery on the VBA and the following requery codes in the
On_Change and After_Update Events on Combo24...Doesnt make any
difference...Please help me with this:

Me.Cost.Requery


THE CODE

Private Sub Form_Current()

'Multiple value code: Select Case Form_Fo_Main_Treatment.Plan_Type.Value &
Form_Fo_Main_Treatment.Plan_iss_No

'Case "S0" & "1"


Select Case Form_Fo_Main_Treatment.Plan_Type.Value

Case "S0"
strList = "A;'A';B;'B';C;'C';D;'D';E;'E';F;'F';G;'G';H;'H'"
Select Case Me.Combo24 & Form_Fo_Main_Treatment.Issue_No.Value
Case "A" & "1"
Me.Cost = 100
Case "A" & "2"
Me.Cost = 130

Case "B" & "1"
Me.Cost = 200
Case "B" & "2"
Me.Cost = 230

Case "C" & "1"
Me.Cost = 300
Case "C" & "2"
Me.Cost = 330

Case "D" & "1"
Me.Cost = 400
Case "D" & "2"
Me.Cost = 430

Case "E" & "1"
Me.Cost = 500
Case "E" & "2"
Me.Cost = 530

Case "F" & "1"
Me.Cost = 600
Case "F" & "2"
Me.Cost = 630

Case "G" & "1"
Me.Cost = 700
Case "G" & "2"
Me.Cost = 730

Case "H" & "1"
Me.Cost = 800
Case "H" & "2"
Me.Cost = 830



End Select

Me.Cost.Requery

End Select

Me.Combo24.RowSource = strList
Me.Combo24.Requery
Me.Cost.Requery


End Sub
 
Not sure what you need.
Your code is in the Form_Current event. Does the Cost field change like it's
supposed to when Combo24 changes as you move from record to record?

Is the remaining issue that you also need to have Cost field change when the
user makes a change to Combo24 directly?
If so, I would:
1) move the entire "Select Case Me.Combo24 &
Form_Fo_Main_Treatment.Issue_No.Value...End Select" to a separate sub called
UpDateCostField (and delete that section from Form_Current).

2) Then call UpDateCostField from the various places the related values
might change:
-Combo24_AfterUpdate
-Issue_No_AfterUpdate
-Form_Current.

HTH,
 
George...You are Fantastic.....This short email has solved a problem which I
was working on for a week...Thanks...You did this voluntarily and that is
most amazing....I dont know how I can repay you for this debt....Please let
me know (:
 
Back
Top