Blank Reports

[fourofclubs]

G'day,
I've got a rather odd problem involving printing reports. I'm adding a record to a table and updating it, then trying to print a report based on the new record. Should be simple. However, I seem to be getting a few blank reports mingled in with the proper reports. I've checked out the table, and the record it uses is in there, so I don't think it's a problem with the table update. Funny thing is, if I print the same report twice, just repeating the same line of code, it'll sometimes print a blank report followed by a data filled report. The only thing I can think of is that the table update takes a while, and that the report is printed before the table is finished updating.
Any thoughts on this would be greatly appreciated.

Thanks,
Brad.

 

[SQLBI]

Brad,

Is the blank page the 2nd page to print?

If so, your report does not fit to a single page and the blank page is the overflow.

Just a thought.

Leigh Moore
LJM Analysis Ltd

http://www.ljm-analysis.co.uk

 

[fourofclubs]

Hi, thanks for replying,
No, it's not an overflow blank page, the report prints with all the labels and everything, but there isn't any data. This, I would think, would mean that the recordset the report is based on is empty, but I'm sure it's not. This is basically what I'm doing:

1. Add a record to the table and update it.
2. Print the report based on the new record. - This prints an empty report.
3. Print the same report again, using the exact same line of code. - This prints a report with data.

I just don't understand why this is happening.

Thanks,
Brad.

 

[SQLBI]

It sounds like your report is executing before the new record is saved. You need to make sure that you unlock the new record before your report prints or it's looking for data that technically isn't there.

Leigh Moore
LJM Analysis Ltd

http://www.ljm-analysis.co.uk

 

[fourofclubs]

That's exactly what it sounds like. Great! But shouldn't the update method unlock the record? I'm not quite certain how to make sure it's unlocked.

Thanks,
Brad.

 

[willir]

Brad

When are you running the query for your report? If you are doing all this in the same module or within the form, is it possible your code does not refresh the recordset after writing to the database. (For example, you may have an update button that writes the record and prints the report all at once.)

If so, refresh the recordset or select the record again before printing.

If you add the record then go to another form for the report, then I am stumped.

Richard

 

[fourofclubs]

All the code I'm using to update the table and print the report is in the same module. I add a record to the table, update it, and then print the report using:

DoCmd.OpenReport "rptLIRTruSpec", acViewNormal, , _
     "Nonconformancetbl![TruSpecID] = " & rstArchive![ID]

The rstArchive![ID] is correct (as far as I can tell) when the report is printed.

The report is based on a saved query which is in turn based on a join of two tables (one of which being the table just updated), so I assume it runs just before printing the report. Sometimes the report has data, sometimes not.

Thanks,
Brad.

 

[willir]

fourofclubs

Without seeing more code, I would have to speculate.

When I run into a problem like this, I take walk through the code.

Put a "stop" in your code, and then step through your code. Look for when you write the record to the table, and when you run the report. The cool thing about debug mode is that you can see what values your variables are using. For example, does rstArchive.ID have a valid number?

Richard

 

[fourofclubs]

Well I have stepped through the code, and when I do that, I can't see any problem at all. The record is properly added to the table, rstArchive.ID contains the correct value, and the reports all print out fine. It's when I let it run that I see blank reports.
If you want the whole code, here it is:

Dim rstArchive As Recordset, rstLIR As Recordset
Dim strCriteria As String, rptLIR As String
Dim strBarCodeReport As String, strCriteriaBarCodeReport As String
    
Set rstLIR = dbsTruSpec.OpenRecordset("Nonconformancetbl", dbOpenDynaset)
strCriteria = "SELECT * FROM tblArchive " _
            & "WHERE LIRreported = No And F > 0"

Set rstArchive = dbsTruSpec.OpenRecordset(strCriteria, dbOpenDynaset)
rptLIR = "rptLIRTruSpec"
Do Until rstArchive.EOF
    With rstLIR
        .FindFirst "[TruSpecID] = " & rstArchive![ID]
        If .NoMatch Then
            .AddNew
            'assign values to about 20 fields
            ![TruSpecID] = rstArchive![ID]
            .Update
        End If
    End With
        
    With rstArchive
        .Edit
        ![LIRreported] = True
        .Update
    End With
               
    strBarCodeReport = "rptMichigan"
    strCriteriaBarCodeReport = "SELECT * FROM tblboss " _
                   & "WHERE ID = " & rstArchive![rollid]
    
    DoCmd.Echo False, "Printing LIR."

    'this report prints out fine
    DoCmd.OpenReport strBarCodeReport, acViewNormal _
        , strCriteriaBarCodeReport, _
        "tblboss.ID=" & rstArchive![rollid]

    DoCmd.Close acReport, strBarCodeReport
        
    'this is the one that gives problems
    DoCmd.OpenReport rptLIR, acViewNormal, , _
        "Nonconformancetbl.TruSpecID=" & rstArchive![ID]
        
    DoCmd.Close acReport, rptLIR
    DoCmd.Echo True
        
    rstArchive.MoveNext
Loop
    
rstArchive.Close
rstLIR.Close
[code]

This should (and for the most part does) find all records in my Archive table that have not yet been reported on, add a corresponding record to the Nonconformance table, and print a report based on a join of the two (on tblArchive.ID = Nonconformancetbl.TruSpecID). Maybe it's this query that doesn't update in time? Any thoughts?

Thanks,
Brad.

 

[fourofclubs]

Alright, I've got a 'solution'. It's really quite terrible, and makes me cringe just to look at it, but it seems to be working. I just stuck a boolean in the report that's true if the report has no data. Then in the module, I just loop through opening and closing the report until it's got some data. Ugly, ugly solution, but when you can only come up with one solution, you don't really have options. If anyone else thinks of something better, I'd love to hear it.

Thanks,
Brad.

 

[markgrizzle]

Brad:
I think it is a timing issue. In my case, I needed to write a new record to a file import log table. I then immediately opened a recordset on that table to get the new PK value, so I could include it while loading sales records from the file.

The code kept failing to find the new record. I eventually wrote a 5 second loop to idle the code.

bytTimeLoop = Format(Now, "s&quot

Do
Loop Until Abs((Format(Now, "s&quot - bytTimeLoop)) > 5

The closest I came to figuring out the problem was either a system write cache, or ado was too slow.

Anyway, I think this may be a better solution than cycling through the report, since any number of other reasons might cause it to report no data.

You may need to change the wait time for your system, I just kept increasing it on mine till it worked, and 5 was the magic number for me.

 

[willir]

Weird !!!

And I would lean to an issue with cach on a write.

If this is on a server, and or using a client / server, disable caching on the write. I have seen this type of thing with ccMail, and other database applications where either the server or the client cached the writes.

Richard

 

[faeryfyrre]

Simple.

Right before you open the report include this line.

'ensure that the current record has been saved
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

This will force the forms new record to save and the record will therefore be available to you when you print your report.

You will sometimes come across a similar problem where you make changes to an existing record then try to print/preview a report based on the new info but the old info is displayed on report. The above code will alleviate this problem.

 

[fourofclubs]

Well, thanks for all the tips.
I found a pretty good solution in a roundabout sort of way. It took some work, and was a bit of a hassle, but I realized I could move the table update to a different module (one that runs a lot sooner), and that eliminated this weird timing thing. Seems to be working fine now.

Thanks for your help,
Brad.