I want to create a Save Layout button, so that clicking this button saves the ColumnOrder, ColumnWidth and ColumnHidden ect. for each column in the subform datasheet to a seperate layouts table with a layout name.
I can then have multiple layouts set for the single datasheet. I would like these to be selected via a combo drop down list box which in turn changes the datasheets layout.
I know I can define this hard coded in VBA. As show in the code below. If a column order is changed I do not know how to save these changes back to the layout table - With Me.form2.Form!field1
-
.ColumnHidden = False
-
.ColumnOrder = 1
-
.ColumnWidth = 864
-
End With
-
-
With Me.form2.Form!field3
-
.ColumnHidden = False
-
.ColumnOrder = 2
-
.ColumnWidth = 2880
-
End With
-
-
With Me.form2.Form!field4
-
.ColumnHidden = False
-
.ColumnOrder = 4
-
.ColumnWidth = -2 'Set for visible text.
-
End With
Any helps would be greatly appreciated.
Thanks
I would use twinnyfo's table structure. The way you are attempting to store it is not normalized and makes everything else more difficult. Especially if you ever need to add a field or property that you want to store. It also makes it more difficult to translate the layout data back to the controls. Technically speaking, the structure is still not normalized as you can have each property in its own row. However, this is one of those few situations in which slight denormalization is beneficial as opposed to detrimental. It makes for slightly more difficult upkeep but benefits by quicker performance.
As for setting the values, you could loop through a recordset setting one control at a time. - Do Until rst.EOF
-
Me.Controls(rst("FldName")).Properties("ColumnHidden") = rst("Hidden")
-
.......
-
rst.MoveNext
-
Loop
15 5098
To update your layout table, you can run an update query.
I want to rearrange the layout of the datasheet view, not the table design. Can you elaborate more please?
I know, but you said you saved the layout info in a table. Or at least that's what it sounded like. If not, that's what you'll need to do.
sorry I misintuperated what you meant. Do you no how I would Get the information of the layout of the Current datasheet (like the info from the code above)Via vba To then update the table?
I don't see that you've said anything any different from what you've already said.
You said
Do you no how I would Get the information of the layout of the Current datasheet (like the info from the code above)Via vba
The answer is, you've already done that. In the very code you mention. You can retrieve the value of the layout with the same property that you're using to set the values.
And you say
To then update the table?
I already answer that in post #2 and #4. If you have a table, then run an update query. If not, then create one and run an update query.
I appreciate your patience, im finding it hard to explain what exactly I want to do.
I have a form, with a datasheet subform on. On this form I want a combo box drop down list. And A few buttons. "Save New layout" and "save layout".
When the user clicks on the combo box, they will see a list of different layout names. Depending on what they click, will rearrange the columns (This can be done using the defined code above).
If however a user manually arranges the columns they may want to save this as a new layout. I have already hard coded the layouts so how would the changes be saved?
This bring me to the idea of using another table to store the column information in, this can be dynamically updated when the user clicks "save layout". I however have no idea how to update this table with the information such as ColumnOrder or ColumnHidden ect.
I have attached a simple database which should help to explain.
I hope this makes a bit more sense!
Thanks.
I understand what you're after. And you're on the right track. You will need a table to store the layout information. As far as how do you update it, run an update query. - DoCmd.RunSQL "UPDATE layoutTable SET ColumnOrder = some value WHERE ..."
How you set up the update statement exactly will depend on your table structure but the basic idea is there.
Thanks that makes sense, using update to change current layouts, and Insert to create new layouts in the table.
How would I get the values of the current layout?
ie if the ID field is in ColumnOrder 5? - DoCmd.RunSQL "UPDATE layoutTable SET ColumnOrder = "HOW DO I GET THIS VALUE?" WHERE ..."
-
Are you able to do an example for the database I uploaded and then I can see how it works and implement it on my current database?
You can retrieve that value with the same property you're using to set it. - DoCmd.RunSQL "UPDATE layoutTable" & _
-
" SET ColumnWidth = " & Me.form2.Form!field1.ColumnWidth & _
-
" WHERE ColumnName = 'field1'" & _
-
" AND LayoutName = 'my layout'"
Yes that's excellent! Thank you very much. I can now get this to work.
The only problem I have, is if I have 30+ fields to define in this way the code is going to become messy?
Is there a way I can concentrate this into a loop procedure and store all the columns information into a memo field like below?
field1,False,1,864;field2,False,2,2880;field3,Fals e,3,705;field4,False,4,864;field5,False,5,2880;fie ld6,False,6,630;
I have wrote a procedure to get the currant data and update the layout table via a loop. -
Private Sub SaveLayout_Click()
-
On Error Resume Next
-
Dim LayoutInfo As String
-
Dim ctl As Control
-
For Each ctl In Me.form2.Form.Controls
-
LayoutInfo = Nz(LayoutInfo) + ctl.Name & "," & ctl.ColumnHidden & "," & ctl.ColumnOrder & "," & ctl.ColumnWidth & ";"
-
Next
-
Debug.Print LayoutInfo
-
DoCmd.RunSQL "UPDATE layoutTable SET info =LayoutInfo where name =cbolayout "
-
End Sub
-
If I then want to call this information again (after I chose the new layout from the combo box)and rearrange the layout how would this be done?
Or can you think of an easier way to achieve this?
Regards
You could also have a loop that assessed each control and the layout parameters, and save to a table with only a few fields: -
LayoutName FldName Hidden Order Width
-
MyLayout1 field1 False 1 864
-
MyLayout1 field2 False 2 2880
-
MyLayout1 field3 False 3 705
-
MyLayout1 field4 False 4 864
-
MyLayout1 field5 False 5 2880
-
MyLayout1 field6 False 6 630
-
This would allow multiple layouts, and allow for an unknown number of fields, which you code could capture.
Then you just "decode" the layout back into the datasheet. Hope this hepps!
I would use twinnyfo's table structure. The way you are attempting to store it is not normalized and makes everything else more difficult. Especially if you ever need to add a field or property that you want to store. It also makes it more difficult to translate the layout data back to the controls. Technically speaking, the structure is still not normalized as you can have each property in its own row. However, this is one of those few situations in which slight denormalization is beneficial as opposed to detrimental. It makes for slightly more difficult upkeep but benefits by quicker performance.
As for setting the values, you could loop through a recordset setting one control at a time. - Do Until rst.EOF
-
Me.Controls(rst("FldName")).Properties("ColumnHidden") = rst("Hidden")
-
.......
-
rst.MoveNext
-
Loop
Thanks very much twinnyfo and Rabbit.
I have cracked it with your help :) -
Do Until Rst.EOF
-
With Me.FORM-NAME.Form(Rst("fieldname"))
-
.ColumnHidden = Rst!fieldhidden
-
.ColumnOrder = Rst!fieldorder
-
.ColumnWidth = Rst!fieldwidth
-
End With
-
Rst.MoveNext
-
Loop
-
The only problem I have is defining the Field name. -
-
Dim ctl As Control
-
Rst!FieldName = ctl.Name
-
Rst!fieldhidden = ctl.ColumnHidden
-
Rst!fieldwidth = ctl.ColumnWidth
-
Rst!fieldorder = ctl.ColumnOrder
If I use the code above to inset ctl.Name returns the field name and label name.
I get the valuses - RowID and RowID_Label.
To over come error messages when setting up the values after the combo box is selected I have used
Is there an easier way to define just the field name without the label?
Thanks
This should exclude labels. - If ctl.Properties("ControlType") <> 100 Then
-
...
-
End If
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Thelma Lubkin |
last post by:
I'm resubmitting a question that went unanswered a while ago.
I'll try to rephrase it.
I have a hierarchical form that presents data on an 'organization'.
It has 2 subforms, the first a...
|
by: Michael R |
last post by:
Hello to all.
In what way can I update a certain selected records' values on a datasheet subform using a command button which is located on the main form?
Thanks and cheers.
|
by: anthopper |
last post by:
hello-i am a new access user--access 2003 winXP pro.
I am trying to create a form with a subform that will allow me to navigate though all of the records from a single table.
From a formatting...
|
by: paulw4 |
last post by:
I have a mail form that has a datasheet subform, there can be up to sixty items in the datasheet. The data sheet has three fields: AttID, AttName, AttYN
The object is for the enduser to select...
|
by: QCLee |
last post by:
Hello Sir, Im here again asking for help. I have a form and a subform relate to HVAC Windward Table. i have a SAVE button on the form that when clicked it saves data on the HVAC Windward Table but...
|
by: WPW07 |
last post by:
Hello Everyone,
I stumbled onto something interesting and was wondering if anyone
else has experienced it and how they fixed it. I've posted to several
forums to no avail.
I've got a...
|
by: WyvsEyeView |
last post by:
I am doing the very standard thing of filtering the contents of one combo box based on another combo box. I've done it many times, but always on a main form. Now I'm trying to do it on a datasheet...
|
by: pzh20 |
last post by:
I have an unbound form/subform where I populate a combo box on the main form, and using the onchange event, display fields from a table in a datasheet subform.
I want to add a new record via the...
|
by: MayoM |
last post by:
Access 2003 has suddenly reordered the sequence of columns in a datasheet subform.
Specifically the last column has stayed in place but the others are back to front. There are about 50 columns in...
|
by: Daniel B |
last post by:
I have a search form where I can enter in an ID# and I want to create a command button that, when clicked, it will bring up another form with a subform that displays the data for the ID# that was...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
| |