473,320 Members | 2,029 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Create a Save Layout Button for Datasheet Subform

AdamHope
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

15 5098
Rabbit
12,516 Expert Mod 8TB
To update your layout table, you can run an update query.
Oct 12 '12 #2
I want to rearrange the layout of the datasheet view, not the table design. Can you elaborate more please?
Oct 12 '12 #3
Rabbit
12,516 Expert Mod 8TB
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
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
Can anyone help please?
Oct 15 '12 #6
Rabbit
12,516 Expert Mod 8TB
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
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, 156 views)
Oct 15 '12 #8
Rabbit
12,516 Expert Mod 8TB
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
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
12,516 Expert Mod 8TB
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
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
3,653 Expert Mod 2GB
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
12,516 Expert Mod 8TB
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
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
12,516 Expert Mod 8TB
This should exclude labels.
Expand|Select|Wrap|Line Numbers
  1. If ctl.Properties("ControlType") <> 100 Then
  2.    ...
  3. End If
Oct 17 '12 #16

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

Similar topics

3
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...
10
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.
3
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...
4
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...
1
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...
0
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...
4
WyvsEyeView
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...
3
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...
7
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...
1
Daniel B
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...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
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...
0
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...
0
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...
0
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...
1
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)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
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
0
isladogs
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...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.