DoCmd.OutputTo Locks the Source Table and Causes the Next Reports to Not Generate Successfully (1 Viewer)

abdullahkhan

Member
Local time
Today, 14:33
Joined
Apr 14, 2020
Messages
274
Hello,

I have a report based on a dynamic crosstab query and a button to save the report in PDF format using DoCmd.OutputTo acOutputReport, "rptResult", acFormatPDF, sFileName. If I display or print the report, everything works fine. However, if I create a PDF using aforesaid code, it also works fine for the first time but, the Record Source table remains locked even after the report is closed. And when try to generate new report, it gives me error something similar to database cannot lock the the table TEMP_AssignSequence because it is being used by someone. Error is received in the following code (RED color line) which is used to reset seed of a table which is basically a temporary table used to create dynamic crosstab query (method suggested in THIS POST by @Pat Hartman).

I also want to add that this error is not received if the SavetoPDF button is on some other form and is clicked to save already opened (because if report is not already opened, code on load event don't trigger which update several columns name) report. Error is only received if button is present on the report and is clicked to save the repor as pdf. To generate the result of new class, you must have run Compact & Repair or Close the DB and reopen it.

Rich (BB code):
Function ResetSeed(strTable As String) As String
    'Purpose:   Reset the Seed of the AutoNumber, using ADOX.
    Dim strAutoNum As String     'Name of the autonumber column.
    Dim lngSeed As Long          'Current value of the Seed.
    Dim lngNext As Long          'Next unused value.
    Dim strSQL As String
    Dim strResult As String
    
    lngSeed = GetSeedADOX(strTable,strAutoNum)
    If strAutoNum = vbNullString Then
        strResult =  "AutoNumber not found."
    Else
        lngNext = Nz(DMax(strAutoNum,strTable),0) + 1
        If lngSeed = lngNext Then
            strResult = strAutoNum &  " already correctly set to " & lngSeed &  "."
        Else
'            Debug.Print "lngnext = " & lngNext, "lngSeed = "; lngSeed
            'strSQL = "ALTER TABLE [" & strTable & "] ALTER COLUMN [" & strAutoNum & "] COUNTER(" & lngNext & ", 1);"
            strSQL =  "ALTER TABLE [" & strTable &  "] ALTER COLUMN " & strAutoNum &  " COUNTER(" & lngNext &  ", 1);"
'            Debug.Print strSQL
           CurrentProject.Connection.Execute strSQL
            strResult = strAutoNum &  " reset from " & lngSeed &  " to " & lngNext
        End If
    End If
    ResetSeed = strResult
End Function

Best Regards
Abdullah
 

Ranman256

Well-known member
Local time
Today, 05:33
Joined
Apr 9, 2015
Messages
3,805
you are already in Access, there is no need to run CurrentProject.Connection.Execute.
there is no need to run any code except the docmd.output .
if docmd.openreport "rptResult" works, why run all this code?

the crosstab query should already be there too and should work as docmd.openquery "qxCrosstab"

you can also 'write' the crosstab results query to a table, THEN docmd.output .
 

abdullahkhan

Member
Local time
Today, 14:33
Joined
Apr 14, 2020
Messages
274
if docmd.openreport "rptResult" works, why run all this code?
I myself am not good at programming and started using Microsoft Access few month ago. I wanted to generate whole class result with subject names as column header but report generated from a cross tab query have good number of columns and if number of subject for a given class are changed, report don't work so I searched internet dynamic cross tab query and many solutions were there but all of them very complicated for me. I posted question on AWF too and at then end of my post, in similar posts, I found one thread for dynamic cross tab query and followed solution provided and I also posted the link that post above.
It uses a temp table to hold the column headings that the crosstab will produce and assign them to a sequence number. Look the Report from variable crosstab to see it in action. For purposes of the example, the process is broken into two steps.

Now coming to why do much code. What I understood from the code is it work in two steps. It uses a temp table to hold the column headings that the crosstab will produce and assign them to a sequence number. Once the temp table is populated, it cannot be used for next report. To make it ready for the next report, we have to delete all the records, reset the seed do that it start autonumber from 1 and then populated again for next report.
 

Users who are viewing this thread

Top Bottom