Setting Format Property

  • Thread starter Thread starter Matt
  • Start date Start date
M

Matt

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 said:
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
Click to expand...

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
 
Does anyone out there know how to set the Format property of a column
through code?
Click to expand...

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 said:
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
Click to expand...


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.
 
; it will not display my Currency data type as that,
Click to expand...

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 said:
Create a query that formats the column as you want it, and use the
query, not the table, to do the merge.
Click to expand...


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
 
Back
Top