RogerCooper
Registered User.
- Local time
- Yesterday, 20:49
- Joined
- Jul 30, 2014
- Messages
- 283
I recently converted from Access 97 to Access 2013. My application runs a long (& changing) list of queries which are printed and/or e-mailed.
I have a module which examines a query and automatically chooses best fit for each field. In Access 97, this would resize the fields and save the query in the new format. E-Mails would be generated with default column widths in Excel 97 format.
Access 2013 works somewhat differently. When printing, it resizes the columns correctly and prints them but does not save the reformatted query. ( A minor issue in and of itself).
If I e-mail in XLS format, the column widths are default. If I e-mail in XLXS format, the column widths vary strangely, with date fields getting huge column widths. (Some users want XLXS format, so they can use mobile devices to read the files).
If I simply e-mail a query in XLXS format, the file usually reflects the column width of the original, but this seems to get messed up if I change the column widths automatically.
I see 2 possible solutions.
1. Get the reformatted query to save. Unfortunately the obvious solution of "DoCmd.Close , , acSaveYes" does not work.
2. Control the formatting of the XLXS file generated. I have no idea how to do that.
Does anyone have an idea of how I can proceed.
For anyone who needs to automatically resize a query, here is the code:
Public Function FixColumnWidthsOfQuery _
(stName As String) As Boolean
Dim DB As Database
Dim qdf As QueryDef
Dim frm As Form
Dim ictl As Integer
Dim ctl As Control
Set DB = CurrentDb
On Error GoTo Standard_Err
Set qdf = DB.QueryDefs(stName)
DoCmd.OpenQuery stName, acViewNormal
Set frm = Screen.ActiveDatasheet
For ictl = 0 To frm.Controls.Count - 1
Set ctl = frm.Controls(ictl)
ctl.ColumnWidth = -2
Call SetDAOFieldProperty(qdf.Fields(ictl), _
"ColumnWidth", ctl.ColumnWidth, dbInteger)
Next ictl
DoCmd.Save acQuery, stName
Standard_Err:
End Function
I have a module which examines a query and automatically chooses best fit for each field. In Access 97, this would resize the fields and save the query in the new format. E-Mails would be generated with default column widths in Excel 97 format.
Access 2013 works somewhat differently. When printing, it resizes the columns correctly and prints them but does not save the reformatted query. ( A minor issue in and of itself).
If I e-mail in XLS format, the column widths are default. If I e-mail in XLXS format, the column widths vary strangely, with date fields getting huge column widths. (Some users want XLXS format, so they can use mobile devices to read the files).
If I simply e-mail a query in XLXS format, the file usually reflects the column width of the original, but this seems to get messed up if I change the column widths automatically.
I see 2 possible solutions.
1. Get the reformatted query to save. Unfortunately the obvious solution of "DoCmd.Close , , acSaveYes" does not work.
2. Control the formatting of the XLXS file generated. I have no idea how to do that.
Does anyone have an idea of how I can proceed.
For anyone who needs to automatically resize a query, here is the code:
Public Function FixColumnWidthsOfQuery _
(stName As String) As Boolean
Dim DB As Database
Dim qdf As QueryDef
Dim frm As Form
Dim ictl As Integer
Dim ctl As Control
Set DB = CurrentDb
On Error GoTo Standard_Err
Set qdf = DB.QueryDefs(stName)
DoCmd.OpenQuery stName, acViewNormal
Set frm = Screen.ActiveDatasheet
For ictl = 0 To frm.Controls.Count - 1
Set ctl = frm.Controls(ictl)
ctl.ColumnWidth = -2
Call SetDAOFieldProperty(qdf.Fields(ictl), _
"ColumnWidth", ctl.ColumnWidth, dbInteger)
Next ictl
DoCmd.Save acQuery, stName
Standard_Err:
End Function
