Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 11-10-2019, 08:27 AM   #1
jin4
Newly Registered User
 
Join Date: Nov 2019
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
jin4 is on a distinguished road
Unable to edit tables after running a macro/VBA code

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.

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 by jin4; 11-10-2019 at 03:26 PM.
jin4 is offline   Reply With Quote
Old 11-10-2019, 08:40 AM   #2
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 5,729
Thanks: 57
Thanked 1,260 Times in 1,241 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Unable to edit tables after running a macro/VBA code

Hi. Is the report open when you run this code?
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 11-10-2019, 09:11 AM   #3
Gasman
Enthusiastic Amateur
 
Gasman's Avatar
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,332
Thanks: 432
Thanked 790 Times in 766 Posts
Gasman is a jewel in the rough Gasman is a jewel in the rough Gasman is a jewel in the rough
Re: Unable to edit tables after running a macro/VBA code

Aren't you running this code from the report?

You are using Me.Name ?

__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is online now   Reply With Quote
Old 11-10-2019, 04:21 PM   #4
jin4
Newly Registered User
 
Join Date: Nov 2019
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
jin4 is on a distinguished road
Re: Unable to edit tables after running a macro/VBA code

Quote:
Originally Posted by theDBguy View Post
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 by jin4; 11-10-2019 at 04:38 PM.
jin4 is offline   Reply With Quote
Old 11-10-2019, 09:06 PM   #5
Micron
AWF VIP
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 1,050
Thanks: 10
Thanked 208 Times in 197 Posts
Micron has a spectacular aura about Micron has a spectacular aura about
Re: Unable to edit tables after running a macro/VBA code

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.
__________________
Sometimes I just roll my eyes out loud...
Windows 10; Office 365 (Access 2016)
Micron is offline   Reply With Quote
Old 11-10-2019, 09:23 PM   #6
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 5,729
Thanks: 57
Thanked 1,260 Times in 1,241 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Unable to edit tables after running a macro/VBA code

Quote:
Originally Posted by jin4 View Post
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.

__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Running VBA code through Macro navi95 Modules & VBA 3 12-06-2016 02:54 PM
[SOLVED] error 2950 running code from a macro hubcap750 Modules & VBA 15 06-27-2013 01:01 PM
Error Message when running 'On Load' VBA code / Macro ersocia Modules & VBA 6 08-30-2012 05:44 AM
Manually Edit Excel Cells while running macro triplell89 Excel 1 05-23-2009 12:51 PM
Reading, not Running, a Macro from VBA code The_Doc_Man Macros 2 07-16-2001 06:56 AM




All times are GMT -8. The time now is 08:34 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World