By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,661 Members | 1,311 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,661 IT Pros & Developers. It's quick & easy.

ACCDE Error 29045 when deleting or saving report

gnawoncents
100+
P: 212
Greetings,

I have a report (Report1) that I create and save using VBA coding when the user presses a button. In order to preserve the same report name without having to prompt the user, I first delete the report. Once I had the delete, create and save steps all functioning fine, I converted to an ACCDE file and get the following error when I run the code:

"Error 29045: You can't import, export, create, modify, or rename any forms, reports, pages or modules in an ACCDE, MDE or ADE database."

Here's the weird bit -- even though I get the error, it still lets me delete, create and save Report1. In fact, the entire code SEEMS to run just fine. I know I can simply trap the error in VBA and ignore it with a resume next so the user never sees it, but here are my concerns:

1) How do I figure out what portion of my code is causing the error? (Is there any way to debug an ACCDE?)

2) If I hide/ignore the error what unanticipated negative consequences am I likely to experience?

Any thoughts? Thanks!
Oct 5 '10 #1

✓ answered by MMcCarthy

OK I've been doing some research on this. It seems you can't create dynamic reports in design view in an executable file like mde or accde. The only solution is to create a template report with unassigned controls and then assign a record source to the report at runtime. You can then assign controls to the various fields.

Share this Question
Share on Google+
8 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
Can you post the relevant code. It would help to figure this out.
Oct 6 '10 #2

