• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 393
  • Last Modified:

Combobox items shortlisted in subform of a subform according a field value in Mainform

Hi Mr.Experts...I am exhausted by this problem...Spent days on it with nout. I have a nested form matrix with Mainform A having a subform B (datasheet view with line items) which has a subform C (again datasheet view with line items).

Mainform A has a field X while subform C has a comboBox Y.

What I want to happen is this.

 When X in the mainform A has a value "S1", I want to filter the values of the comboBox Y to "AF;CF;RF;Cr;Br;Den"

When X in the mainform A has a value "S2", I want to filter the values of the comboBox Y to "AF;CF;RF;Cr"

When X in the mainform A has a value "S3", I want to filter the values of the comboBox Y to "AF;CF"

When X in the mainform A has a value "S4", I want to filter the values of the comboBox Y to "AF"

I have tried Case statements but it didnt work (My VBA knowledge is very very very basic). So I turned to If Then statements to write this code (at least I am not getting interrupted by Debugs). But the Combox Y is showing blank!!!

I am pasting my Code, which is On_Current on subform C.

In the code:

A = Fo_Main_Treatment
B = [Fo_Treatment_Course Subform] (Not involved in this code)
C = [Fo_treatment_details Subform] (Not nvolved in this code)

X filed in A = Plan_Type
Y ComboBox in C = Combo24

Note that I have tried Me.[Fo_Treatment_Course Subform].Form.Fo_main_Treatment..Form.Plan_Type.value in place of the first code line but it gives me a Debug error saying it cant find the field Plan_Type


Private Sub Form_Current()

Dim strList As String

If Form_Fo_Main_Treatment.Plan_Type.Value = S1 Then   'Calling form A from Form C  
strList = "AF;CF;RF;Cr;Br;Den"

ElseIf Form_Fo_Main_Treatment.Plan_Type.Value = S2 Then

strList = "AF;CF;RF;Cr"

ElseIf Form_Fo_Main_Treatment.Plan_Type.Value = S3 Then

strList = "AF;CF"


ElseIf Form_Fo_Main_Treatment.Plan_Type.Value = S4 Then

strList = "AF"

End If

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


End Sub

Someone Please Please give me the VBA Code....Pleaaasssseeee



0
robbhat
Asked:
robbhat
  • 10
  • 4
1 Solution
 
dqmqCommented:
Not sure you have it on the right event, but this may head you in the right direction:

Private Sub Form_Current()
Dim strList As String

If Form_Fo_Main_Treatment.Plan_Type.Value = "S1" Then   'Calling form A from Form C  
strList = "AF;CF;RF;Cr;Br;Den"

ElseIf Form_Fo_Main_Treatment.Plan_Type.Value = "S2" Then

strList = "AF;CF;RF;Cr"

ElseIf Form_Fo_Main_Treatment.Plan_Type.Value = "S3" Then

strList = "AF;CF"


ElseIf Form_Fo_Main_Treatment.Plan_Type.Value = "S4" Then

strList = "AF"
ELSE
strList = Form_Fo_Main_Treatment.Plan_Type.Value    'to see what you've got
End If

Me.Combo24.RowSource = strList
'Me.Combo24.Requery   'don't need to requery after changing RowSource

End Sub
0
 
robbhatAuthor Commented:
Yep! a Brickwall...Now its saying "The recordsource 'AF:....Den' specified on this form or report does not exist" when I click on the ComboBox....sigh
0
 
robbhatAuthor Commented:
But at least one thing for sure...the code is working for the above message shows all 6 items on the combo box when Plan_Type is S1, and when the Plan_Type is S3, only 2 items are presented on the above message.......Some good news??????
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
dqmqCommented:
check properties of the combo box.  Can't remember exactly, but it seems like there is a recordsourcetype (or something like that) property.  You may have that set to table/query. It needs to be set to value list (or something like that)
0
 
robbhatAuthor Commented:
I have put the row source to field list. The message is not showing but the fields are blank on the combo box.... Is it worth mentioning that the data in the subform where the combobox is is derived from a query called Q_Treatment_Details....Do we need to connect to this recordsource you think? If so how?

0
 
robbhatAuthor Commented:
Another small development...When I change the rowsource to value list, it is selecting some data in the combo Box but not as planned.

S1 it gives, CF,Cr,Den and not all 6 as on the code
S3 it gives CF only as opposed to 2 on the code
S4 it gives none

??/What you make of that???
0
 
robbhatAuthor Commented:
Even that is not right...

When I goto select it, it says, "The value you have chosen isnt correct for this field"

yipes
0
 
robbhatAuthor Commented:
Interestingly, If I replace the items AF, Cr etc with their number equivalents (The ID for these items on the Table), it is allowing the selection. After selecting the number it shows the correct text code (AF, Cr etc, But the choices are limited to 3 items on S1 instead of 6, 1 item on S3 instead of 2 as mentioned above.

?A positive step?
0
 
dqmqCommented:
Right, Row Source Type should be Value List.
Make Column Count = 1 (Looks to me like it is 2)
Make sure to space out column width (looks to me like you have a 0;)
Make sure bound column  = 1
0
 
robbhatAuthor Commented:
You are Fantastic....But job not fully done yet...Please suggest something to make it practical....Although it is choosing the correct listing (S1 = 7,8,9,10 etc), the problem now is that I cant get the code equivalent (AF,Br etc), which is vital to make the combobox practicable. Can you give it the final magic touch? The Code that works is:

Private Sub Form_Current()


Select Case Form_Fo_Main_Treatment.Plan_Type.Value     'Calling upon Mainform A field X (Plan_Type field)

      Case "S1"
         strList = "7;8;9;10;11;12"
      Case "S2"
        strList = "7;8;9;10"
         
     Case "S3"
         strList = "7;8"
         
    Case "S4"
         strList = "7"

End Select

 Me.Combo24.RowSource = strList
''Me.Combo24.Requery

End Sub
0
 
dqmqCommented:
Oh, I think we are very close.

But, I do not understand where
  strlist = "7;8;9;10;11;12"

came from. Last I remember, you were setting it to
   strList = "AF;CF;RF;Cr;Br;Den"

What is the purpose of the numbers?

0
 
robbhatAuthor Commented:
To explain myself I have uploaded the stripped database onto EEStuff. The url is:

View all files for Question ID: 22753111
https://filedb.experts-exchange.com/incoming/ee-stuff/4314-COMBO24.zip 

Direct link to your file
https://filedb.experts-exchange.com/incoming/ee-stuff/4314-COMBO24.zip 

The problem I am having is this:

If I do the comboBox to get the Treatment_Code (AF,CF etc) from Tbl_Treatment_options and store it in a Text field created in the Tbl_Treatment_details (All through the form), the box does give the list according to the VBA, but it does not show the corresponding Treatment_Description on the form, which makes the form impractical. But if I store the numeric data (as is shown on the database that I hace sent to you), the corresponding Description also shows on the form (ie. there is the link between the two fields.

See what you think
0
 
robbhatAuthor Commented:
I have done it....It works!It Works!It works...Thanks
0
 
robbhatAuthor Commented:
dqmg provided me with the key to solve my problem...Thanks
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 10
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now