Unable to edit tables after running a macro/VBA code (1 Viewer)

jin4

New member
Local time
Yesterday, 19:09
Joined
Nov 10, 2019
Messages
3
Hi all

I copied the code below to export a query-based report as PDF. The code itself works well. The report was successfully automatically converted to a PDF as intended. However, after I ran the VBA code, I encountered an error message "Either an object bound to the table is open or another user has the table open. Do you want to open the table as read-only?" and I can't design view the source table without reopening the database. No other objects than the report were open or in use.

I first thought there was something wrong with the code. I created a built-in macro (ExportWithFormatting) to get the same function and it worked, but the same problem is still happening: Design-viewing the source table is still not allowed after I run the macro.

I tried to how to solve this problem by myself for about half a day, but I still can't find it. :banghead:

What is the cause? How can I fix the problem? Any solution would be welcome... Thank you.

Code:
Function FileExist(FileFullPath As String) As Boolean
  Dim value As Boolean
  value = False
  If Dir(FileFullPath) <> "" Then
    value = True
  End If
  FileExist = value
End Function

Private Sub cmdExportAsPDF_Click() '_Click()
 
  Dim fileName As String, fldrPath As String, filePath As String
  Dim answer As Integer
 
  fileName = QuotationNumber 'filename for PDF file
  fldrPath = "C:\Users\" & Environ$("username") & "\Desktop\mak" 'folder path where pdf file will be saved *
 
  filePath = fldrPath & "\" & fileName & ".pdf"
 
  'check if file already exists
  If FileExist(filePath) Then
    answer = MsgBox(prompt:="PDF file already exists: " & vbNewLine & filePath & vbNewLine & vbNewLine & _
      "Would you like to replace existing file?", buttons:=vbYesNo, Title:="Existing PDF File")
    If answer = vbNo Then Exit Sub
  End If
 
  On Error GoTo invalidFolderPath
  DoCmd.OutputTo objecttype:=acOutputReport, objectName:=Me.Name, outputformat:=acFormatPDF, outputFile:=filePath
 
  MsgBox prompt:="PDF File exported to: " & vbNewLine & filePath, buttons:=vbInformation, Title:="Report Exported as PDF"
  Exit Sub
 
invalidFolderPath:
  MsgBox prompt:="Error: Invalid folder path. Please update code.", buttons:=vbCritical

End Sub
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 19:09
Joined
Oct 29, 2018
Messages
21,358
Hi. Is the report open when you run this code?
 

Gasman

Enthusiastic Amateur
Local time
Today, 02:09
Joined
Sep 21, 2011
Messages
14,048
Aren't you running this code from the report?

You are using Me.Name ? :confused:
 

jin4

New member
Local time
Yesterday, 19:09
Joined
Nov 10, 2019
Messages
3
Hi. Is the report open when you run this code?

First of all I'm sorry I'm a helpless noob...

Yes. I ran the macro with the report open. I wonder if there is anything wrong with having the report open while I run the VBA code/macro. Given that both you and Gasman are asking if the report was open, it seems that the problem happened because I ran the code without closing the report.

I expected it would work like in Excel (Macros on spreadsheets work even if the sheet is open, as you know.) and I still don't know what running the macro with the report open has to do with designviewing the source table.

Do I have to assign the macro to commands somewhere else outside the report?
 
Last edited:

Micron

AWF VIP
Local time
Yesterday, 22:09
Joined
Oct 20, 2018
Messages
3,476
You missed something. Gasman asked if you're running the code from the report, not if you had it open. I think he and I suspect you have this button on the report, which I would not do. I have no idea if Access creates a pdf of a report based on opening it in design view or not. I have done this from a form and can say you don't see Access opening the report, but that doesn't mean that it's not happening. If it is, then you're probably causing a conflict between those 2 views. Controls like buttons or combos don't belong on a report IMHO.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 19:09
Joined
Oct 29, 2018
Messages
21,358
First of all I'm sorry I'm a helpless noob...

Yes. I ran the macro with the report open. I wonder if there is anything wrong with having the report open while I run the VBA code/macro. Given that both you and Gasman are asking if the report was open, it seems that the problem happened because I ran the code without closing the report.

I expected it would work like in Excel (Macros on spreadsheets work even if the sheet is open, as you know.) and I still don't know what running the macro with the report open has to do with designviewing the source table.

Do I have to assign the macro to commands somewhere else outside the report?
Hi. I asked if the report was open because you said you can't go to the table's design view after running the code. Sure you can run any code while the report is open. However, you can't go to design view if the table is being used by the report at the same time. So, you don't have to close the database, just close the report before you go to the design view of the table.
 

Users who are viewing this thread

Top Bottom