Change a field attributes in a query eg width

[Pat]

Is there a way in VBA to display and change a fields Width as well as the order of the fields in a query?
The reason is that users want to see the queries fields in a different order from left to right.
eg. a query has fields A, B, C, D
User 1 wants to see them as A, B, C, D
User 2 wants to see them as A, B, D, C
User 3 wants to see them as B, C, D, A

I was thinking of opening the query after the user has changed it and save the query in a table with all the widths saved as well.
Then when the user logged in i would reconstruct the query before he/she used it.

[HansV]

You can use the properties ColumnOrder, ColumnWidth and ColumnHidden of the DAO Field object.

ColumnOrder is of type dbInteger; it specifies the position in which the field is displayed (1= first place, 2 = second place etc.)
ColumnWidth is of type dbInteger; it specifies the column width in twips, where 1440 twips = 1 inch.
ColumnHidden is of type dbBoolean; it specifies whether the column is hidden (True = hidden, False = visible)

These properties are custom properties, they initially don't exist, so you have to create them if necessary.

Here is an example. It could be streamlined.

Code: Select all

Sub SetProperty()
  Dim dbs As DAO.Database
  Dim qdf As DAO.QueryDef
  Dim fld As DAO.Field
  Dim prp As DAO.Property
  Dim strName As String
  Dim intType As Integer
  Dim varValue As Variant
  Set dbs = CurrentDb
  Set qdf = dbs.QueryDefs("Query1")
  Set fld = qdf.Fields("LastName")
  ' Specify name etc. of the property we're going to set
  strName = "ColumnWidth"
  intType = dbInteger
  varValue = 2880 ' 2 inches
  ' Error handler is necessary because the property may not exist
  On Error GoTo ErrHandler
  Set prp = fld.Properties(strName)
  prp.Value = varValue

ExitHandler:
  fld.Properties.Refresh
  Set prp = Nothing
  Set fld = Nothing
  Set qdf = Nothing
  Set dbs = Nothing
  Exit Sub

ErrHandler:
  If Err = 3270 Then
    ' Property not found
    Set prp = fld.CreateProperty(Name:=strName, Type:=intType)
    Resume Next
  Else
    ' Other error
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
  End If
End Sub

[Pat]

Thanks Hans, i'll muck around with it.

By the way, what does "LastName" mean in the following?

Set fld = qdf.Fields("LastName")

[HansV]

Please note that I have edited my reply. The code originally contained the line

qdf.Properties.Refresh

This should be

fld.Properties.Refresh

[Pat]

Hi Hans, my code is :

Code: Select all

Private Sub CommandGet_Click()
    Dim q As dao.QueryDef
    Dim f As dao.Field
    Set q = CurrentDb.QueryDefs("Query3")
    For Each f In q.Fields
        Debug.Print f.name, f.Properties("ColumnOrder"), f.Properties("ColumnWidth")
    Next f
    MsgBox "end"
End Sub

It gives the first 3 columns ok but bombs on the fourth saying "Property not found" for ColumnOrder and ColumnWidth

[HansV]

As I mentioned in my first reply, the ColumnOrder and ColumnWidth properties don't exist initially - Access only creates them when you change the column order or column width of a field. So if you never changed the column order or column width for the fourth field, the properties don't exist yet and your code will cause an error.

The code that I posted tries to refer to the ColumnWidth property, and uses an error handler to create the property on the fly if it doesn't exist yet.

[Pat]

I missed that point, i am sorry about that.

So I should change all the columns width or order before it introduces those.

[Pat]

As I mentioned in my first reply, the ColumnOrder and ColumnWidth properties don't exist initially - Access only creates them when you change the column order or column width of a field. So if you never changed the column order or column width for the fourth field, the properties don't exist yet and your code will cause an error.

I have changed every columns width and it still bombs on the 4th column.
I am nonplussed.

[HansV]

That's strange - without seeing the database I have no idea what could cause this.

[Pat]

I cant do that as it is proprietary. I may try a cutdown version later.
In the mean time i need to get a form to show a list of records, allow the user to move columns around and size them. The i want the form to be able to sort a column ascending/descending.
What should i do?
I have decided (right or wrong) to go with a datasheet form based upon a query, this means i can resize or move columns around, however i cant choose a column to sort. Any ideas?

[HansV]

It's built-in: the user can right-click anywhere in a column and select Sort Ascending or Sort Descending from the popup menu.
These options are also available in the Records | Sort submenu and on the Form View toolbar (Access 2003 or earlier) or on the Ribbon (Access 2007 or later).
Access 2007 and later also have dropdown buttons in the column headers.

[Pat]

Well silly me, so you can. I have just tried it, that's great.
Sorry to bother you Hans.

I may have been too quick, after all this database has everything turned off in the startup options.
Correction, it seems fine thanks.

[Pat]

A further question on this subject.
Can the saved parameters (ColumnWidth and ColumnOrder) be used to re-establish the query to how the user had it?

Can the properties (ColumnWidth and ColumnOrder) be set in the query?

[HansV]

It's best to give each user his/her own copy of the frontend. The column settings are stored with the query or datasheet form, so users can arrange the columns to their liking.
If you use a shared frontend, you'd run into problems if multiple users open the query or datasheet form at the same time - each user's settings would overrule the previous ones.

[Pat]

I always give users their own copy of the FE, the problem arises when changes are made to the FE which then replaces the users FE.

[HansV]

You could create a table in the backend like this:

Username	FormName	FieldName	ColumnOrder	ColumnWidth
Pat	frmData	LastName	2	1440
Pat	frmData	FirstName	1	2160
John	frmData	LastName	1	720
John	frmData	FirstName	2	2880

When the form is opened, read the data for the form and the current user from the table, and use them to set the column properties as described in an earlier reply.
When the form is closed, write the column properties back to the table.

[HansV]

Another option would be to store the settings in the user's registry using SaveSetting when closing the form, and to read them using GetSetting when opening the form.

[Pat]

You could create a table in the backend like this:

Username	FormName	FieldName	ColumnOrder	ColumnWidth
Pat	frmData	LastName	2	1440
Pat	frmData	FirstName	1	2160
John	frmData	LastName	1	720
John	frmData	FirstName	2	2880

When the form is opened, read the data for the form and the current user from the table, and use them to set the column properties as described in an earlier reply.
When the form is closed, write the column properties back to the table.

Nice solution, thank you.

[Pat]

When the form is opened, read the data for the form and the current user from the table, and use them to set the column properties as described in an earlier reply.
When the form is closed, write the column properties back to the table.

So when the form is opened i set the property of each column of the form (Or the query behind the datasheet form)?
Do you address the form or the query?
Any chance of showing me a slice of code that can do this?

[HansV]

It has to be handled in the form - changes to the column widths etc. in a table or query don't affect a datasheet form based on it.

The attached database contains a table tblSettings and a module basSettings that contains two functions: LoadSettings and SaveSettings. These functions can be used with any datasheet form. To make the form use them, enter =LoadSettings() in the On Load or On Open event and enter =SaveSettings() in the On Close or On Unload event of the form:

x276.png

If you already have code for one of these events, you can also call the function from the event procedure, for example

Code: Select all

Private Sub Form_Open(Cancel As Integer)
  ...
  Call LoadSettings
End Sub

If there is no record for a specific control, it will use the settings saved with the form itself. When the user closes the form, a record will be created to save the settings.

In a split frontend/backend design, the table tblSettings should be stored in the backend, and each frontend should contain a link to this table.

Datasheet.zip