VB Code for organizing columns in query

M

Matt

I have a subform, which is from a query in datasheet view. I have a few
columns that I wish to organize differently each time a user selects new
criteria. Is there code that will move Column 1 to where Column 3 is, and
Column 3 to where column 1 was? I know you can move the column by the drag
and drop method, however I would like to automate this.

Thanks,
Matt
 
B

Brendan Reynolds

You can do this using the ColumnOrder property. Here's an example using the
Orders form from Northwind ...

Private Sub Command66_Click()

If Me.Orders_Subform.Form.Controls("ProductID").ColumnOrder = 1 Then
Me.Orders_Subform.Form.Controls("ProductID").ColumnOrder = 2
Me.Orders_Subform.Form.Controls("UnitPrice").ColumnOrder = 1
Else
Me.Orders_Subform.Form.Controls("ProductID").ColumnOrder = 1
Me.Orders_Subform.Form.Controls("UnitPrice").ColumnOrder = 2
End If

End Sub

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.
 
M

Matt

This code is going to work perfect, however i have one syntax problem that I
cannot figure out. The code is needed for a subform within another subform.
I tried using the code below, am I close?

Me.Orders_Subform.SubDatasheet_Subfrom.Form.Controls("ProductID").ColumnOrder = 2
 
B

Brendan Reynolds

Not 100% sure, but you may need to specify the Form property of the first
subform control ...

Me.Orders_Subform.Form.SubDatasheet_Subfrom.Form.Controls("ProductID").ColumnOrder
= 2

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.
 
Top