vb6 - RunTime Error 2486 - You can't carry out this action at the present time

[tgifgemini]

I am trying to display/open MS Access report and I get: RunTime error 2486 - You can't not carry out this action at the present time on this line of code:

DoCmd.OpenReport stDocName, acViewNormal

Below is my module:

Set rsUnclaimedRptqry = dbUnclaimed.OpenRecordset("SELECT DISTINCT tblUnclaimed.L1L2, tblUnclaimed.PassNumber, tblUnclaimed.EmployeeName, tblUnclaimed.Status " _
& "FROM tblUnclaimed LEFT JOIN EmployeeInfo ON (tblUnclaimed.PassNumber = EmployeeInfo.Pass_Number) AND (tblUnclaimed.L1L2 = EmployeeInfo.L1L2) " _
& "WHERE (((tblUnclaimed.Status) In ('A','E','U')) AND ((Len([EmployeeInfo].[Status]))=1)) " _
& "ORDER BY tblUnclaimed.L1L2, tblUnclaimed.PassNumber;")

'Populate Report Table:
'----------------------
Set rsUnclaimedtbl = dbUnclaimed.OpenRecordset("tblUnclaimedChk_Msg", dbOpenDynaset)


rsUnclaimedRptqry.MoveFirst

Do Until rsUnclaimedRptqry.EOF = True
    rsUnclaimedtbl.AddNew
    rsUnclaimedtbl![L1L2] = rsUnclaimedRptqry![L1L2]
    rsUnclaimedtbl![PassNumber] = rsUnclaimedRptqry![PassNumber]
    rsUnclaimedtbl![EmployeeName] = rsUnclaimedRptqry![EmployeeName]
    rsUnclaimedtbl![Status] = rsUnclaimedRptqry![Status]
    rsUnclaimedtbl.Update
    rsUnclaimedRptqry.MoveNext
Loop
    

Ans = MsgBox("Would you like to View this report?", vbYesNo, "FTP Transmission")

If Ans = vbYes Then GoTo ViewReport

ViewReport:
'------------
Dim stDocName As String

stDocName = "RptUnclaimedChk_Msgs"

'Note: "RptUnclaimedChk_Msgs" is the name of the report in my database 

DoCmd.OpenReport stDocName, acViewNormal

Any insight will be appreciated.
tgifgemini

[QVeen72]

Hi,

I guess, it is a memory Issue, and why you open such a complicated sql query, and again add all the records, to the same table....?
Your table must have become huge... Every time this report is viewed, Total records in that table(tblUnclaimed ) Doubles..
Is this what you want...?

Regards
Veena

[tgifgemini]

Hi QVeena.
Is it a really a memory issue? No, I don't want the recordds to double each time I run the sql and populate the table.

Are you suggesting I delete the records in the table before adding new data to the table?

Any help will be appreciated.
Thanks.
tgifgemini

[tgifgemini]

Problem resolution:
Even though I have MS Access 9.0 Object library in my References, I have to physically create and instatiate a new Access object inside my vb code.

Dim MsAxs As Access.Application
Set MsAxs =  New Access.Application
MsAxs.OpenCurrentDatabase("Mydb")

stDocName = "MyReportName"

MsAxs.DoCmd.OpenReport stDocName,  acViewNormal

And that solved the problem.

I was under the impression that having the Access reference checked with the rest of my references will do the trick but I guess not.

Thanks for you help.
tgifgemini

[QVeen72]

Hi,

I Got confused with the code there...
One more suggestion, Instead of
Opening >> Loop >> Add recordsets, Just run one SQL Statement:

Dim sSQL As String
ssql="Insert Into tblUnclaimedChk_Msg " _
& " (L1L2,PassNumber,EmployeeName,Status) " _
& " SELECT DISTINCT tblUnclaimed.L1L2, tblUnclaimed.PassNumber, " _
& " tblUnclaimed.EmployeeName, tblUnclaimed.Status " _
& "FROM tblUnclaimed LEFT JOIN EmployeeInfo ON " _
& " (tblUnclaimed.PassNumber = EmployeeInfo.Pass_Number)" _
& " AND (tblUnclaimed.L1L2 = EmployeeInfo.L1L2) " _
& "WHERE (((tblUnclaimed.Status) In ('A','E','U')) AND "  _
& " ((Len([EmployeeInfo].[Status]))=1)) " 
dbUnclaimed.Execute sSQL

Regards
Veena

[tgifgemini]

Thanks Veena. That worked too.
Have a wonderful weekend.
tgifgemini