Hi,

Does anyone out there know how to set the Format property of a column
through code? I found one that sets the Required property to No, so I
know it must be possible.

In case you want to see it, here's my code:

Function CapCallMerge()
Dim cg As New ADOX.Catalog
Dim tb As New ADOX.Table
Dim cn As ADODB.Connection
Dim cl As ADOX.Column
Dim fFormat As Property
Dim CapCall As Field
Set cg.ActiveConnection = CurrentProject.Connection
tb.Name = "CapCallMergeSource"
tb.ParentCatalog = cg

With tb
With .Columns
.Append "Investor Name"
.Append "ID#"
.Append "Title"
.Append "Contact Name"
.Append "Salutation"
.Append "Company"
.Append "Address"
.Append "Address2"
.Append "City"
.Append "State"
.Append "Zip"
.Append "Fund"
.Append "Date Due"
.Append "Capital Call Amount", adCurrency, 30
.Append "Committed Capital", adCurrency, 30
End With
End With
For Each cl In tb.Columns
cl.Attributes = adColNullable
Next cl
cg.Tables.Append tb
End Function

Matt wrote:

> Hi,
>
> Does anyone out there know how to set the Format property of a column
> through code? I found one that sets the Required property to No, so I
> know it must be possible.
>
> In case you want to see it, here's my code:
>
> Function CapCallMerge()
> Dim cg As New ADOX.Catalog
> Dim tb As New ADOX.Table
> Dim cn As ADODB.Connection
> Dim cl As ADOX.Column
> Dim fFormat As Property
> Dim CapCall As Field
> Set cg.ActiveConnection = CurrentProject.Connection
> tb.Name = "CapCallMergeSource"
> tb.ParentCatalog = cg
>
> With tb
> With .Columns
> .Append "Investor Name"
> .Append "ID#"
> .Append "Title"
> .Append "Contact Name"
> .Append "Salutation"
> .Append "Company"
> .Append "Address"
> .Append "Address2"
> .Append "City"
> .Append "State"
> .Append "Zip"
> .Append "Fund"
> .Append "Date Due"
> .Append "Capital Call Amount", adCurrency, 30
> .Append "Committed Capital", adCurrency, 30
> End With
> End With
> For Each cl In tb.Columns
> cl.Attributes = adColNullable
> Next cl
> cg.Tables.Append tb
> End Function

Actually...nevermind setting the formatting property; I just figured
out that it doesn't help me anyway.

My REAL question is why my mail merge from the CapCallMergeSource table
does not preserve the Currency data type on the "Capital Call Amount"
field (displaying it as an unformatted number). I have the merge
automated thusly:

Function CapCallMergeIt()
Dim objWord As Word.Document
Set objWord = GetObject("I:\Investors\ProQuest Investments Capital
Call Letter.doc", "Word.Document")
' Make Word visible.
objWord.Application.Visible = True
' Set the mail merge data source as the database.
With objWord.MailMerge
.MailSubject = "Bank Verification Confirmation"
.MailAddressFieldName = "Email"
End With
objWord.MailMerge.OpenDataSource _
Name:="I:\Investors\Investors.mdb", _
LinkToSource:=True, _
Connection:="TABLE CapCallMergeSource", _
SQLStatement:="SELECT * FROM [CapCallMergeSource]"
' Execute the mail merge.
objWord.MailMerge.Execute
End Function

Any ideas?


Thanks,
Matt

"Matt" <(E-Mail Removed)> wrote in
news:(E-Mail Removed):

> Does anyone out there know how to set the Format property of a column
> through code?

Answer 1): it's in the Properties collection of the Field object, but it
doesn't exist until it has been filled in, so you might have to call the
..Append method first.

Answer 2): there's no logical reason to do so. The Format property is only
used for setting the default for new controls on forms and reports bound to
the field. If your users are creating new forms and reports, then it's easy
enough to update the Format property using the UI. If they don't have
access to the GUI, then they'll never get any benefit from the Format
property.

Hope that helps


Tim F

Tim Ferguson wrote:

> "Matt" <(E-Mail Removed)> wrote in
> news:(E-Mail Removed):
>
> > Does anyone out there know how to set the Format property of a
> > column through code?
>
> Answer 1): it's in the Properties collection of the Field object, but
> it doesn't exist until it has been filled in, so you might have to
> call the .Append method first.
>
> Answer 2): there's no logical reason to do so. The Format property is
> only used for setting the default for new controls on forms and
> reports bound to the field. If your users are creating new forms and
> reports, then it's easy enough to update the Format property using
> the UI. If they don't have access to the GUI, then they'll never get
> any benefit from the Format property.
>
> Hope that helps
>
>
> Tim F


Ah, yes, I see it now

You're right, though, that the formatting doesn't do anything for me
after all. My real problem is a mail merge from the table I am
creating; it will not display my Currency data type as that, preferring
instead for some obnoxious reason or another to output a raw number.
I've posted the specifics in a reply to myself above your response;
perhaps you have some idea of what I can set to make it display the
proper format on the letter.


Thanks,
Matt

Create a query that formats the column as you want it, and use the query,
not the table, to do the merge.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele


"Matt" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Actually...nevermind setting the formatting property; I just figured
> out that it doesn't help me anyway.
>
> My REAL question is why my mail merge from the CapCallMergeSource table
> does not preserve the Currency data type on the "Capital Call Amount"
> field (displaying it as an unformatted number). I have the merge
> automated thusly:
>
> Function CapCallMergeIt()
> Dim objWord As Word.Document
> Set objWord = GetObject("I:\Investors\ProQuest Investments Capital
> Call Letter.doc", "Word.Document")
> ' Make Word visible.
> objWord.Application.Visible = True
> ' Set the mail merge data source as the database.
> With objWord.MailMerge
> .MailSubject = "Bank Verification Confirmation"
> .MailAddressFieldName = "Email"
> End With
> objWord.MailMerge.OpenDataSource _
> Name:="I:\Investors\Investors.mdb", _
> LinkToSource:=True, _
> Connection:="TABLE CapCallMergeSource", _
> SQLStatement:="SELECT * FROM [CapCallMergeSource]"
> ' Execute the mail merge.
> objWord.MailMerge.Execute
> End Function
>
> Any ideas?
>
>
> Thanks,
> Matt
>

"Matt" <(E-Mail Removed)> wrote in
news:(E-Mail Removed):

> ; it will not display my Currency data type as that,

Base the merge on a query (this is always a good idea anyway), so you could
use

SELECT This, That, TheOther,
Format( MyMoneyField, "$0.00") As MyMoneyFieldFormatted
FROM MyTable
etc...


Hope that helps



Tim F

Douglas J. Steele wrote:

> Create a query that formats the column as you want it, and use the
> query, not the table, to do the merge.
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele


Hey, that seems to work even if I don't do any formatting in the query
at all. I don't know why it preserves table formatting if passed
through a query and not the table directly, but thanks


Regards,
Matt