Query Column Width (Newbie) 1

[darrenhubbard]

OK, "stupid question time" as I'm sure this must be really simple:

I've created a query through vba and I just want to specify the width of some of the columns within vba -- and I can't find a method to do it....

Thanks for any help!

TIA,

Darren

 

[Philly44]

Your post doesn not make much sence. Are trying to populate a listbox or make a query in the "Queries" section of you db?

 

[BillPower]

Hi darrenhubbard,

If you post your code and or sql with a bit more detail of what you want to do, I wouldn't mind trying to help out on this one.

Interesting!

Bill

 

[TonyJollans]

Hi darrenhubbard,

You need to set the ColumnWidth property of the Field in the QueryDef. A value of 0 means it's hidden, a value of -1 means use the default; any other value is the width of the column in twips (1440 twips to the inch). As a simple example this sets a width to 2 inches:

Dim tjDb As DAO.Database
Dim tjQry As DAO.QueryDef
Dim tjFld As DAO.Field
Dim tjProp As DAO.Property

Set tjDb = CurrentDb
Set tjQry = tjDb.QueryDefs!

QueryName

Set tjFld = tjQry.Fields!

FieldName

Set tjProp = tjFld.Properties!ColumnWidth

tjProp = 2880

tjDb.Close

Set tjProp = Nothing
Set tjFld = Nothing
Set tjTab = Nothing
Set tjDb = Nothing

Enjoy,
Tony

 

[darrenhubbard]

Cheers, Tony that did it!

I couldn't do it originally as I was missing out the ".Fields" bit.... so now I know!

Cheers,

Darren

 

[ReluctantDataGuy]

Here's a trick I found using Send Keys. After a command that opens a query, add the following:

For X = 1 To qryDef.Fields.Count
SendKeys "%oc+{TAB}{ENTER}{RIGHT}", True
Next X

SendKeys "{UP}{F2}{HOME}", True

Note that this code counts on the Format, and ColumnWidths menu picks being available - so if you use a home made menu, add this before the above code:

Application.MenuBar = ""

Then add this below the SendKeys code:

Application.MenuBar = "<your menu name>"

It works, but its ugly. If anyone knows how to do it in code without using SendKeys, I'd really appreciate the help.

Thanks!
ReluctantDataGuy