gnawoncents
100+
P: 212
Since I can't debug an ACCDE (or at least don't know how), below is the code that runs when I press the button in question. Likely, the error occurs on line 126 (delete report), 295 (open/create report), and/or 298 (save report). Sorry for the length, and thanks for the help.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command36_Click()
  2. On Error GoTo Err_Command36_Click
  3.  
  4. 'Setup recordset
  5. Call Create_Report_Recordset
  6.  
  7. 'Test for no records
  8. If strSQLreport = "No records" Then Exit Sub
  9.  
  10. 'Delete all records currently in the TBLReportCount table
  11. DoCmd.SetWarnings False
  12. DoCmd.OpenQuery "QRYdeleteReportCountALL"
  13. DoCmd.SetWarnings True
  14.  
  15. 'Update TBLReportCount with number of each type of answer for each question
  16. Dim dbQuestions As Database
  17. Dim rstQuestions As Recordset
  18. Dim fldQuestions As Field
  19. Dim sql3 As String
  20.  
  21. Set dbQuestions = CurrentDb()
  22. Set rstQuestions = dbQuestions.OpenRecordset(strSQLreport)
  23.  
  24. 'Add the required fields to the TBLReportCount Table
  25. Dim dbs2 As Database
  26. Set dbs2 = CurrentDb()
  27. rstQuestions.MoveLast
  28. rstQuestions.MoveFirst
  29. Do While Not rstQuestions.EOF
  30. For Each fldQuestions In rstQuestions.Fields
  31.     If fldQuestions.Name Like "*Answer" Then
  32.        If Not IsNull(fldQuestions) Then
  33.         dbs2.Execute " INSERT INTO TBLReportCount " _
  34.         & "(AnswerNumber, AnswerName) VALUES " _
  35.         & "(" & Mid(Replace(fldQuestions.Name, "Answer", ""), 2) & " , '" & fldQuestions.Name & "');"
  36.        End If
  37.     End If
  38. Next fldQuestions
  39. rstQuestions.MoveNext
  40. Loop
  41.  
  42. Dim strComments As String
  43. Dim strLOOKUP As String
  44. strComments = ""
  45. Dim varNUM As Long
  46. Dim intCount As Integer
  47.  
  48.         'Count the answers
  49.         rstQuestions.MoveLast
  50.         rstQuestions.MoveFirst
  51.         Do While Not rstQuestions.EOF
  52.         For Each fldQuestions In rstQuestions.Fields
  53.                If Not IsNull(fldQuestions) Then
  54.                DoCmd.SetWarnings False
  55.                  If fldQuestions.Name Like "*Answer" Then
  56.                     strLOOKUP = Replace(fldQuestions.Name, "Answer", "Comments")
  57.                     If Not IsNull(rstQuestions.Fields(strLOOKUP)) Then
  58.                       strComments = Nz(DLookup("[Comments]", "TBLReportCount", "[AnswerName] = '" & fldQuestions.Name & "'"), "") & "Survey ID: " & rstQuestions!SurveyID & "  Survey Response: " & fldQuestions & "  Comment: " & rstQuestions.Fields(strLOOKUP) & vbCr & vbLf
  59.                       sql3 = "UPDATE TBLReportCount SET Comments = '" & strComments & "'" & _
  60.                       " WHERE AnswerName = '" & fldQuestions.Name & "';"
  61.                       DoCmd.RunSQL sql3
  62.                     End If
  63.                     varNUM = Nz(DLookup("[FieldValue]", "TBLSurveySelections", "[StoreValue] = '" & fldQuestions & "'"), 0)
  64.                     Select Case varNUM
  65.                      Case 1        'varNum = 1
  66.                         intCount = Nz(DLookup("[Count1]", "TBLReportCount", "[AnswerName] = '" & fldQuestions.Name & "'"), 0) + 1
  67.                         sql3 = "UPDATE TBLReportCount SET Count1 = " & intCount & _
  68.                         " WHERE AnswerName = '" & fldQuestions.Name & "';"
  69.                         DoCmd.RunSQL sql3
  70.                      Case 2        'varNum = 2
  71.                         intCount = Nz(DLookup("[Count2]", "TBLReportCount", "[AnswerName] = '" & fldQuestions.Name & "'"), 0) + 1
  72.                         sql3 = "UPDATE TBLReportCount SET Count2 = " & intCount & _
  73.                         " WHERE AnswerName = '" & fldQuestions.Name & "';"
  74.                         DoCmd.RunSQL sql3
  75.                      Case 3        'varNum = 3
  76.                         intCount = Nz(DLookup("[Count3]", "TBLReportCount", "[AnswerName] = '" & fldQuestions.Name & "'"), 0) + 1
  77.                         sql3 = "UPDATE TBLReportCount SET Count3 = " & intCount & _
  78.                         " WHERE AnswerName = '" & fldQuestions.Name & "';"
  79.                         DoCmd.RunSQL sql3
  80.                      Case 4        'varNum = 4
  81.                         intCount = Nz(DLookup("[Count4]", "TBLReportCount", "[AnswerName] = '" & fldQuestions.Name & "'"), 0) + 1
  82.                         sql3 = "UPDATE TBLReportCount SET Count4 = " & intCount & _
  83.                         " WHERE AnswerName = '" & fldQuestions.Name & "';"
  84.                         DoCmd.RunSQL sql3
  85.                      Case 5        'varNum = 5
  86.                         intCount = Nz(DLookup("[Count5]", "TBLReportCount", "[AnswerName] = '" & fldQuestions.Name & "'"), 0) + 1
  87.                         sql3 = "UPDATE TBLReportCount SET Count5 = " & intCount & _
  88.                         " WHERE AnswerName = '" & fldQuestions.Name & "';"
  89.                         DoCmd.RunSQL sql3
  90.                      Case 6        'varNum = 6
  91.                         intCount = Nz(DLookup("[Count6]", "TBLReportCount", "[AnswerName] = '" & fldQuestions.Name & "'"), 0) + 1
  92.                         sql3 = "UPDATE TBLReportCount SET Count6 = " & intCount & _
  93.                         " WHERE AnswerName = '" & fldQuestions.Name & "';"
  94.                         DoCmd.RunSQL sql3
  95.                      Case 7        'varNum = 7
  96.                         intCount = Nz(DLookup("[Count7]", "TBLReportCount", "[AnswerName] = '" & fldQuestions.Name & "'"), 0) + 1
  97.                         sql3 = "UPDATE TBLReportCount SET Count7 = " & intCount & _
  98.                         " WHERE AnswerName = '" & fldQuestions.Name & "';"
  99.                         DoCmd.RunSQL sql3
  100.                      Case 8        'varNum = 8
  101.                         intCount = Nz(DLookup("[Count8]", "TBLReportCount", "[AnswerName] = '" & fldQuestions.Name & "'"), 0) + 1
  102.                         sql3 = "UPDATE TBLReportCount SET Count8 = " & intCount & _
  103.                         " WHERE AnswerName = '" & fldQuestions.Name & "';"
  104.                         DoCmd.RunSQL sql3
  105.                      Case Else
  106.                     End Select
  107.                     strComments = ""
  108.                  End If
  109.                  DoCmd.SetWarnings True
  110.                End If
  111.         Next fldQuestions
  112.         rstQuestions.MoveNext
  113.         Loop
  114.  
  115. 'Close what was opened
  116. rstQuestions.Close
  117. dbQuestions.Close
  118.  
  119. 'Allow the "Please Wait" message to close
  120. YourVar = ""
  121.  
  122. Forms!FRMsurveyReports.Visible = False
  123. DoCmd.OpenForm ("FRMDynamicReport")
  124.  
  125. DoCmd.SetWarnings False
  126. DoCmd.DeleteObject acReport, "Report1"
  127. CreateDynamicReport "SELECT * FROM QRYReportCount"
  128. DoCmd.SetWarnings True
  129.  
  130. Exit_Command36_Click:
  131.         Set rstQuestions = Nothing
  132.         Set dbQuestions = Nothing
  133.         Exit Sub
  134.  
  135. Err_Command36_Click:
  136.         'Allow the "Please Wait" message to close
  137.         YourVar = ""
  138.         MsgBox "Error " & Err.Number & ": " & Err.Description
  139.         Resume Exit_Command36_Click
  140.  
  141. End Sub 
  142.  
  143. Public Function CreateDynamicReport(strSQL As String)
  144.  
  145. Dim db As DAO.Database ' database object
  146. Dim rs As DAO.Recordset ' recordset object
  147. Dim fld As DAO.Field ' recordset field
  148. Dim txtNew As Access.TextBox ' textbox control
  149. Dim lblNew As Access.Label ' label control
  150. Dim CmdBtnNew As Access.CommandButton ' command button control
  151. Dim rpt As Report ' hold report object
  152. Dim lngTop As Long ' holds top value of control position
  153. Dim lngLeft As Long ' holds left value of controls position
  154. Dim title As String ' holds title of report
  155. Dim lngWidth As Long  ' holds the width of the longest label in twips
  156.  
  157.      ' set the title
  158.      title = "Snapshot Report"
  159.  
  160.      ' set initial width to zero
  161.      lngWidth = 0
  162.  
  163.      ' initialise position variables
  164.      lngLeft = 100
  165.      lngTop = 0
  166.  
  167.      ' Create the report
  168.      Set rpt = CreateReport
  169.  
  170.      ' set properties of the Report
  171.      With rpt
  172.          .Width = 8500
  173.          .RecordSource = strSQL
  174.          .Caption = title
  175.      End With
  176.  
  177.      ' Open SQL query as a recordset
  178.      Set db = CurrentDb
  179.      Set rs = db.OpenRecordset(strSQL)
  180.  
  181.      ' Create Label Title
  182.      Set lblNew = CreateReportControl(rpt.Name, acLabel, _
  183.      acPageHeader, , title, 0, 0)
  184.      lblNew.FontBold = True
  185.      lblNew.FontSize = 12
  186.      lblNew.SizeToFit
  187.  
  188.      ' Create close command button
  189.      Set CmdBtnNew = CreateReportControl(rpt.Name, acCommandButton, acPageHeader, , , rpt.Width - 500, 0, 780, 360)
  190.      CmdBtnNew.Name = "BtnClose"
  191.      CmdBtnNew.Caption = "Close"
  192.      CmdBtnNew.DisplayWhen = 2
  193.  
  194.      ' Create corresponding label and text box controls for each field.
  195.      Dim strStoreValue As String
  196.      For Each fld In rs.Fields
  197.      If fld.Name Like "Count*" Then
  198.      strStoreValue = Nz(DLookup("[StoreValue]", "TBLSurveySelections", "FieldValue = " & Right(fld.Name, 1)), "!@#$")
  199.        If Not strStoreValue = "!@#$" Then ' Do not list controls for empty choices
  200.          ' Create new text box control and size to fit data.
  201.          Set txtNew = CreateReportControl(rpt.Name, acTextBox, _
  202.          acDetail, , fld.Name, lngLeft + 1500, lngTop)
  203.          txtNew.TextAlign = 1
  204.          txtNew.CanGrow = True
  205.          txtNew.CanShrink = True
  206.          txtNew.SizeToFit
  207.          txtNew.Name = fld.Name
  208.  
  209.          ' Create new label control and size to fit data.
  210.          Set lblNew = CreateReportControl(rpt.Name, acLabel, acDetail, _
  211.          txtNew.Name, strStoreValue, lngLeft, lngTop, 1400, txtNew.Height)
  212.          lblNew.SizeToFit
  213.          lblNew.Name = fld.Name & "_Label"
  214.  
  215.          ' Capture the longest label width
  216.          If lblNew.Width > lngWidth Then lngWidth = lblNew.Width
  217.  
  218.          ' Increment top value for next control
  219.          lngTop = lngTop + txtNew.Height + 25
  220.        End If
  221.      Else
  222.          ' Create new text box control and size to fit data.
  223.          Set txtNew = CreateReportControl(rpt.Name, acTextBox, _
  224.          acDetail, , fld.Name, lngLeft + 1500, lngTop)
  225.          txtNew.TextAlign = 1
  226.          txtNew.CanGrow = True
  227.          txtNew.CanShrink = True
  228.          txtNew.SizeToFit
  229.          txtNew.Name = fld.Name
  230.  
  231.          ' Create new label control and size to fit data.
  232.          Set lblNew = CreateReportControl(rpt.Name, acLabel, acDetail, _
  233.          txtNew.Name, fld.Name, lngLeft, lngTop, 1400, txtNew.Height)
  234.          lblNew.SizeToFit
  235.          lblNew.Name = fld.Name & "_Label"
  236.  
  237.          ' Capture the longest label width
  238.          If lblNew.Width > lngWidth Then lngWidth = lblNew.Width
  239.  
  240.          ' Increment top value for next control
  241.          lngTop = lngTop + txtNew.Height + 25
  242.      End If
  243.      Next
  244.  
  245.      ' Move text boxes into position according to length of labels
  246.      Dim ctl As Control
  247.      Dim ctlName As String
  248.      For Each ctl In rpt.Controls
  249.          If ctl.ControlType = acTextBox Then
  250.            ctlName = ctl.Name
  251.            rpt(ctlName).Left = lngLeft + lngWidth + 100
  252.            rpt(ctlName).Width = rpt.Width - lngLeft - lngWidth - 100
  253.          End If
  254.      Next ctl
  255.  
  256.      ' Create datestamp in Footer
  257.      Set lblNew = CreateReportControl(rpt.Name, acLabel, _
  258.      acPageFooter, , Now(), 0, 0)
  259.  
  260.      ' Create page numbering on footer
  261.      Set txtNew = CreateReportControl(rpt.Name, acTextBox, _
  262.      acPageFooter, , "='Page ' & [Page] & ' of ' & [Pages]", rpt.Width - 1000, 0)
  263.      txtNew.SizeToFit
  264.  
  265.      ' Set OnClose event of created report
  266.      Dim mdl As Module
  267.      Dim lngReturn As Long
  268.      Dim strCode As String
  269.  
  270.      ' Format and add an 'on close' event
  271.      strCode = vbCrLf & "   On Error Resume Next" & _
  272.                vbCrLf & "   Forms!FRMDynamicReport.Visible = True"
  273.  
  274.      Set rpt = Reports(rpt.Name)
  275.      Set mdl = rpt.Module
  276.      ' Add event procedure.
  277.      lngReturn = mdl.CreateEventProc("Close", "Report")
  278.      ' Insert text into body of procedure.
  279.      mdl.InsertLines lngReturn + 1, strCode
  280.  
  281.      ' Format and add an 'on open' event
  282.      strCode = vbCrLf & "   On Error Resume Next" & _
  283.                vbCrLf & "   Forms!FRMDynamicReport.Visible = False"
  284.  
  285.      ' Add event procedure.
  286.      lngReturn = mdl.CreateEventProc("Open", "Report")
  287.      ' Insert text into body of procedure.
  288.      mdl.InsertLines lngReturn + 1, strCode
  289.  
  290.  
  291.  
  292.      ' HOW DO I GET THE VB EDITOR WINDOW TO CLOSE?
  293.  
  294.      ' Open new report.
  295.      DoCmd.OpenReport rpt.Name, acViewPreview
  296.  
  297.      DoCmd.SetWarnings False
  298.      DoCmd.Save acReport, "Report1"
  299.      DoCmd.SetWarnings True
  300.  
  301.      'reset all objects
  302.      rs.Close
  303.      Set rs = Nothing
  304.      Set rpt = Nothing
  305.      Set db = Nothing
  306.  
  307. End Function
