Can someone explain to me how to take the results of an ADO

[jazzz]

recordset and place it into the proper fields in a Report?

I have a report that needs to have three sections of items from three different record sources. I have the results from one query how do I add the results to fields in my report. I realize these fields will need to be unbound so I can fill them with the proper results based on my recordset.

Here is my code, which gives me the reults I require. What I need to do is now take that results place it in a specific section in the report.

Private Sub Report_Page()
'*Purpose: Build recordets to open our many sections to display the customers data.
On Error GoTo DefaultHandler

Dim rstCigar As ADODB.Recordset
Dim strSQLCigar As String
Dim lngCustomerID As Long
Dim strResultCigarItem As String

'capture the proper customer ID to use in our where clause.
lngCustomerID = txtCigarCustomerID

'*build our SQl query to retriev the results for the specific customer
strSQLCigar = "SELECT cigarInvNumID, CustomerID_FK, CustomerNumber, Cigar_InvoiceNumber, Cigar_OrderDate, Cigar_ItemPurchased, Cigar_ProductRetail " & _
"FROM OrderDetailsCigar " & _
"WHERE CustomerID_FK = " & lngCustomerID & ";"

'* open our recordset here
Set rstCigar = New ADODB.Recordset

rstCigar.ActiveConnection = CurrentProject.Connection
rstCigar.CursorType = adOpenKeyset
rstCigar.Source = strSQLCigar

rstCigar.Open

'*Retrieve the recordset results here
Do While Not rstCigar.EOF
strResultCigarItem = rstCigar.Fields("Cigar_ItemPurchased").Value
Debug.Print strResultCigarItem
rstCigar.MoveNext
Loop


SubExit:
'set our recordset to nothing cleaning up after ourselves.
Set rstCigar = Nothing

Exit Sub

DefaultHandler:
Select Case Err.Number
'* Handle errors here.


Case Else

MsgBox "Error:" & " " & Me.Name & ": " & Err.Number & vbCrLf _
& Err.Description

End Select
Resume SubExit

End Sub


Life's a journey enjoy the ride...

jazzz

 

[dhookom]

Is there a reason why you think you need to create an ADO recordset? Also, what is the reason for using the On Page event?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[jazzz]

Here is the reasoning, I have three tables, which contains the customer orders based on a club they belong to. The three tables hold individual product orders by club linked by a FK to CustomerID.

This report is being called from a form in which the customer name is selected from a combo box opening the report with the proper where clause. Now the code above is only ONE of the three recordsets that will be needed to populate the report.

The report will be broken down into three sections, Cigars, Tobacco and Pipes. All three are stored in a different Product tables for each customer if they belong to that club. Now when a customer is selected from our main form I need to check each club to see if they are a member of it and show their recent orders of that club.

Therefore if I build a query and join them on CustomerID all items are now on one line in the query and won't break down properly as required for the report, I can't group them by club.

If you have any suggestions on how else to fill the report as I described above please feel free to offer guidance to me. Like I said the above code will have two more record sets in it so I can check two more clubs then place the results into the proper sections in the report.

Thank you for your reply.

Life's a journey enjoy the ride...

jazzz

 

[dhookom]

I would seriously try to avoid three orders tables in favor of a single orders table but I assume you understand your requirements.

Do you understand how to use subreports? I didn't see anything in your requirements that suggests a subreport for each order table wouldn't work.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[jazzz]

Thank you Duane I will look into subreports I never used them.

Life's a journey enjoy the ride...

jazzz

 

[jazzz]

The subreport worked just fine thank you for the guidance.

Life's a journey enjoy the ride...

jazzz