List box - 2 cols - 1 from SQL - 1 from RS - Possible?

kez1304 · #1 (**permalink**) 07-07-11, 05:13

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.

healdem · #2 (**permalink**) 07-07-11, 05:31

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

kez1304 · #3 (**permalink**) 07-07-11, 05:44

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...

healdem · #4 (**permalink**) 07-07-11, 06:02

how do you work out the quantity?

you may then need to do a subselect to 'attach' the stock to the product information

kez1304 · #5 (**permalink**) 07-07-11, 06:19

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.

healdem · #6 (**permalink**) 07-07-11, 06:22

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

kez1304 · #7 (**permalink**) 07-07-11, 06:31

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.

kez1304 · #8 (**permalink**) 07-07-11, 06:47

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?

kez1304 · #9 (**permalink**) 07-07-11, 07:58

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.

kez1304 · #10 (**permalink**) 07-07-11, 08:15

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?

Internet Services

Web Development

Digital Marketing

Consumer Tech