Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2002
    Location
    Rochester, NY
    Posts
    84

    Question Unanswered: Only last record appears on report

    I have a report that contains unbound controls in the detail section of the report. When the report opens, an ADO recordset is opened that contains data returned by a stored Oracle procedure. The ADO recordset is used to populate the unbound controls in the detail section. My problem is that only the last row from the recordset is appearing on the report. The detail section and controls in it all have the Can Grow/Can Shrink property set to yes. Any ideas on why only one row is printing?

  2. #2
    Join Date
    Oct 2001
    Location
    Chicago
    Posts
    440
    Can Grow/Shrink applies to the vertical size of the control not the number of records.

    >The ADO recordset is used to populate the unbound controls in the detail section.

    How are you trying to accomplish this?

    Are you setting a recordsource for the report in the open event?


    More info from you along with your code...

    Matt

  3. #3
    Join Date
    Oct 2002
    Location
    Rochester, NY
    Posts
    84

    Post

    Thanks for your response!
    Here is the code in my report_open event:

    Private Sub Report_Open(Cancel As Integer)
    If ReportFromLogged Then
    GenLoggedEngRpt (Cancel)

    Else
    'Code for specific, past reports goes here
    MsgBox "Reporting on previously logged inspection data
    functionality has not been implemented at this time."
    Exit Sub

    End If

    SetERLRPCaption

    End Sub

    The GenLoggedEngReport subroutine executes a stored Oracle procedure and returns a recordset. This recordset is used as the detail section's data.

    I'm using an example I found in a book where they created a crosstab report. Here is the code I'm using to put the data from the recordset into the unbound controls - it's in the detail_format event code

    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    Dim i As Integer
    Dim intDiscCnt As Integer
    Dim intRptRows As Integer

    If Not mrstDetailResult.BOF And Not mrstDetailResult.EOF Then
    intDiscCnt = 0

    If Me.FormatCount = 1 Then

    For i = 0 To mrstDetailResult.RecordCount - 1
    If Not IsNull(mrstDetailResult(0)) Then
    Me.txtCondDesc = XNulls(mrstDetailResult(4))
    Me.txtDegDfct = XNulls(mrstDetailResult(5))
    Me.txtLgthBeg = XNulls(mrstDetailResult(6))
    Me.txtClkBeg = XNulls(mrstDetailResult(7))
    Me.txtLgthEnd = XNulls(mrstDetailResult(8))
    Me.txtClkEnd = XNulls(mrstDetailResult(9))
    Me.txtObsComment = XNulls(mrstDetailResult(10))

    Else
    intDiscCnt = intDiscCnt + 1

    End If

    mrstDetailResult.MoveNext

    Next i
    End If
    End If

    End Sub

    In the book called "Mastering Microsoft Access 2000 Development", Allison Balter shows how to make a crosstab report using a recordset and unbound controls. She used DAO, but I can't because I have to use ADO to execute the Oracle procedure. Any ides?
    Regards,
    Terry

  4. #4
    Join Date
    Oct 2001
    Location
    Chicago
    Posts
    440
    What is the recordsource of the report? Do you have only one row appearing on the report or are the last records from the recordset appearing repeatedly on every row of the report?

    It appears that in either case, when the detail format event fires off, the coding will loop through the recordset - changing the value of the current record's text box repeatedly - always ending up with the last record before moving on.

    Matt

  5. #5
    Join Date
    Oct 2002
    Location
    Rochester, NY
    Posts
    84
    Originally posted by Rockey
    What is the recordsource of the report? Do you have only one row appearing on the report or are the last records from the recordset appearing repeatedly on every row of the report?

    It appears that in either case, when the detail format event fires off, the coding will loop through the recordset - changing the value of the current record's text box repeatedly - always ending up with the last record before moving on.

    Matt
    There is no recordsource assigned. There is none in the example I was "borrowing" from the book I mentioned. I only have one row of controls in the detail section of the form and they are all unbound. This is also true with the example I was using. The main difference is that in the book, they used a DAO recordset to populate the unbound controls with data (same or similar code as what I gave you). Unfortunately, I have to use an ADO recordset to execute the Oracle procedure. Should I try to create a DAO recordset from an ADO recordset?
    Regards,
    Terry

  6. #6
    Join Date
    Oct 2001
    Location
    Chicago
    Posts
    440
    No the problem is that the form does not have a recordsource.

    How you create your recordset for the unbound controls does not matter.

    You have two problems with your situation.

    1. no recordsource means that the detail format event will only fire off once - so report shows only one record. Set the recordsource of your report to any table (create one with a few records) and you will see the difference. 12 records in the table = 12 rows of data in the report (assuming you are not grouping).

    2. There needs to be some other logic in the detail format section to match the data to what would be the current record (see problem #1).


    Please post the original Detail_Format code from the book and we should be able to find the discrepancy.

    Matt

  7. #7
    Join Date
    Oct 2002
    Location
    Rochester, NY
    Posts
    84
    Originally posted by Rockey
    No the problem is that the form does not have a recordsource.

    How you create your recordset for the unbound controls does not matter.

    You have two problems with your situation.

    1. no recordsource means that the detail format event will only fire off once - so report shows only one record. Set the recordsource of your report to any table (create one with a few records) and you will see the difference. 12 records in the table = 12 rows of data in the report (assuming you are not grouping).

    2. There needs to be some other logic in the detail format section to match the data to what would be the current record (see problem #1).


    Please post the original Detail_Format code from the book and we should be able to find the discrepancy.

    Matt
    OK, here's what I'm going to try:
    I have no tables in the database. It is just an application/user interface to data stored in an Oracle database.
    1) Create a table with a single field of integers 1 - 1000
    2) assign the table as the recorsource to the report (temporarily). This will allow me to open the report.
    3) During the report_open event, define a query (querydef) that selects the integers that are less than or equal to the number of records to appear from the recordset.
    4) Optional - only if selected records from the table don't limit the count of detail records - Change the recordsource for the report to the query.
    5) Execute the code I sent to you earlier to populate the detail columns.

    I can't find a method or property anywhere that controls or causes a new row of columns to appear - must be that this is an "automatic" thing handled between the report object and the underlying recordsource. If so, it seems a little inflexible to me. You can't set the recordsource to a lot of the things that would be much more useful (such as an ADO recordset).
    Regards,
    Terry

  8. #8
    Join Date
    Oct 2001
    Location
    Chicago
    Posts
    440
    What is your XNulls fuction?

    What is the value of intRptRows?

    After looking at that code again - I realize that even if you have the same number of records in the reports recordsource - the logic will only occur once since on the first pass of the event format the recordset moves to EOF. Thus, the value of the text boxes will be set to the last record where mrstDetailResult(0) is not null, and the value will stay that way during all subsequent passes unless the recordset returns to the beginning.

    Another confusing thing is that the Detail format even will actually fire record number x 2. So if you had a modular level counter (x) and had this code in the Detail Format Event:
    x = x + 1
    Me.txtBox = x

    You would see that Me.txtBox would be 4,5,6 if the underlying recordsource had three records.


    How much of the original code are you using.

  9. #9
    Join Date
    Oct 2002
    Location
    Rochester, NY
    Posts
    84
    Originally posted by Rockey
    What is your XNulls fuction?

    What is the value of intRptRows?

    After looking at that code again - I realize that even if you have the same number of records in the reports recordsource - the logic will only occur once since on the first pass of the event format the recordset moves to EOF. Thus, the value of the text boxes will be set to the last record where mrstDetailResult(0) is not null, and the value will stay that way during all subsequent passes unless the recordset returns to the beginning.

    Another confusing thing is that the Detail format even will actually fire record number x 2. So if you had a modular level counter (x) and had this code in the Detail Format Event:
    x = x + 1
    Me.txtBox = x

    You would see that Me.txtBox would be 4,5,6 if the underlying recordsource had three records.


    How much of the original code are you using.
    Whew! Lots of changes. I got around the problem by creating a separate Access database that will only contain one table. This table will be temporary in nature, so the database is killed and recreated each time a report is run.

    I created a link to the temporary table in the application database and leave it alone until the report is run.

    At the time the user requests a report, a new mdb file is created along with a new table to be used as the basis for the report. This mdbfile is created in each user's personal file space so multiple users won't be over-writing each others report data. The link is used as the report's recordsource, and each of the controls is now bound to a field in the temporary table.

    To populate the table with records, I simply go through each of the records in the ADO recordset and put them in the temporary table.

    This seems like a lot of work just to get the contents of a recordset into a report. Does it seem to anyone else that ADO is still only half baked? I thought this was the way Microsoft was pushing Data Access. It seems as though recordsets oughtto be able to be used as the basis of a report. It's not terribly useful to create a recordset and then create a whole lot of temporary stuff just to get the data in a report.
    Regards,
    Terry

Posting Permissions

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