Hello,
I have a report based on a dynamic crosstab query and a button to save the report in PDF format using
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.
Best Regards
Abdullah
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