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

    Question Unanswered: List box - 2 cols - 1 from SQL - 1 from RS - Possible?

    Guess who's back, back again...

    Hi all,

    Hopefully someone can shed some light on this, as I can't seem to work it out.

    I have a list box, that populates itself when a button is pushed, using the follow:

    Code:
    strMLSQL = "SELECT [Product vName] " & _
               "FROM main INNER JOIN ref_products ON ref_products.[Product Code] = main.[Product Code] " & _
               "WHERE [ID] IN (" & ids & ")"
    I then use:

    Code:
    mainList.RowSource = strMLSQL

    Which works fine, not a problem there.

    Now, I have two columns setup in this 'mainList' listbox. The second column in each row, I want to be populated with the quantity of each product. Sooo... I created a recordset which does the job, using:

    Code:
    Dim rsTmp As DAO.Recordset
    Dim tmpInt As Integer
    Set rsTmp = CurrentDb.OpenRecordset(strMLSQL)
    rsTmp.MoveLast
    tmpInt = rsTmp.RecordCount
    Which does the job of assigning the quantity involved within the tmpInt variable.


    The problem is, I want both 'strMLSQL' and 'tmpInt' to populate the listbox. I've tried:

    Code:
    mainList.RowSource = strMLSQL, tmpInt
    But, as you'd expect, SYNTAX ERROR!!!

    What I'd like to know is:

    1) Is this possible?
    2) If so, how should I go about it?
    3) If not, how else can I achieve the desired results?


    Thanks in advance guys, hope that's clear enough. I've tried google, but it doesn't like long search strings.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    yes its possible to do this
    the key is marshalling the data so you can then populate the list/combo box as required

    a list box can have either
    a SQL query (whether thats from a single table or multiple tables or whatever)
    OR
    a developer supplied list which can be set a design and/or run time

    so the key is can you effectively merge your two queries into a single one.

    a problem you may need to consider is that stock level may be volatile so storing this in a list / combo box for this may not be the best approach. you may be better to use a dlookup or issue a query to retireve the current stock when the user selects that product
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    Thanks for your quick reply.

    The problem is, I can't have a quantity field in the table, as each product is a seperate entity, as they have a serial number associated with them.

    Which is where I fall into trouble, as I don't know how to get the total number of records found from an SQL query without using VBA.

    Is it actually possible to have SQL tell me this? If it is, I can setup a query that will contain the two bits of data and that should be problem solved.

    I just don't know how else to get the recordCount without using the method I described earlier.

    Is there some SQL command that will return the number of records its found? If so, please enlighten me, lol.

    The only other way I can think, is to populate two seperate lists, each placed next to each other, simultaneously with their seperate data. So one list for the name, one for the quantity. As the lists are being populated during a while loop, this should work, but it seems awfully messy for something that should be fairly straightforward.


    EDIT:

    Code:
    strMLSQL = "SELECT [Product vName] " & _
                       "FROM main INNER JOIN ref_products ON ref_products.[Product Code] = main.[Product Code] " & _
                       "WHERE [ID] IN (" & ids & ")"
            
            Dim rsTmp As DAO.Recordset
            Dim iTmp As Integer
            Set rsTmp = CurrentDb.OpenRecordset(strMLSQL)
            rsTmp.MoveLast
            iTmp = rsTmp.RecordCount
            mainQntList.RowSource = iTmp
                    
            strMLSQL = "SELECT DISTINCT [Product vName] " & _
                       "FROM main INNER JOIN ref_products ON ref_products.[Product Code] = main.[Product Code] " & _
                       "WHERE [ID] IN (" & ids & ")"
            Set rsTmp = CurrentDb.OpenRecordset(strMLSQL)
            mainList.RowSource = strMLSQL
    This is my hacked version that I quickly made. It works, but is far from ideal...
    Last edited by kez1304; 07-07-11 at 06:54.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    how do you work out the quantity?

    you may then need to do a subselect to 'attach' the stock to the product information
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    The quantity is worked out by using an SQL statement to SELECT all the records that fit the criteria.

    Code:
            strMLSQL = "SELECT [Product vName] " & _
                       "FROM main INNER JOIN ref_products ON ref_products.[Product Code] = main.[Product Code] " & _
                       "WHERE [ID] IN (" & ids & ")"
    I then open a recordset with strMLSQL as the argument:

    Code:
    Dim rsTmp As DAO.Recordset
    Set rsTmp = CurrentDb.OpenRecordset(strMLSQL)
    I can then use the following, to find the total number of records stored in the recordset.

    Code:
    rsTmp.MoveLast
            iTmp = rsTmp.RecordCount
    iTmp then holds the total number of matches my SQL statement found. Which is the quantity of products that match a given criteria.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so what do you not have in the query thgat returns the quantity that you need in the list box?

    have you considered using a select count(mycolumn)
    ms access select count - Google Search
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    Funny you should mention that, just discovered it exists. Trying to get it to work now.

    I've only been using SQL and VB for about a week. I should probably try and find a reference sheet somewhere with all the commands on it.

    I'll keep you updated as I make (or don't make ) progress.

  8. #8
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    Right, nearly there at last...

    I have:

    Code:
    strMLSQL = "SELECT [Product vName], COUNT(1) AS 'ID' " & _
                       "FROM main INNER JOIN ref_products ON ref_products.[Product Code] = main.[Product Code] " & _
                       "WHERE [ID] IN (" & ids & ")"
            
            Me.testList.RowSource = strMLSQL
    The COUNT works, if I take out the [Product vName] bit, but I can't get it into the two columns on my list.

    Examples I can find on the net use aList.AddItem, which access 2000 (the one I'm using), does not have...

    Any ideas on how to get those into their respective columns?

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

    Angry

    Right, really having a tough time with this.

    I can't for the life of me get it to populate properly.

    The listbox won't accept two seperate queries for its columns, it won't accept a table reference for one column and a value for the other.

    All I want is a listbox with a product name in one column, and the amount of times it appears in its table in the other column.

    Writing the query like:

    Code:
    strMLQSQL = "SELECT [Product vName], COUNT(1) AS 'ID' " & _
                        "FROM main INNER JOIN ref_products ON ref_products.[Product Code] = main.[Product Code] " & _
                        "WHERE [ID] IN (" & ids & ")"
    Doesn't input anything into the listbox at all when setting it as its source.

    Putting either:

    Code:
    strMLQSQL = "SELECT [Product vName] "
    Or,

    Code:
    strMLQSQL = "SELECT COUNT(1) AS 'ID'"
    As the first line in the SQL string, both work, and will populate the first column in the listbox with the correct information. I just can't, for the life of me, get them to populate the columns correctly.

    Been trying with this for hours now and getting a little discouraged. Anymore assistance would be a great help.

    Thanks once again.

  10. #10
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    FINALLY!!

    Got it working... I needed a 'GROUP BY' statement with what I wanted returned in the first column. So using:

    Code:
            strMLQSQL = "SELECT [Product vName], COUNT(1) AS 'ID' " & _
                        "FROM main INNER JOIN ref_products ON ref_products.[Product Code] = main.[Product Code] " & _
                        "WHERE [ID] IN (" & ids & ") " & _
                        "GROUP BY [Product vName]"
    Did the trick.

    Thanks for all your help mate!

    If anyone reads this, do you think you explain briefly what a GROUP BY statement does?

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
  •