Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297

    Unanswered: Empty multicolumn listbox

    Hi one, hi all!

    Having a little bit of bother here...

    I have a multicolumn listbox, 'orderContentsDetail'. It has 3 columns, and is populated by:

    Code:
    strSQL = "SELECT main.[Product Code], ref_products.[Product vName] AS " & _
             "[Product Description], main.[Serial Number], main.[ID] " & _
             "FROM main INNER JOIN ref_products " & _
                 "ON ref_products.[Product Code] = main.[Product Code] " & _
             "WHERE [Order Number] = " & Me.orderNum.Value & " " & _
             "AND main.[Delete] = FALSE " & _
             "ORDER BY main.[Product Code]"
    
    Me.orderContentsDetail.RowSource = strSQL
    The main.[ID] above doesn't show on the multicolumn listbox, it's merely there to identify otherwise indistinguishable records. So that's not an issue.


    The problem is, that if the join can't be made, because a 'ref_products.[Product Code]' matching the 'main.[Product Code]' can't be found, then no part of that record is listed in the listbox. Not even just the 'main.[Product Code]'.

    What I'm basically looking for is a way to do the above, but in the case that a match for the join can't be found, act as if the join isn't there for that record, and simply put:

    Code:
    Product Code | Product Description | Serial Number
    -------------+---------------------+--------------
      GEJOG35D   | Product Descript 1  |   33561613
      GEJON11X   |                     |   51224356
      SLDFG11E   | Product Descript 2  |   37268221
    If you follow what I mean?

    Can anyone suggest how this can be achieved?


    Thanks a lot guys!
    Looking for the perfect beer...

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Replace "INNER JOIN" by "LEFT JOIN" in the query.
    Have a nice day!

  3. #3
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    Hahahaha.

    Of course!

    Gaaawd... This is why I should phone in sick on Mondays. *Facepalm*

    Cheers buddy.
    Looking for the perfect beer...

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •