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!
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.
8 3188
Can you post the relevant code. It would help to figure this out.
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. -
Private Sub Command36_Click()
-
On Error GoTo Err_Command36_Click
-
-
'Setup recordset
-
Call Create_Report_Recordset
-
-
'Test for no records
-
If strSQLreport = "No records" Then Exit Sub
-
-
'Delete all records currently in the TBLReportCount table
-
DoCmd.SetWarnings False
-
DoCmd.OpenQuery "QRYdeleteReportCountALL"
-
DoCmd.SetWarnings True
-
-
'Update TBLReportCount with number of each type of answer for each question
-
Dim dbQuestions As Database
-
Dim rstQuestions As Recordset
-
Dim fldQuestions As Field
-
Dim sql3 As String
-
-
Set dbQuestions = CurrentDb()
-
Set rstQuestions = dbQuestions.OpenRecordset(strSQLreport)
-
-
'Add the required fields to the TBLReportCount Table
-
Dim dbs2 As Database
-
Set dbs2 = CurrentDb()
-
rstQuestions.MoveLast
-
rstQuestions.MoveFirst
-
Do While Not rstQuestions.EOF
-
For Each fldQuestions In rstQuestions.Fields
-
If fldQuestions.Name Like "*Answer" Then
-
If Not IsNull(fldQuestions) Then
-
dbs2.Execute " INSERT INTO TBLReportCount " _
-
& "(AnswerNumber, AnswerName) VALUES " _
-
& "(" & Mid(Replace(fldQuestions.Name, "Answer", ""), 2) & " , '" & fldQuestions.Name & "');"
-
End If
-
End If
-
Next fldQuestions
-
rstQuestions.MoveNext
-
Loop
-
-
Dim strComments As String
-
Dim strLOOKUP As String
-
strComments = ""
-
Dim varNUM As Long
-
Dim intCount As Integer
-
-
'Count the answers
-
rstQuestions.MoveLast
-
rstQuestions.MoveFirst
-
Do While Not rstQuestions.EOF
-
For Each fldQuestions In rstQuestions.Fields
-
If Not IsNull(fldQuestions) Then
-
DoCmd.SetWarnings False
-
If fldQuestions.Name Like "*Answer" Then
-
strLOOKUP = Replace(fldQuestions.Name, "Answer", "Comments")
-
If Not IsNull(rstQuestions.Fields(strLOOKUP)) Then
-
strComments = Nz(DLookup("[Comments]", "TBLReportCount", "[AnswerName] = '" & fldQuestions.Name & "'"), "") & "Survey ID: " & rstQuestions!SurveyID & " Survey Response: " & fldQuestions & " Comment: " & rstQuestions.Fields(strLOOKUP) & vbCr & vbLf
-
sql3 = "UPDATE TBLReportCount SET Comments = '" & strComments & "'" & _
-
" WHERE AnswerName = '" & fldQuestions.Name & "';"
-
DoCmd.RunSQL sql3
-
End If
-
varNUM = Nz(DLookup("[FieldValue]", "TBLSurveySelections", "[StoreValue] = '" & fldQuestions & "'"), 0)
-
Select Case varNUM
-
Case 1 'varNum = 1
-
intCount = Nz(DLookup("[Count1]", "TBLReportCount", "[AnswerName] = '" & fldQuestions.Name & "'"), 0) + 1
-
sql3 = "UPDATE TBLReportCount SET Count1 = " & intCount & _
-
" WHERE AnswerName = '" & fldQuestions.Name & "';"
-
DoCmd.RunSQL sql3
-
Case 2 'varNum = 2
-
intCount = Nz(DLookup("[Count2]", "TBLReportCount", "[AnswerName] = '" & fldQuestions.Name & "'"), 0) + 1
-
sql3 = "UPDATE TBLReportCount SET Count2 = " & intCount & _
-
" WHERE AnswerName = '" & fldQuestions.Name & "';"
-
DoCmd.RunSQL sql3
-
Case 3 'varNum = 3
-
intCount = Nz(DLookup("[Count3]", "TBLReportCount", "[AnswerName] = '" & fldQuestions.Name & "'"), 0) + 1
-
sql3 = "UPDATE TBLReportCount SET Count3 = " & intCount & _
-
" WHERE AnswerName = '" & fldQuestions.Name & "';"
-
DoCmd.RunSQL sql3
-
Case 4 'varNum = 4
-
intCount = Nz(DLookup("[Count4]", "TBLReportCount", "[AnswerName] = '" & fldQuestions.Name & "'"), 0) + 1
-
sql3 = "UPDATE TBLReportCount SET Count4 = " & intCount & _
-
" WHERE AnswerName = '" & fldQuestions.Name & "';"
-
DoCmd.RunSQL sql3
-
Case 5 'varNum = 5
-
intCount = Nz(DLookup("[Count5]", "TBLReportCount", "[AnswerName] = '" & fldQuestions.Name & "'"), 0) + 1
-
sql3 = "UPDATE TBLReportCount SET Count5 = " & intCount & _
-
" WHERE AnswerName = '" & fldQuestions.Name & "';"
-
DoCmd.RunSQL sql3
-
Case 6 'varNum = 6
-
intCount = Nz(DLookup("[Count6]", "TBLReportCount", "[AnswerName] = '" & fldQuestions.Name & "'"), 0) + 1
-
sql3 = "UPDATE TBLReportCount SET Count6 = " & intCount & _
-
" WHERE AnswerName = '" & fldQuestions.Name & "';"
-
DoCmd.RunSQL sql3
-
Case 7 'varNum = 7
-
intCount = Nz(DLookup("[Count7]", "TBLReportCount", "[AnswerName] = '" & fldQuestions.Name & "'"), 0) + 1
-
sql3 = "UPDATE TBLReportCount SET Count7 = " & intCount & _
-
" WHERE AnswerName = '" & fldQuestions.Name & "';"
-
DoCmd.RunSQL sql3
-
Case 8 'varNum = 8
-
intCount = Nz(DLookup("[Count8]", "TBLReportCount", "[AnswerName] = '" & fldQuestions.Name & "'"), 0) + 1
-
sql3 = "UPDATE TBLReportCount SET Count8 = " & intCount & _
-
" WHERE AnswerName = '" & fldQuestions.Name & "';"
-
DoCmd.RunSQL sql3
-
Case Else
-
End Select
-
strComments = ""
-
End If
-
DoCmd.SetWarnings True
-
End If
-
Next fldQuestions
-
rstQuestions.MoveNext
-
Loop
-
-
'Close what was opened
-
rstQuestions.Close
-
dbQuestions.Close
-
-
'Allow the "Please Wait" message to close
-
YourVar = ""
-
-
Forms!FRMsurveyReports.Visible = False
-
DoCmd.OpenForm ("FRMDynamicReport")
-
-
DoCmd.SetWarnings False
-
DoCmd.DeleteObject acReport, "Report1"
-
CreateDynamicReport "SELECT * FROM QRYReportCount"
-
DoCmd.SetWarnings True
-
-
Exit_Command36_Click:
-
Set rstQuestions = Nothing
-
Set dbQuestions = Nothing
-
Exit Sub
-
-
Err_Command36_Click:
-
'Allow the "Please Wait" message to close
-
YourVar = ""
-
MsgBox "Error " & Err.Number & ": " & Err.Description
-
Resume Exit_Command36_Click
-
-
End Sub
-
-
Public Function CreateDynamicReport(strSQL As String)
-
-
Dim db As DAO.Database ' database object
-
Dim rs As DAO.Recordset ' recordset object
-
Dim fld As DAO.Field ' recordset field
-
Dim txtNew As Access.TextBox ' textbox control
-
Dim lblNew As Access.Label ' label control
-
Dim CmdBtnNew As Access.CommandButton ' command button control
-
Dim rpt As Report ' hold report object
-
Dim lngTop As Long ' holds top value of control position
-
Dim lngLeft As Long ' holds left value of controls position
-
Dim title As String ' holds title of report
-
Dim lngWidth As Long ' holds the width of the longest label in twips
-
-
' set the title
-
title = "Snapshot Report"
-
-
' set initial width to zero
-
lngWidth = 0
-
-
' initialise position variables
-
lngLeft = 100
-
lngTop = 0
-
-
' Create the report
-
Set rpt = CreateReport
-
-
' set properties of the Report
-
With rpt
-
.Width = 8500
-
.RecordSource = strSQL
-
.Caption = title
-
End With
-
-
' Open SQL query as a recordset
-
Set db = CurrentDb
-
Set rs = db.OpenRecordset(strSQL)
-
-
' Create Label Title
-
Set lblNew = CreateReportControl(rpt.Name, acLabel, _
-
acPageHeader, , title, 0, 0)
-
lblNew.FontBold = True
-
lblNew.FontSize = 12
-
lblNew.SizeToFit
-
-
' Create close command button
-
Set CmdBtnNew = CreateReportControl(rpt.Name, acCommandButton, acPageHeader, , , rpt.Width - 500, 0, 780, 360)
-
CmdBtnNew.Name = "BtnClose"
-
CmdBtnNew.Caption = "Close"
-
CmdBtnNew.DisplayWhen = 2
-
-
' Create corresponding label and text box controls for each field.
-
Dim strStoreValue As String
-
For Each fld In rs.Fields
-
If fld.Name Like "Count*" Then
-
strStoreValue = Nz(DLookup("[StoreValue]", "TBLSurveySelections", "FieldValue = " & Right(fld.Name, 1)), "!@#$")
-
If Not strStoreValue = "!@#$" Then ' Do not list controls for empty choices
-
' Create new text box control and size to fit data.
-
Set txtNew = CreateReportControl(rpt.Name, acTextBox, _
-
acDetail, , fld.Name, lngLeft + 1500, lngTop)
-
txtNew.TextAlign = 1
-
txtNew.CanGrow = True
-
txtNew.CanShrink = True
-
txtNew.SizeToFit
-
txtNew.Name = fld.Name
-
-
' Create new label control and size to fit data.
-
Set lblNew = CreateReportControl(rpt.Name, acLabel, acDetail, _
-
txtNew.Name, strStoreValue, lngLeft, lngTop, 1400, txtNew.Height)
-
lblNew.SizeToFit
-
lblNew.Name = fld.Name & "_Label"
-
-
' Capture the longest label width
-
If lblNew.Width > lngWidth Then lngWidth = lblNew.Width
-
-
' Increment top value for next control
-
lngTop = lngTop + txtNew.Height + 25
-
End If
-
Else
-
' Create new text box control and size to fit data.
-
Set txtNew = CreateReportControl(rpt.Name, acTextBox, _
-
acDetail, , fld.Name, lngLeft + 1500, lngTop)
-
txtNew.TextAlign = 1
-
txtNew.CanGrow = True
-
txtNew.CanShrink = True
-
txtNew.SizeToFit
-
txtNew.Name = fld.Name
-
-
' Create new label control and size to fit data.
-
Set lblNew = CreateReportControl(rpt.Name, acLabel, acDetail, _
-
txtNew.Name, fld.Name, lngLeft, lngTop, 1400, txtNew.Height)
-
lblNew.SizeToFit
-
lblNew.Name = fld.Name & "_Label"
-
-
' Capture the longest label width
-
If lblNew.Width > lngWidth Then lngWidth = lblNew.Width
-
-
' Increment top value for next control
-
lngTop = lngTop + txtNew.Height + 25
-
End If
-
Next
-
-
' Move text boxes into position according to length of labels
-
Dim ctl As Control
-
Dim ctlName As String
-
For Each ctl In rpt.Controls
-
If ctl.ControlType = acTextBox Then
-
ctlName = ctl.Name
-
rpt(ctlName).Left = lngLeft + lngWidth + 100
-
rpt(ctlName).Width = rpt.Width - lngLeft - lngWidth - 100
-
End If
-
Next ctl
-
-
' Create datestamp in Footer
-
Set lblNew = CreateReportControl(rpt.Name, acLabel, _
-
acPageFooter, , Now(), 0, 0)
-
-
' Create page numbering on footer
-
Set txtNew = CreateReportControl(rpt.Name, acTextBox, _
-
acPageFooter, , "='Page ' & [Page] & ' of ' & [Pages]", rpt.Width - 1000, 0)
-
txtNew.SizeToFit
-
-
' Set OnClose event of created report
-
Dim mdl As Module
-
Dim lngReturn As Long
-
Dim strCode As String
-
-
' Format and add an 'on close' event
-
strCode = vbCrLf & " On Error Resume Next" & _
-
vbCrLf & " Forms!FRMDynamicReport.Visible = True"
-
-
Set rpt = Reports(rpt.Name)
-
Set mdl = rpt.Module
-
' Add event procedure.
-
lngReturn = mdl.CreateEventProc("Close", "Report")
-
' Insert text into body of procedure.
-
mdl.InsertLines lngReturn + 1, strCode
-
-
' Format and add an 'on open' event
-
strCode = vbCrLf & " On Error Resume Next" & _
-
vbCrLf & " Forms!FRMDynamicReport.Visible = False"
-
-
' Add event procedure.
-
lngReturn = mdl.CreateEventProc("Open", "Report")
-
' Insert text into body of procedure.
-
mdl.InsertLines lngReturn + 1, strCode
-
-
-
-
' HOW DO I GET THE VB EDITOR WINDOW TO CLOSE?
-
-
' Open new report.
-
DoCmd.OpenReport rpt.Name, acViewPreview
-
-
DoCmd.SetWarnings False
-
DoCmd.Save acReport, "Report1"
-
DoCmd.SetWarnings True
-
-
'reset all objects
-
rs.Close
-
Set rs = Nothing
-
Set rpt = Nothing
-
Set db = Nothing
-
-
End Function
NeoPa 32,556
Expert Mod 16PB
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.
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) -
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.
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.
NeoPa 32,556
Expert Mod 16PB
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.
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!
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
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Bernard Koninckx |
last post by:
Hi everybody,
The following code (putted in a inherited object from AbstractTableModel
object) make some errors :
public void deleteRow(int rowToDelete){
try{
Object dataObject =...
|
by: Mark |
last post by:
Not sure this is the right place for this questions, but here goes:
I get an error message when deleting an table from a Access database.
The code is as follows and the error message is after...
|
by: intl04 |
last post by:
I am getting strange print-related error messages when trying to
create (not print!) reports. For example, when I click 'new' to create
a report then choose 'design view', I get an error message...
|
by: Alpha |
last post by:
Hi, I just added a new form with Crystl report viewer but one of my other
form suddently starts giving me an error message when I rebuild the code.
The error is complainig about the class name. ...
|
by: Martin Bischoff |
last post by:
Hi,
I'm creating temporary directories in my web app (e.g. ~/data/temp/temp123)
to allow users to upload files. When I later delete these directories (from
the code behind), the application...
|
by: Patrick Vanden Driessche |
last post by:
Hi All,
I'm currently writing an in-house Form validation framework (WinForms) which
is based on 'Component'-inheriting object.
So basically, I have a small hierarchy.
FormValidator
+--...
|
by: John Veldthuis |
last post by:
My code works perfectly 100% when adding items to my ArrayList and updating the listbox.
Works perfectly when deleting an item in the ArrayList when it is not the last entry but if it is
the last...
|
by: Joe Van Dyk |
last post by:
When you delete a pointer, you should set it to NULL, right?
Joe
|
by: Rabbit |
last post by:
Dear All,
I have recently using ASP.net 2.0 to build web application with Crystal
Report for .net, one of my typical coding to generate report on web page as
follows:
Dim rpt As New...
|
by: techuse |
last post by:
how can i raise the error massage in jasper i report if no record found in database?
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
| |