Access Report Error

[GhostWolf]

I have an Access 2003 report that displays perfectly when opened from Access. However, when the report is activated from a VB6 app, the last detail line on the report is missing!

The report is grouped on User, and has a detail line for each type of transaction that user processed with the number of transactions. Each group ends with the total number of transactions the user processed, and the report ends with the number of transactions all users processed.

When the report is opened by VB, what I see in the last group is:
[USERID]
[TranType_1] 82
[TranType_2] 43
[User total] 125
[Report Total] 999

What I should see there, (and actually do see if the report is opened in Access), is a line for [TranType_3] with 1 transaction, and the User and Report totals include this transaction, (making them 126 and 1000 respectively).

I originally ran into this problem when we noticed that the VB program's DataEnvironment/Report was dropping the last line from the report. Silly me thought I'd solve the problem by replacing the VB report with an Access report!

Any ideas on why this might be happening?

 

[lameid]

Are you sure the code to open the report is equivalent in both environments?

Other than that it sounds like a VB6 so you have better luck a VB forum unless you get lucky and someone that does both reads this.

To that end you may do well to post the VB6 code that displays the report.

 

[GhostWolf]

No code in Access, just double-click the report to show it. (The report's built over a work table.)

The VB code uses an Access object:

Global objAxs as Object
---
Set objAxs=GetObject([Access mdb name])
---
Public Sub ShowReport()
...
With objAxs.Application
    DoCmd.OpenReport "rptStats", acViewPreview,,,acDialog
    DoCmd.Maximize
End With
...
End Sub

 

[lameid]

A coworker and I played around with it and found this better. Your report should show all data although the maximize is useless unless you open the report as a normal window. Also With doesn't do anything without the leading dot however docmd does not seem to need it if Access is properly referenced.

Option Explicit
Global objAxs As Object 'Access.Application

Public Sub ShowReport()
Set objAxs = GetObject([ACCESS MDB NAME])

    objAxs.Visible = True
    
    DoCmd.OpenReport "empaddr", acViewPreview ', , , acDialog
    DoCmd.Maximize

End Sub

 

[GhostWolf]

Your report should show all data...

I was afraid of that.

I tried your .Visible and .OpenReport changes, and they work quite nicely.

Still have the same problem with the last record not showing on the report, though, so I reckon it's about time to give the VB gurus a crack at it.

 

[lameid]

The only other thought my coworker had was that maybe you are doing something like not using the same database file in a test environment.

That being said, you are automating Access so it should display the same regardless of whether you open in Access or via VB.

Perhaps there is something else going on that controls whether or not the record is displayed... Events on the report or criteria that changes based on other objects being opened or filled out?

Just thought I'd reach for an Access problem but I don't think it is there.

 

[GhostWolf]

I appreciate the reach, and I've got to admit being lost on this one too.

The only copy of the database resides on my PC, (development and test), no conditions, events or VBA on the report - the only "special" functions on the report are the two total accumulators, (group and report).

For what it's worth: I'm using VB6 sp6, Access 2003 sp2, MDAC 2.8 sp1, on XP sp2.

 

[lameid]

Reaching deeper... do you get the same results in Access AFTER you call the report from VB6? I am just thinking VB6 program may be changing data too.

Also the code I provided makes the Access App visible... What if you close the report and reopen it in the Access instance VB6 opened? Do you get the same or different results?

 

[GhostWolf]

Immediately after closing the report VB opened, I manually opened the Access report - and it showed me all the records.

Your method of making Access visible works better than what I'd coded. Without setting the .Visible property, the report wouldn't display unless I specified the WindowMode property.

 

[lameid]

Sorry being late getting back... I'm on vacation and didn't check the mail tek-tips goes to.

I must say that is exceptionally weird. I've known different versions of Jet to give different results in some situations (somewhere in Jet 3.0 to 3.5 with patches), but I can't figure out a way that Access could possibly be forced into using a differnt version let alone it being loaded on the computer.

Good luck with this and if you ever find an answer pleas post back, this has my curiosity raised.