Oct 6 '10 #3

NeoPa
Expert Mod 15k+
P: 31,271
I've had a chat with Mary on this and it's going to be all but impossible to work on this without info as to what is going wrong where.

Can you handle the error and MsgBox the details - including the number, message and line number. Remember when you post this that the line numbers will be different so make sure any changes made are clear and can be worked on.
Oct 6 '10 #4

gnawoncents
100+
P: 212
Thanks for the reminder of using MsgBoxes. I inserted about 20 different ones, and identified the areas causing problems. As anticipated, I get the following:

Error 29045 ("You can't import, export, create, modify, or rename any forms, reports, pages or modules in an ACCDE, MDE or ADE database") when I try to delete a report (report1).

Error 7802 ("The command you specified is not available in an .mde, .accde, or .ade database) when I try to create/open the report on the code line below. (listed as 295 in the previous code block)
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenReport rpt.Name, acViewPreview
And, of course, line 298 causes an error when it tries to save the report that never opened.

I would really like to convert to an ACCDE, but it looks like I may not be able to for this database. That said, I have a few issues to resolve to secure the database. Would it be more appropriate to start a new thread for these, or continue here?

Thanks for all your help.
Oct 8 '10 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
OK I've been doing some research on this. It seems you can't create dynamic reports in design view in an executable file like mde or accde. The only solution is to create a template report with unassigned controls and then assign a record source to the report at runtime. You can then assign controls to the various fields.
Oct 8 '10 #6

NeoPa
Expert Mod 15k+
P: 31,271
That sounds like a good solution if it works. GnawOnCents (which is a strange concept to start with - very historical - but may just be phonetic, and self-deprecating), let us know if this approach works for you. As saving and making permanent changes to the design of the database is all that is restricted within an executable (AccDE or MDE), I expect it should. A pretty neat idea really :-)

If you prefer the other approach we can also help you there I expect.
Oct 8 '10 #7

gnawoncents
100+
P: 212
I've given up on the idea of being able to convert to ACCDE and will simply have to lock down the database as best I can. The template report does sound tempting, but I don't think it will work in my situation. When the report is run it might have 60 controls one time and 120 the next, all with different spacing, and needing to be saved with the new format/data each time.

I'm going to go ahead and mark this thread closed with Mary's suggestion in case it can help others. Thanks for all the help, I really appreciate it!
Oct 13 '10 #8

MMcCarthy
Expert Mod 10K+
P: 14,534
Something you could look at for the future is Excel Automation. It's what I use when I need these kind of dynamic reports. The reports can then be designed in Excel and automation used to populate the reports.

Mary
Oct 13 '10 #9

Post your reply

Sign in to post your reply or Sign up for a free account.