Change a field attributes in a query eg width
-
- 5StarLounger
- Posts: 1148
- Joined: 08 Feb 2010, 21:27
Change a field attributes in a query eg width
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.
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.
-
- Administrator
- Posts: 78595
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Change a field attributes in a query eg width
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.
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
Last edited by HansV on 09 Aug 2010, 07:46, edited 1 time in total.
Reason: to correct error
Reason: to correct error
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 1148
- Joined: 08 Feb 2010, 21:27
Re: Change a field attributes in a query eg width
Thanks Hans, i'll muck around with it.
By the way, what does "LastName" mean in the following?
Set fld = qdf.Fields("LastName")
By the way, what does "LastName" mean in the following?
Set fld = qdf.Fields("LastName")
Last edited by Pat on 09 Aug 2010, 07:57, edited 1 time in total.
-
- Administrator
- Posts: 78595
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Change a field attributes in a query eg width
Please note that I have edited my reply. The code originally contained the line
qdf.Properties.Refresh
This should be
fld.Properties.Refresh
qdf.Properties.Refresh
This should be
fld.Properties.Refresh
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 1148
- Joined: 08 Feb 2010, 21:27
Re: Change a field attributes in a query eg width
Hi Hans, my code is :
It gives the first 3 columns ok but bombs on the fourth saying "Property not found" for ColumnOrder and ColumnWidth
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
-
- Administrator
- Posts: 78595
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Change a field attributes in a query eg width
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.
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.
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 1148
- Joined: 08 Feb 2010, 21:27
Re: Change a field attributes in a query eg width
I missed that point, i am sorry about that.
So I should change all the columns width or order before it introduces those.
So I should change all the columns width or order before it introduces those.
-
- 5StarLounger
- Posts: 1148
- Joined: 08 Feb 2010, 21:27
Re: Change a field attributes in a query eg width
I have changed every columns width and it still bombs on the 4th column.HansV wrote: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 am nonplussed.
-
- Administrator
- Posts: 78595
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Change a field attributes in a query eg width
That's strange - without seeing the database I have no idea what could cause this.
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 1148
- Joined: 08 Feb 2010, 21:27
Re: Change a field attributes in a query eg width
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?
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?
-
- Administrator
- Posts: 78595
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Change a field attributes in a query eg width
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.
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.
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 1148
- Joined: 08 Feb 2010, 21:27
Re: Change a field attributes in a query eg width
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.
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.
-
- 5StarLounger
- Posts: 1148
- Joined: 08 Feb 2010, 21:27
Re: Change a field attributes in a query eg width
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?
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?
-
- Administrator
- Posts: 78595
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Change a field attributes in a query eg width
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.
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.
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 1148
- Joined: 08 Feb 2010, 21:27
Re: Change a field attributes in a query eg width
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.
-
- Administrator
- Posts: 78595
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Change a field attributes in a query eg width
You could create a table in the backend like this:
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.
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 closed, write the column properties back to the table.
Best wishes,
Hans
Hans
-
- Administrator
- Posts: 78595
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Change a field attributes in a query eg width
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.
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 1148
- Joined: 08 Feb 2010, 21:27
Re: Change a field attributes in a query eg width
Nice solution, thank you.HansV wrote:You could create a table in the backend like this:
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.
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 closed, write the column properties back to the table.
-
- 5StarLounger
- Posts: 1148
- Joined: 08 Feb 2010, 21:27
Re: Change a field attributes in a query eg width
So when the form is opened i set the property of each column of the form (Or the query behind the datasheet form)?HansV wrote: 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.
Do you address the form or the query?
Any chance of showing me a slice of code that can do this?
-
- Administrator
- Posts: 78595
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Change a field attributes in a query eg width
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:
If you already have code for one of these events, you can also call the function from the event procedure, for example
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.
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:
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
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.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans