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

Filter items on a ComboBox through the value in another field

Dear Experts,

I have a combobox field with a list of items. What I want it to do is to select only some of the items for a form and what items it selects will depend on the value in another field. Thus, for example, The comboBox has items A,B,C,D,E,F and when another value - S1, the combo box will display all 6 items; When the value in the other field is S2 it will display only C,D,E,F. When the other value is S3 it will display E,F. Is there any way of doing this through VBA or otherwise?

Cheers
0
robbhat
Asked:
robbhat
  • 5
  • 4
1 Solution
 
Wayne Taylor (webtubbs)Commented:
Hello robbhat,

You could do something like this....

     Private S1Values As String
     Private S2Values As String
     Private S3Values As String
     
     Private Sub Form_Load()
         S1Values = "A;B;C;D;E;F"
         S2Values = "C;D;E;F"
         S3Values = "E;F"
     End Sub
     
     Private Sub Form_Current()
         Do While Combo44.ListCount > 0
             Combo44.RemoveItem (0)
         Loop
         Select Case [OtherField]
             Case "S1": Combo44.RowSource = S1Values
             Case "S2": Combo44.RowSource = S2Values
             Case "S3": Combo44.RowSource = S3Values
         End Select
     End Sub

Regards,

Wayne
0
 
robbhatAuthor Commented:
Cheers for that. I will try it this evening (dont have database here at work) and let you know (:
0
 
robbhatAuthor Commented:
I tried that code with slight modification (Instead of S1,S2 etc, I am using the original ID for the record). One thing I have to mention that the form where it applies to is a subform of a subform of the MainForm. Not sure whether to put the code in the mainform and refer to the subform of the subform from there (If so, How?). Anyway, the code liiks like this:


Private S1Values As String
Private S2Values As String
Private S3Values As String
Private S4Values As String

Private Sub Form_Load()
         S1Values = "A;B;C;D;E;F"
         S2Values = "C;D;E;F"
         S3Values = "D;E"
         S4Values = "E"
     End Sub

Private Sub Form_Current()
         Do While Combo10.ListCount > 0
             Combo10.RemoveItem (0)
         Loop
         Select Case [Treatment_Code]
             Case "1": Combo10.RowSource = S1Values
             Case "2": Combo10.RowSource = S2Values
             Case "3": Combo10.RowSource = S3Values
             Case "4": Combo10.RowSource = S4Values
         End Select
     End Sub

On opening the subform itself independent of the other subform and mainform, it stops to Debug on the line:

"Combo10.RemoveItem (0)"

Any thoughts as to why? can it be rectified?

Ta WebTubbs,

Rob
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
Wayne Taylor (webtubbs)Commented:
Rob,

Put the code in the subform itself. The easiest way to do this is open up VB (Alt+F11), then double click the form's name in the Project Explorer (on the left side).

To fix the error, try this....

     Private S1Values As String
     Private S2Values As String
     Private S3Values As String
     Private S4Values As String
     
     Private Sub Form_Load()
         S1Values = "A;B;C;D;E;F"
         S2Values = "C;D;E;F"
         S3Values = "D;E"
         S4Values = "E"
     End Sub
     
     Private Sub Form_Current()
         Combo10.RowSource = ""
         Select Case [Treatment_Code]
             Case "1": Combo10.RowSource = S1Values
             Case "2": Combo10.RowSource = S2Values
             Case "3": Combo10.RowSource = S3Values
             Case "4": Combo10.RowSource = S4Values
         End Select
     End Sub

Wayne
0
 
robbhatAuthor Commented:
Hi Wayne

The debug has stopped, but now the combobox is not displaying any data in the Combo Box.

Let me give you the details of the fields involved to help you to troubleshoot.
The subform in question Has the Field "Treatment_ID" in its source Table(table A). This source Table is linked to another table (table B) in the 1 (Table B - Treatment_ID1 - Autonumber) to many (table A - Treatment_ID - number) relationship.

The ComboBox (Now changed to Combo12) in Table A looks up the Value "Treatment_Code" (Text field) from Table B and stores it in Treatment_ID in Table A. Every time I click on the ComboBox, it gives the message "The record source 'AF;...Den' specified on this form does not exist.

So, I physically typed in these Codes in the record source of the combo Box. when I click on it, it does not display the Codes. can you help me?

My Code now looks like this (some value alterations):

Private S1Values As String
     Private S2Values As String
     Private S3Values As String
     Private S4Values As String
     
     Private Sub Form_Load()
         S1Values = "AF;CF;RF;Cr;Br;Den"
         S2Values = "RF;Cr;Br;Den"
         S3Values = "Cr;Br;Den"
         S4Values = "Br;Den"
     End Sub
     
     Private Sub Form_Current()
         Combo12.RowSource = ""
         Select Case [Treatment_ID]
             Case "3": Combo12.RowSource = S1Values
             Case "6": Combo12.RowSource = S2Values
             Case "7": Combo12.RowSource = S3Values
             Case "8": Combo12.RowSource = S4Values
         End Select
     End Sub

Cheers, Rob

0
 
robbhatAuthor Commented:
**************Sorry Wayne....I am sending you on a wild goose chase. The link between the two fields exist between the mainform and subform 2 and not in the subform itself:

Main Form (M) from Table X has field Plan_Type = S1,S2,S3 etc.

M is linked to subform 1 (C1) from Table Y and C1 is linked to another subform (C2). C2 (From Table B) has the field Treatment_ID which looks up the value Treatment_Code from Table B. The conditions are thus:

When Plan_Type (Form M) = S1, Combox value in Form C2 is filetered to "AF;CF;RF;Cr;Br;Den"
When Plan_Type (Form M) = S2, Combox value in Form C2 is filetered to "RF;Cr;Br;Den"

Etc.

Sorry about that.

Cheers Rob
0
 
Wayne Taylor (webtubbs)Commented:
Rob,

I forgot to mention something VERY important. In order to set the RowSource like this, you need to set the Row Source Type property to 'Value List'. This should fix the error ""The record source 'AF;...Den' specified on this form does not exist".

Wayne
0
 
Wayne Taylor (webtubbs)Commented:
BTW - It would have been nice if you'd given me the oppurtunity to respond before giving a B grade. Remember, experts come from all parts of the globe (I'm from Australia), so may not be available at the same time as yourself.
0
 
robbhatAuthor Commented:
Sorry Wayne....I didnt realise the intricacies. I have other questions that have been open for days...So, I just think that experts either forget or give up.

I will make it up for you Wayne...What I have done is I have started this thread again under a different title with 250 points. Another expert is trying to help me but I think that he doesnt understand the form structure. Please go there and give me the solution Wayne and I will give you 200 with an A because you started the helping process. The Title is "Selecting values in a field in the subform according to values in a field in the mainform". If you choose to do so, please read my last post in this thread with an update of the facts (I gave u incorrect information yesterday). (:
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

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