getting ADO recordset to display

[Spyridon]

I'm trying to get the information from my recordset into a field on a report. It's almost working, that is of course, almost. In the details section of the report, it does display the last number in the record. But I'm wanting the field in the details section to display each and every record. For example, "SalesType" has 4 records, 1, 101, 200, and 203. Since 203 is the last record, that is what is displayed. What's wrong with my code here?


Dim salesNum As Integer
Dim Sales As Integer
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim strsql As String


strsql = "SELECT * FROM Sales"

cnn.Open CurrentProject.Connection

rst.Open strsql, cnn

Do While Not rst.EOF
Sales = rst.Fields("SalesType&quot.Value
Me.listb.Value = "=" & Sales & ""

rst.MoveNext
Loop


Michael

 

[RoyVidar]

Hi!

Your code assignes the value of the salestype to the variable/box listb each and every time the loop runs, replacing the old value with the new one. The last entry is the one you'll see.

If you want to concatinate it, then perhaps something like this:

[tt]Do While Not rst.EOF
Sales = Sales & ", " & rst.Fields("SalesType&quot.Value
rst.MoveNext
Loop
Sales=mid$(Sales, 3)
Me.listb.Value = "=" & Sales & ""[/tt]

If you want them in textboxes below eachother, then perhaps consider using a subreport bound to the table insted.

Roy-Vidar

 

[Spyridon]

Hey thanks for the quick reply. What I'm wanting is text boxes below it, but here's my problem. I have a field that contains different types of sales. There are 16 different sales types, each with it's own number. Some of those types are two sales added together. For example, an order is placed for a pair jeans, and that order number is 100. An order could have been placed for a shirt, and that would be number 5. At the same time, both of them could have been bought, and the number would be 105.

So I need a report to list something like this:

Sales Order Jeans Shirts
1111 1 0
2222 0 1
3333 1 1

The underlying query has one column, SalesType, that has the values listed as :100,5,105

So I basically need it to extract all the 100's and count them, and then do the same for the 5's, but then take the 105's and populate two different fields. The only way that I've been able to do it in the report is with the IIF function. The problem with that is that in the report footer the fiels are summed, so Jeans would = 2, Shirts = 2,
and then Total Sales would = 4. It won't sum a value from an IIF function (or I can't get it to), so I'm left with code to do my work. Any ideas?

 

[RoyVidar]

Sorry - I don't understand. I only see a lot of unrelated numbers (100, 5, 105, 1111, 2222, 3333) in something that appears unnormalized.

I also see you've crossposted the thread in the reports forum. This is generally not appreciated. Perhaps someone else might assist.

Roy-Vidar

 

[Spyridon]

well thanks for your help anyhow. I posted a portion of this in the reports forum to see if there was possibly another option rather than using code, if so, then this would be an inappropriate forum right? The numbers are related in this way, for each sales order number (let's say 1111) there would be a type of sale, which could be 5,100,105, etc. My Sales table has the fields WorkOrderNumber, SalesType, SalesID, and ScheduleDate. Why would this be unnormalized?

Anyways, thanks for your input.