Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

How to change combo box data on a subform when a certain selection is made from a combo box on a main form

Posted on 2011-02-21
15
Medium Priority
?
500 Views
Last Modified: 2012-05-11
I have a form1 (frmEmployee_Audits)  that has a subform (frmQuality_Review_Subform).  There is a combo box (cboRegion) on form1 (frmEmployee_Audits) that if the user chooses "East" that the combo box (cboQualRevCriteria) on the subform (frmQuality_Review_Subform) should change to a specific listing that is in a table called "tblCode_East", otherwise it should change the combo box on the subform to list the data in a table called "tblCode_West".

I have posted my code below in the Code box.  When I run my code I get an error saying that it can't find the form "frmQuality_Review_Subform" referred to in a macro expression or Visual Basic Code, and the combo box dropdown(cboQualRevCriteria) on the subform (frmQuality_Review_Subform) is blank.

I have the code on the Deactivate Event Procedure.

What am I doing wrong?

Thanks,

gdunn59
Private Sub Form_Deactivate()
    If Me.Region = "East" Then
        Forms!frmQuality_Review_Subform!cboQualRevCriteria.SetFocus
        Forms!frmQuality_Review_Subform!cboQualRevCriteria = "Combo Box"
        Forms!frmQuality_Review_Subform!cboQualRevCriteria.RowSource = "select * from tblCode_East"
        Forms!frmQuality_Review_Subform!cboQualRevCriteria.Requery
    Else
        Forms!frmQuality_Review_Subform!cboQualRevCriteria.RowSource = "select * from tblCode_West"
        Forms!frmQuality_Review_Subform!cboQualRevCriteria.Requery
    End If
    
End Sub

Open in new window

0
Comment
Question by:gdunn59
  • 9
  • 6
15 Comments
 
LVL 1

Author Comment

by:gdunn59
ID: 34946508
Meant to say on a subform, not on a "second from" in the Title.

Thanks,
gdunn59
0
 
LVL 1

Author Comment

by:gdunn59
ID: 34946528
It is on the Deactivate Event Procedure of form1 (frmEmployee_Audits).

Forgot to state that.

Thanks,
gdunn59
0
 
LVL 3

Expert Comment

by:DockieBoy
ID: 34946676
Your subform is effectively a control on your main form.

Me.[subform_name].Form.[control_name] =
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 3

Expert Comment

by:DockieBoy
ID: 34946702
Sorry, I should clarify that.  "[subform_name]" is not the name of your sub form, it is the name of the control on your form that holds the sub form.  :)

"[control_name]" is the name of the control on your sub form.  
0
 
LVL 1

Author Comment

by:gdunn59
ID: 34946732
Ok, I'm still a little confused.  I need the subform control_name "cboQualRevCriteria (combo box dropdown) list to change depending on what the user chooses in the main form (frmEmployee_Audits) control_name "cboRegion (combo box dropdown) list.

Thanks,
gdunn59
0
 
LVL 3

Expert Comment

by:DockieBoy
ID: 34946770
On your main form in design view, make sure that the sub form is not selected, then right click on the sub form, select properties, click on the "all" tab and at the very top of that list, tell me what the name is.  :)
0
 
LVL 1

Author Comment

by:gdunn59
ID: 34946785
It is:

frmQuality_Review_Subform
0
 
LVL 3

Expert Comment

by:DockieBoy
ID: 34946797
Sorry, I also need to clarify what "Me.Region" is that you refer to in your code.  Is that a text box that has the value of the combo box "cboRegion" in it?
0
 
LVL 1

Author Comment

by:gdunn59
ID: 34946838
Not it is a separate combo box on the subform.  That is the combo box that I need to change the data that appears in that combo box depending on what the user chooses from the combo box cboRegion on the main form.
0
 
LVL 1

Author Comment

by:gdunn59
ID: 34946845
Sorry not on the subform, but on the main form.
0
 
LVL 1

Author Comment

by:gdunn59
ID: 34946867
Please let me clarify.

The cboRegion combo box is on the main form (frmEmployee_Audits).

Depending on what the user chooses in this cboRegion combo box on the main form (frmEmployee_Audits), I need the combo box (cboQualRevCriteria) on the subform (frmQuality_Review_Subform) to change based off of what was chosen in the cboRegion combo box on the main form (frmEmployee_Audits).

Hope this makes sense and I'm not confusing you too much!

Thanks,
gdunn59
0
 
LVL 3

Accepted Solution

by:
DockieBoy earned 1000 total points
ID: 34946944
Ok, cool, try this then.

Private Sub cboRegion_Change()
Dim MyString As String
 Select Case Me.cboRegion.Text
  Case "East"
   MyString = "select * from tblCode_East;"
   Me.frmQuality_Review_Subform.Form.cboQualRevCriteria.RowSource = MyString
   Me.frmQuality_Review_Subform.Form.cboQualRevCriteria.Requery
  Case "West"
   MyString = "select * from tblCode_West;"
   Me.frmQuality_Review_Subform.Form.cboQualRevCriteria.RowSource = MyString
   Me.frmQuality_Review_Subform.Form.cboQualRevCriteria.Requery
  Case Else
   MsgBox "Subform Can Not Be Updated From This Value"
 End Select
End Sub

This will update the combo on the sub form when the user changes the selection in the combo on the main form.  

The code is for the on change event of the combo "cboRegion"  on the main form

Sorry, I haven't tested this, so let me know how you go.  :)
0
 
LVL 1

Author Comment

by:gdunn59
ID: 34946983
Great, that worked!

You're wonderful.

I will accept the last solution you provided.

Thanks much,
gdunn59
0
 
LVL 1

Author Closing Comment

by:gdunn59
ID: 34946990
Works like a charm.

Thanks much!
0
 
LVL 3

Expert Comment

by:DockieBoy
ID: 34947003
Happy to help  :)
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

971 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question