Results 1 to 5 of 5
  1. #1
    cpullen91 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2016
    Posts
    9

    List box update from combo boxes

    Hello,



    I am having a bit of trouble linking a list on my form to combo boxes on the smae form.

    They are cascading combo boxes and i am trying to filter the list depending on what is selected in the combo boxes.

    here is my code

    Code:
    Private Sub FilterMachineList()
      
      Dim strRS As String
      
      ' Filter the list box appropriately based on the combo box selection(s)
      strRS = "SELECT qrymachine.SuffixName"
    
    
      If Not IsNull(Me.cboSuffixID) Then
        strRS = strRS & " WHERE SuffixID = " & Me.cboSuffixID
      ElseIf Not IsNull(Me.cboSizeID) Then
        strRS = strRS & " WHERE SizeID = " & Me.cboSizeID
      ElseIf Not IsNull(Me.cboModelID) Then
        strRS = strRS & " WHERE ModelID = " & Me.cboModelID
      End If
      
      strRS = strRS & " ORDER BY qrymachine.SuffixName;"
     
      Me.lstMachineID.RowSource = strRS
      
      Me.lstMachineID.Requery
      
    
    
    End Sub
    the order that the combo boxes are selected is Model ID, Size ID and finally Suffix ID.

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    so what is you question? If it is that the order is wrong then swap the order of your if statement, putting ModelID first.

    Edit: also just noticed - your SQL does not include the FROM part

  3. #3
    cpullen91 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2016
    Posts
    9
    Quote Originally Posted by Ajax View Post
    so what is you question? If it is that the order is wrong then swap the order of your if statement, putting ModelID first.

    Edit: also just noticed - your SQL does not include the FROM part
    I'm Fairly new to coding, what is the FROM part?

  4. #4
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    sql statements (which is what you are creating) have several parts - a simple SELECT query for example

    1. SELECT - some fields - required
    2. FROM - a table or query - required
    3. WHERE - criteria
    4. ORDER BY - a field or fields

    other types of queries (insert, update, delete, group by, etc) also have these parts plus additionally ones

    If you were to create your rowsource query in the query builder for one of your options, then look at the SQL view, you will see what I mean.

    Otherwise, your query syntax looks correct.

    Also you do not need the requery when you change the rowsource - that happens anyway when the rowsource is changed so you are actually requerying twice.

    Google 'SQL syntax' or similar to find out more

  5. #5
    cpullen91 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2016
    Posts
    9
    Thanks,

    Got it sorted now.

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

Similar Threads

  1. Replies: 1
    Last Post: 03-19-2016, 03:56 AM
  2. need help with Combo/List Boxes
    By SSUTTON in forum Forms
    Replies: 7
    Last Post: 04-01-2015, 11:12 AM
  3. Replies: 3
    Last Post: 08-12-2014, 02:12 PM
  4. Cascading Combo / List Boxes
    By plowe in forum Programming
    Replies: 5
    Last Post: 09-07-2012, 10:55 AM
  5. Combo/List Boxes
    By refryguy in forum Forms
    Replies: 3
    Last Post: 07-28-2011, 01:34 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