Access Left Justify Table Column

I'm trying to left justify all columns in an Access table using VBA. Can you provide sample code to accomplish. Thanks
shieldscoAsked:
Who is Participating?
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I completely agree with Nick - there is simply no need to set formatting options on a table, since tables should never really be shown to the user.

That said, the code below will set all columns in all non-system tables to be Left Aligned:

    Dim dbs    As DAO.Database
    Dim tdf    As DAO.TableDef

    Set dbs = CurrentDb

    Dim prp    As DAO.Property
    Dim fld    As DAO.Field

    For Each tdf In CurrentDb.TableDefs
        If Left(tdf.Name, 4) <> "msys" Then
            For Each fld In tdf.Fields
                For Each prp In fld.Properties
                    If prp.Name = "TextAlign" Then
                        '/ 1=Left, 2=Center, 3=Right
                        prp.Value = 1
                    End If
                Next
            Next
        End If
    Next tdf

Open in new window

0
 
Nick67Commented:
You'll need to post a lot more detail than that
I'm trying to left justify all columns in an Access table using VBA.
You shouldn't be gussying up the presentation of a Table at all.
Table objects are for storing data, and something that users in general should never see or use.

Table Objects also have absolutely no VBA available to them at all and no events, so that too is a non-sequitur.
A better description of what you are doing and why, and a sample .mdb and a screenshot of the desired outcome go a long way to getting a good Answer
0
 
shieldscoAuthor Commented:
Its a simple question - can you provide code to left justify all fields in an access table? Please respond to the question or allow someone else to offer a solution to the question. I understand that people do not know how to do all things. No disrespect intended.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
shieldscoAuthor Commented:
Can any provide code to change the TableDefs to left justify fields in an Access table????
0
 
Nick67Commented:
Please respond to the question
I did.
That cannot and should not be done to a table.
Creating formatted HTML tables?
Datasheet views on a form?
All the controls on a report section.
Yeah, yeah.
All that is both doable and operationally sensible.

So I asked you to clarify what you want to do and why.
You appear to really want to apply formatting to a table.
Beside the fact that you shouldn't do that, VBA offers you no tools to do that.
Neither does the GUI.
Open a table.
There is only a very limited selection of text formatting tools available.
That is by design.
Tables aren't meant for presentation, data entry or printing, and have only rudimentary capabilities in that regard as a result

I understand that people do not know how to do all things. No disrespect intended.
At this point, about the only thing I can't get Access to do is the impossible.
0
 
shieldscoAuthor Commented:
The partial code to change default values in a table is below:
CurrentDb.TableDefs("TableName").Fields("FieldName").DefaultValue = ...

 can you at least provide the defaulvalue for left justify?
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
A Default Value is the value inserted into that column when a new record is added, and would have no bearing on text formatting.
0
 
shieldscoAuthor Commented:
Works great... Thanks
0
 
Nick67Commented:
It may not so simple as a default value.
The Field Properties collection is one of the more interesting constructions that exists.
It is sparsely populated.
Some Properties exist by default.
Perhaps TextAlign will be one of them
Others do not exist until you create them, or the system creates them.
Only documented values can be created with some of these types of sparsely populated collections.
The Database Properties collection will actually let you create your own Properties.
That can be nice when you want to flange in some of the things that have been added into new versions of Access for use in older versions.

To do these kinds of things, you take code like what Scott posted and throw in code to set a Boolean to check whether a particular property exists or not.  If it exists, you set a value, if not, you create it and then set a value.  It is very likely that Access creates the TextAlign property and sets it at the time the field is created.

Whether that default Access sets when it creates textAlign is accessible and/or amenable to alteration is anybody's guess.  Primarily because nobody should really care about the formatting of a table, I don't know if anyone has poked that particular bear to find out.

So, likely for each field you create, you'll have to run the code afterward.
And you, and your successors will have to remember why and how you've done so.
Fill yer boots!
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
The TextAlign property exists for all Field objects - but I do agree that for the most part, it's a good idea to check for a property. It's not listed in the MSDN literature, but as a check I created a new table in 2013, saved it (no data, just structure) and then looped through the properties to insure the TextAlign property was present for each Field Type (Text, Numeric, Date, etc). It was, even for Attachment, Boolean and Hyperlink fields.

To do these kinds of things, you take code like what Scott posted and throw in code to set a Boolean to check whether a particular property exists or not.
Or you could use the code exactly as I provided, which includes an If check to determine whether the property exists.

Whether that default Access sets when it creates textAlign is accessible and/or amenable to alteration is anybody's guess.
No guesswork needed - the code I show above proves it can be done. To make sure, I created a new table in code, added fields in code, inserted a few records in code, and set the TextAlign property in code (nothing in the interface, everything with DAO and SQL). I then opened the table in Access, and the columns were left-aligned, as I expected them to be.
0
 
Nick67Commented:
Whether that default Access sets when it creates textAlign is accessible and/or amenable to alteration is anybody's guess.

By that I meant, can you instruct Access that from now on you'd like all fields to TextAlign left?
You can certainly change them after the fields are created -- I didn't know you could get after that, and after all, why would you? -- but can you globally alter the default so that you do not need to ever again alter any
TableDefs("SomeTable").Fields("SomeField").Properties("TextAlign") to equal 1?

Can you get to and set the global default for all new fields:
TableDefs("AnyTable").Fields("ANewField").Properties("TextAlign")  which is now 3 and instead make it 1?
Or can you only change it after creating a new field?
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
AFAIK you can't do that. I'm not certain how Access creates new objects and such, but I'd assume it's at the code level, and the only way to modify those defaults would be to modify that code (and of course we can't do that).

You can create a "blank" database, per Allen Browne, and Access will supposedly use that for new, blank databases:

http://allenbrowne.com/ser-43.html

See the bottom of the page. However, I don't think you have much control over a NEW table created in that database.
0
 
Nick67Commented:
It's been quite a while since I played with the field properties collection.
The big use was to turn off subdatasheets in all fields of all tables.
I wouldn't have wanted to do that by hand.
Here's the stub of that code, which now just msgbox's which ones haven't been whacked.
When it ran originally, it turned them all off, but I altered the code afterward.

Function ReportSubDataSh()
On Error Resume Next
    Dim db As dao.Database
    Dim tdf As dao.TableDef
    Dim prp As dao.Property
    Const conPropName = "SubdatasheetName"
    Const conPropValue = "[None]"
    
    Set db = CurrentDb
    For Each tdf In db.TableDefs
        If (tdf.Attributes And dbSystemObject) = 0 Then
            If tdf.Connect = vbNullString And Asc(tdf.Name) <> 126 Then 'Not attached, or temp.
                    If tdf.Properties(conPropName) <> conPropValue Then
                        MsgBox tdf.Name
                    End If
            End If
        End If
    Next
    
    Set prp = Nothing
    Set tdf = Nothing
    Set db = Nothing
End Function

Open in new window

0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Still, that won't do anything for a NEW table, just as the code I provided would not do anything for a NEW Table or Field. It just alters properties after Access has created the Table.
0
 
Nick67Commented:
Agreed.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.