Hello all.:D

OK here it is. I have a form with five combo boxes that are populated with a table's field names and a single command button. After selecting one or more fields pressing the command button opens a report that has been formatted using the selected fields. The problem is that after I close the report it pops up in design mode and when I close that it then asks if I would like to save the changes. Ideally I would like the report to just close and not save the changes and return to the original form.

Here is the code I'm using in the command button:

Dim rs As DAO.Recordset
Dim i As Integer
Dim prevColwidth As Long
Dim lngMaxVal As Long
Dim fldval As String
Dim mywidthval As Integer

lngLeft = 0
lngTop = 0

DoCmd.OpenReport "AccessColumnBuilder", acViewDesign, , , acHidden
Set rpt = Reports![AccessColumnBuilder]
reportQuery = Me.Label44.Caption
Set rs = CodeDb().OpenRecordset(reportQuery)
rpt.RecordSource = reportQuery
prevColwidth = 0
lblCol = 0

For i = 0 To rs.Fields.Count - 1
        Set txtNew = CreateReportControl(rpt.Name, acTextBox, acDetail, , , lngLeft + prevColwidth, lngTop)
      txtNew.SizeToFit
      Debug.Print txtNew.Width
      mywidthval = txtNew.Width
      txtNew.ControlSource = rs(i).Name
      txtNew.TextAlign = 1
      prevColwidth = prevColwidth + txtNew.Width
      Set labNew = CreateReportControl(rpt.Name, acLabel, acPageHeader, , rs.Fields(i).Name, lblCol, , , lngTop)
      labNew.Width = mywidthval
      lblCol = lblCol + 5 + labNew.Width
Next

DoCmd.OpenReport "ReportBuilder", acViewPreview

End Sub




Thanks,
Jesse

Thanks,
Jesse

__________________
Thanks,
Jesse

Make sure you save the report programattically if you must open it in design mode programatically.

Also make sure that this app willnot be a shared access database or you will corrupt your data after a user opens the database exclusively and closes improperly.

I do not recommend doing run time mods like this one with Access. This is too much tayloring for users anyway. If you must give them this, just export the recordset to Excel.





Sal

The workaround was to create it and save it in hidden design mode and reopen it in normal mode again for the user. The entire cycle time remarcably takes no time at all to run. The label and textbox names are stored by a seperate function and after the user closes the report it is opened in hidden view in design mode and the labels and textboxes are programatically removed from the report and saved.

The db is an access adp which front ends an MS SQL app and each user is supplied their own copy via a CITRIX server logon script so each user creating their own reports locally are not a problem.

Thanks again.;)

Thanks,
Jesse