Results 1 to 4 of 4
  1. #1
    lccrews is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Location
    Los Angeles
    Posts
    194

    How To Pre-Populate A Combo Box Based On Another

    Forgive me but I think that I might be a little dense. I have read a number of articles on populating one combo box based on another. I'm not understanding it very well and I'm hoping someone here can break it down barney style for me. I have:

    tbl_RMS
    [RawMaterial] [SpecLvl]
    101114 | 4
    101114 | 5
    210225 | 1





    On a form: I have one combo box set with a rowsource of [RawMaterial] from tbl_RawMaterial (A different table). My second combo box is a simple list of 1-16. Right now the user has to already know what spec is available for viewing. I'd like to make it simpler and, via VBA, set the a rowsource of combo box #2 to be 4-5 automatically. Here's what I have so far (plase don't laugh):

    Code:
    Private Sub RM_Click()
    With Me![SpecLvl]
    If IsNull(Me!RM) Then
        .RowSource = ""
    Else
        .RowSource = "SELECT [SpecLevel] " _
                   & "FROM tbl_RMS " _
                   & "WHERE tbl_RMS.[RawMaterial] = Me!RM;"
    End If
    End With
    End Sub
    When I use this code, [SpecLvl] is prepopulated with "SELECT" and not 4-5 when I type in 101114.

    I've also tried:
    Code:
    Private Sub RM_Click()
    Dim sSQL As String
    
    sSQL = "SELECT [SpecLevel] " _
         & "FROM tbl_RMS " _
         & "WHERE tbl_RMS.[RawMaterial] =" & Me!RM
         
    With Me![SpecLvl]
    
    
    If IsNull(Me!RM) Then
        .RowSource = ""
    Else
        .RowSource = sSQL
    End If
    End With
    End Sub

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Please post your tables and relationships window as a jpg so we can get some understanding of your data in context.

    Here are 2 older free videos from Datapig that go through the general setting up of cascading combos.

    http://www.datapigtechnologies.com/f...combobox1.html
    http://www.datapigtechnologies.com/f...combobox2.html

  3. #3
    lccrews is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Location
    Los Angeles
    Posts
    194
    Thank you for your comment Orange. The video showed me a way to accomplish my goal without a lot of VBA. I just set the rowsource to a query with Me!RM as the criteria and added an after update event to requery Me!SpecLvl. Thanks so much!

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    You're welcome. Datapig has great Access tutorials.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Populate combo box based on value chosen in another combo box
    By Gary Childress in forum Database Design
    Replies: 3
    Last Post: 01-12-2013, 09:44 PM
  2. Replies: 1
    Last Post: 07-11-2012, 08:36 AM
  3. Replies: 33
    Last Post: 01-13-2012, 07:44 AM
  4. How to populate text box based on Combo Box
    By maxstead in forum Forms
    Replies: 3
    Last Post: 09-08-2011, 01:25 PM
  5. Replies: 4
    Last Post: 01-24-2011, 07:11 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums