Column Name, size, order, hidden etc.

[Eightball3]

I was wondering if someone can help me with this. I have a datasheet that allows the user to hide, sort, move etc. each column. I am attempting to generate a report dynamically based of this user customized datasheet. I am trying to figure out how to read or loop through the form controls to determine the column order, whether hidden or not, size etc. I then want to use that information to generate the report. I'm no expert with VB but I know experts are here! Can anyone direct me to an example? Thank you!

 

[boblarson]

I don't know if this will help (and I certainly don't know about getting the column order but perhaps).

 

[Eightball3]

I don't know if this will help (and I certainly don't know about getting the column order but perhaps).

Thanks for the reply Bob,

Yes I ran accross that article during my search as well. It seems to be dealing with setting column conditions. What I'm after is just reading them so I can recreate them using controls on a report.

 

[boblarson]

For your column order, try something like this:

Function GetColOrder(frm As Form) As String
Dim strColOrder As String
Dim ctl As Control

For Each ctl In frm.Controls
    If ctl.ControlType <> acLabel Then
    strColOrder = strColOrder & ctl.ColumnOrder & " " & ctl.Name & ","
    End If
Next

GetColOrder = strColOrder

MsgBox strColOrder

End Function

That will get the output for you and you just have to be able to sort it into the right numbering. ( I don't have something for that right now).

 

[vbaInet]

Idea:

Using Bob's method you will get the column order of each control (that supports the property). I would be inclined to be explicit as to what controls you want to get the order of instead of using "<> acLabel" because you may have other controls (like a box for example) that don't support it.

You could also save the Order and the control name (or field name even better) in a Dictionary Object (for easy and sequential retrieval).

Lay out all the controls on the report. Use only text boxes. Name the textboxes txtbox1, txtbox2, txtbox3 etc... do the same for the associated labels. When it comes to changing the order on the report you would start from 1 and change txtbox1's record source to the field name linked to 1 in the Dictionary or array. Count how many items are in the Dictionary and from that you will know how many controls to hide.

 

[boblarson]

Idea:

Using Bob's method you will get the column order of each control (that supports the property). I would be inclined to be explicit as to what controls you want to get the order of instead of using "<> acLabel" because you may have other controls (like a box for example) that don't support it.

Nope, you won't have any other controls on a DATASHEET.

You could also save the Order and the control name (or field name even better) in a Dictionary Object (for easy and sequential retrieval).

Nope, the OP said the user might move the columns around so we need to get the EXPLICIT order at runtime.

 

[boblarson]

NOTE - the code I provided works ONLY on a DATASHEET, any other viewing type and you will get an error when using .ColumnOrder.

 

[vbaInet]

Nope, you won't have any other controls on a DATASHEET.

True, but For Each Controls is not going to loop through only visible controls (i.e. visible on a datasheet). It loops through all. A box can be visible in design view but not visible (for obvious reasons) on Datasheet view.

Nope, the OP said the user might move the columns around so we need to get the EXPLICIT order at runtime.

In my last paragraph I mentioned how to set the order by retrieving (starting from 1) the field name from the dictionary. You open the report in design view, change the record source of each text box based on what is in the dictionary.

 

[boblarson]

True, but For Each Controls is not going to loop through only visible controls (i.e. visible on a datasheet). It loops through all. A box can be visible in design view but not visible (for obvious reasons) on Datasheet view.

When in DATASHEET view, it does not get the other controls, believe it or not. I have several other controls (including a button) on my test form and it works just fine the way it is. But you could specify others to avoid.

In my last paragraph I mentioned how to set the order by retrieving (starting from 1) the field name from the dictionary. You open the report in design view, change the record source of each text box based on what is in the dictionary.

But this doesn't make sense as the OP WANTS to set it in the order the user has set it (dynamically) not by some static method in a dictionary. So, your method is not what they asked for (you might read the first post over again) as they explicitly stated:

I am attempting to generate a report dynamically based of this user customized datasheet

They WANT to be able to generate the report in with the columns in the order that the user has specified by the moving around of them. But I guess we'll wait and let them chime in and see which of us read it the way they intended, eh.

 

[boblarson]

When in DATASHEET view, it does not get the other controls, believe it or not. I have several other controls (including a button) on my test form and it works just fine the way it is. But you could specify others to avoid.

Okay, I messed up on that one - I just went back and tested and I had no other controls on that particular form. So I added some and so you would want to check by using:

Select Case ctl.ControlType
  Case acTextBox, acComboBox, acListBox, acOptionGroup, acCheckBox, acOptionButton
     'do the control stuff here
End Select

 

[Eightball3]

Thanks for the suggestions Bob and vbaInet.

Like I posted earlier, I'm not that great with vba. I was originally thinking like vbaInet when it comes to the report structure. Label1 text1, label2 text2, etc. Once I had the info from the datasheet, I would apply it to the controls on the report. The end goal it to have a report that looks like the datasheet.

Question (my ignorance):
Since the datasheet will have a command button on it giving the user the option to print the report, and the datasheet will be open when the report is generated and previewed, why couldn't you just reference each control on the datasheet then apply it to the control on the report? Would there be a need to store the control data?

 

[boblarson]

Would there be a need to store the control data?

If you are going to display the report in a standard format, there's no need for any of that just use a standard query.

If you want the format of the report to be based on the user's current view of the datasheet then you will need to use code like what I showed (although sorted somehow - I'm not good at doing up a coded sort, but someone can help with that - probably vbaInet can).

 

[vbaInet]

Let me explain what I'm talking about with respect "changing the order". You wouldn't be changing the order per say, you will be changing the Control Source which inadvertently changes the order. Which is why I mentioned using text boxes and ensuring you have that as 1,2,3 etc.

Say you have three controls with order like:

CustomerID: 1
Surname: 2
Fornename: 3

User changes the order to:

CustomerID: 3
Surname: 1
Fornename: 2

You loop through and save the order as key in Dictionary plus the field name as Item. Open the report in design view, loop through the Dictionary:

For i = 1 to myDictionary.count
    reports("").controls("txtbox" & i).controlsource = mydictionary.item(i)
next

Then hide the other controls:

For i = mydictionary.count + 1 to 10
    reports("").controls("txtbox" & i).visible = false
next

 

[vbaInet]

A sorted code is possible maybe using QuickSort, HeapSort or BubbleSort algorithm but with the dictionary you wouldn't need to sort it because the key is a number so you just loop through.

 

[Eightball3]

There are 27 textbox controls on my subform each having a label. By default OnOpen there are 12 visible and the order is set. The user may re-order the columns, hide/unhide or size any of the columns.

Isn't it possible to have a report with 27 textbox controls and 27 labels and dynamically set the label caption, textbox name/recordsource, width of the textbox (overall and from the left) for each control on the report with the intention of duplicating the datasheet?

Thanks for the comments and help guys!!

 

[vbaInet]

I think we understood what you wanted which is why Bob posted a code for getting the column order (post #4), then I gave you some code and ideas for saving the order into a Dictionary Object and using that to change the control source based on the order (post #13). This isn't a 1 minute job so you should be prepared to doing lots of Googling. Here's what I found regarding using a Dictionary:

http://www.kamath.com/tutorials/tut009_dictionary.asp

 

[Eightball3]

Function GetColOrder(frm As Form) As String
Dim strColOrder As String
Dim ctl As Control

For Each ctl In frm.Controls
If ctl.ControlType <> acLabel Then
strColOrder = strColOrder & ctl.ColumnOrder & " " & ctl.Name & ","
End If
Next

GetColOrder = strColOrder

MsgBox strColOrder

End Function

Thanks for the help on this guys, I really appreciate it!

Did I mention I'm not too good at vba?

So for the above code that Bob posted, where do I put this? It looks like it provides the control order, and name for all controls except labels. I am also thinking that it does it for the active form or do I have to replace the 'frm' with my form name? It looks like this creates a string 'strColOrder' that is built by adding the control column order and the control name and then repeating for the next contol until all controls have been done. After that is assigns this string to GetColOrder (I'm not sure I understand this). Then it displays the strColOrder in a message box.

So in the end I end up with a string that has the order and name of each control?

Am I understanding this? Thanks a lot for the help

 

[vbaInet]

In my honest opinion you would spend a long time trying to figure what is going on. What you're trying to achieve requires expert coding experience and you have two obstacles to overcome, Column Ordering and Resizing. What has been proposed is JUST column ordering which is easier than Resizing/Alignment of controls.

If you haven't got intermediate vba skills then I would advice that you learn the basics first before taking the plunge.

 

[Eightball3]

In my honest opinion you would spend a long time trying to figure what is going on. What you're trying to achieve requires expert coding experience and you have two obstacles to overcome, Column Ordering and Resizing. What has been proposed is JUST column ordering which is easier than Resizing/Alignment of controls.

If you haven't got intermediate vba skills then I would advice that you learn the basics first before taking the plunge.

First off let me say I appreciate the efforts people give on these forums. Without that I wouldn't have learned what I know now.

Intermediate skills?? I don't know. If one doesn't know the entire realm of a topic how could they know at what part of the scale to place their knowledge level. I understand certain parts of Access well and others not at all. I always seem to find ways around the areas I don't understand. There are many ways to get to the same point.

I'm attaching a database that I threw together yesterday in my attempt to accomplish my task. It only consists of two forms, one report, and a table. I seem to have everything working with the exception of the ordering. You can hide, resize, filter, sort, and all of this is captured on the report. It seems to be working relatively well. Yes it would need dressing up, but the concept is working.

I've added a button on the main form (ATestFormA). What I would like is the code to go behind the button that would look at the datasheet and put in the textbox the name of the first column, either Text1, Text2, or Text3.

Thanks again for any assistance!!

 

[vbaInet]

Good job!! But with your style you're not going to be able to get the column ordering or resizing (with ease). Your code would also start to suffer when more than three controls are added. Which is why we had suggested ways to achieve the ordering by looping through all the controls, storing the order, opening the report in design view, changing the Control source based on the order then resize the controls.