By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,561 Members | 3,083 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,561 IT Pros & Developers. It's quick & easy.

Create a Save Layout Button for Datasheet Subform

AdamHope
P: 13
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

Expand|Select|Wrap|Line Numbers
  1. With Me.form2.Form!field1
  2. .ColumnHidden = False
  3. .ColumnOrder = 1
  4. .ColumnWidth = 864
  5. End With
  6.  
  7. With Me.form2.Form!field3
  8. .ColumnHidden = False
  9. .ColumnOrder = 2
  10. .ColumnWidth = 2880
  11. End With
  12.  
  13. With Me.form2.Form!field4
  14. .ColumnHidden = False
  15. .ColumnOrder = 4
  16. .ColumnWidth = -2 'Set for visible text.
  17. End With
Any helps would be greatly appreciated.

Thanks
Oct 12 '12 #1

✓ answered by Rabbit

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.
Expand|Select|Wrap|Line Numbers
  1. Do Until rst.EOF
  2.    Me.Controls(rst("FldName")).Properties("ColumnHidden") = rst("Hidden")
  3.    .......
  4.    rst.MoveNext
  5. Loop

Share this Question
Share on Google+
15 Replies


Rabbit
Expert Mod 10K+
P: 12,366
To update your layout table, you can run an update query.
Oct 12 '12 #2

AdamHope
P: 13
I want to rearrange the layout of the datasheet view, not the table design. Can you elaborate more please?
Oct 12 '12 #3

Rabbit
Expert Mod 10K+
P: 12,366
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.
Oct 12 '12 #4

AdamHope
P: 13
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?
Oct 12 '12 #5

AdamHope
P: 13
Can anyone help please?
Oct 15 '12 #6

Rabbit
Expert Mod 10K+
P: 12,366
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.
Oct 15 '12 #7

AdamHope
P: 13
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.
Attached Files
File Type: zip Layouts.zip (56.8 KB, 114 views)
Oct 15 '12 #8

Rabbit
Expert Mod 10K+
P: 12,366
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.
Expand|Select|Wrap|Line Numbers
  1. 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.
Oct 15 '12 #9

AdamHope
P: 13
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?

Expand|Select|Wrap|Line Numbers
  1. DoCmd.RunSQL "UPDATE layoutTable SET ColumnOrder = "HOW DO I GET THIS VALUE?" WHERE ..."
  2.  
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?
Oct 15 '12 #10

Rabbit
Expert Mod 10K+
P: 12,366
You can retrieve that value with the same property you're using to set it.
Expand|Select|Wrap|Line Numbers
  1. DoCmd.RunSQL "UPDATE layoutTable" & _
  2. " SET ColumnWidth = " & Me.form2.Form!field1.ColumnWidth & _
  3. " WHERE ColumnName = 'field1'" & _
  4. " AND LayoutName = 'my layout'"
Oct 15 '12 #11

AdamHope
P: 13
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub SaveLayout_Click()
  2. On Error Resume Next
  3. Dim LayoutInfo As String
  4. Dim ctl As Control
  5. For Each ctl In Me.form2.Form.Controls
  6. LayoutInfo = Nz(LayoutInfo) + ctl.Name & "," & ctl.ColumnHidden & "," & ctl.ColumnOrder & "," & ctl.ColumnWidth & ";"
  7. Next
  8. Debug.Print LayoutInfo
  9. DoCmd.RunSQL "UPDATE layoutTable SET info =LayoutInfo  where name =cbolayout  "
  10. End Sub
  11.  
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
Oct 15 '12 #12

twinnyfo
Expert Mod 2.5K+
P: 3,284
You could also have a loop that assessed each control and the layout parameters, and save to a table with only a few fields:

Expand|Select|Wrap|Line Numbers
  1. LayoutName  FldName  Hidden  Order Width
  2. MyLayout1   field1   False   1     864
  3. MyLayout1   field2   False   2     2880
  4. MyLayout1   field3   False   3     705
  5. MyLayout1   field4   False   4     864
  6. MyLayout1   field5   False   5     2880
  7. MyLayout1  field6   False   6     630
  8.  
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!
Oct 15 '12 #13

Rabbit
Expert Mod 10K+
P: 12,366
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.
Expand|Select|Wrap|Line Numbers
  1. Do Until rst.EOF
  2.    Me.Controls(rst("FldName")).Properties("ColumnHidden") = rst("Hidden")
  3.    .......
  4.    rst.MoveNext
  5. Loop
Oct 15 '12 #14

AdamHope
P: 13
Thanks very much twinnyfo and Rabbit.

I have cracked it with your help :)

Expand|Select|Wrap|Line Numbers
  1. Do Until Rst.EOF
  2.         With Me.FORM-NAME.Form(Rst("fieldname"))
  3.         .ColumnHidden = Rst!fieldhidden
  4.         .ColumnOrder = Rst!fieldorder
  5.         .ColumnWidth = Rst!fieldwidth
  6.         End With
  7.    Rst.MoveNext
  8. Loop
  9.  
The only problem I have is defining the Field name.

Expand|Select|Wrap|Line Numbers
  1.  
  2. Dim ctl As Control
  3. Rst!FieldName = ctl.Name
  4. Rst!fieldhidden = ctl.ColumnHidden
  5. Rst!fieldwidth = ctl.ColumnWidth
  6. 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

Expand|Select|Wrap|Line Numbers
  1. on error resume next
Is there an easier way to define just the field name without the label?

Thanks
Oct 17 '12 #15

Rabbit
Expert Mod 10K+
P: 12,366
This should exclude labels.
Expand|Select|Wrap|Line Numbers
  1. If ctl.Properties("ControlType") <> 100 Then
  2.    ...
  3. End If
Oct 17 '12 #16

Post your reply

Sign in to post your reply or Sign up for a free account.