How can I centre-align a field using VBA?

[desmond]

Does anyone know how to centre-align a field in a table using VBA?

 

[Galaxiom]

You seem to be posting a series of questions about changing the display of fields in tables. The formatting is tables does not matter since the user should only see data presented in forms and reports. Next thing you will be asking how to Conditionally Format a field in a table.

You are on the wrong track. Tables are for storing data not displaying it. There are many things that can be done in forms and reports that simply cannot be done by directly displaying a table. There are no Event procedures for a table and fields changes cannot be controlled.

You probably think you want the property of the field called TextAlign. It is likely to be represented by a number between zero and four. But I am not going to tell you how to address this property unless you can satisfactorily explain why you would need it.

P.S. Conditional Formatting in a table is impossible.

 

[boblarson]

And I'll throw my support behind Galaxiom's answer.

 

[desmond]

Well, I load values into a table dynamically by a recordset, then I have to choose which values I want to display in a report.

So I use the table to store all possible values that can go in a report, click on the fields I want in a report (maybe 10 from several hundred), then they get loaded to a report for viewing.

The report is already formatted, it'd just be nice to have one of the column's in the 'interim' table centred.

Is there a more efficient way of doing this?

 

[boblarson]

There is VERY LITTLE you can do around formatting in a table. Centering is not one of them.

 

[ByteMyzer]

Hello, desmond,

Let's see if we can't clarify this simply and explicitly:

Tables are for data storage.
Queries are for querying the stored data and returning the results.
Forms are for viewing/editing the stored data.
Reports are for displaying/printing the data with an appropriately formatted layout.

NOTE: the data sources for Forms/Reports can be Tables or Queries.

 

[Galaxiom]

There is VERY LITTLE you can do around formatting in a table. Centering is not one of them.

No so in A2007 at least.
See the Text Align property of the field.
Values: General, Left Center, Right, Distribute.

 

[boblarson]

Yes, you are right - in 2007 there is a text align property in the table. I had not opened 2007 and was basing my answer wrongly off of 2003.

 

[Galaxiom]

So I use the table to store all possible values that can go in a report, click on the fields I want in a report (maybe 10 from several hundred), then they get loaded to a report for viewing.

How do you get several hundred fields in a table? Access can only manage 255 fields. Such a large number of fields also suggests the data structure may not be ideal.

If you are recreating actual tables rather than just working with a recordset you will eventually run out table object indexes. I remember seeing somewhere the limit on the number of tables in the life of a database is in the 700's.

You are just displaying a recordset. Why a make a table or even a query? Regardless of the record source the data should be displayed in a form where you will have the capacity to format it however you like.

 

[Galaxiom]

Yes, you are right - in 2007 there is a text align property in the table. I had not opened 2007 and was basing my answer wrongly off of 2003.

Just another way (along with Table level lookups) that Microsoft is encouraging users to work with tables. They haven't added Conditional Formatting to tables for Access 2010 have they?

 

[boblarson]

Just another way (along with Table level lookups) that Microsoft is encouraging users to work with tables. They haven't added Conditional Formatting to tables for Access 2010 have they?

no but there are calculated fields and data macros.

 

[desmond]

How do you get several hundred fields in a table? Access can only manage 255 fields. Such a large number of fields also suggests the data structure may not be ideal.

If you are recreating actual tables rather than just working with a recordset you will eventually run out table object indexes. I remember seeing somewhere the limit on the number of tables in the life of a database is in the 700's.

You are just displaying a recordset. Why a make a table or even a query? Regardless of the record source the data should be displayed in a form where you will have the capacity to format it however you like.

I have a huge (text) file of raw data, that is over 4000 bytes long for each record. This needs to be mapped dynamically to a field-name depending on which file is read. Because access will not allow records > 2k to be loaded, I have to provide an 'interim' database that allows the user to select which "fields" (from the mapping of the text file) so that the limit is not exceeded.

Once the field selection is complete, then the data (text file) is read, mapped to the offset (based on details from the interim db), and displayed in another table. Queries and reports then run off this table. Of course the 255 limit also applies (and is catered for).

 

[desmond]

Just incase anyone did want to know how this is done without the vba police questioning why you want to do it, it's very simple:

Properties("TextAlign") = 2