Changing a table's field format

[cdavisdccb]

I originally posted this thread on the VBA code forum. I thought by reposting it here, I may get some more help.
Thanks.......

I've created a form to allow certain users to add a new field/column to an existing table.
The following code is in the VB portion of the form.

Dim strNEWFLD As String
DoCmd.RunSQL "ALTER TABLE [tblProduct]" & _
"ADD [" & strNEWFLD & "] NUMERIC NULL"

The VB code looked like this(which I'm not currently using):

Dim dbs As Database, tdf As TableDef, fld As Field
Dim strNEWFLD As String
Set dbs = CurrentDb.
Set tdf = dbs."tblProduct"
Set fld = tdf.CreateField(" & strNEWFLD & ", dbNumeric,10)
tdf.Fields.Append fld
dbs.TableDefs.Append tdf
Set dbs = Nothing

This sets the field's data type to Number. Problem is that the field's FORMAT should also be set to percentage. Is there a way to do this using VB or SQL?

I'd like the table to store the data in percent that way I dont have to modify all of my reports to convert it.

I've asked the Microsoft "PaperClip" to death!!! I've tried several different approaches to this in both VB and SQL.

Any help will be appriciated.

Thanks,
cd

"It doesn't matter how idiot-proof you make it, someone makes a better idiot!"

 

[beetee]

I just got an interrupt, and can revisit this in more detail later if you need:

1) Check out all the parameters to the CreateField function. Perhaps there's a format there. If not...

2) Check out the field 'properties'
e.g.
dim curprop as property
dim TheField as field
for each curprop in TheField.Properties
debug.print curprop.name & " = " & curprop.value
next curprop

Create a sample field with the format you desire.

See if that format is in the field's properties.

If this works, you can add a property by:

Dim MyProp As Property
Dim FieldType
Dim PropValue
Set MyProp = TheField.CreateProperty("name", FieldType, PropValue)
TheField.Properties.Append MyProp


You can do lots of nice things on many types of Access objects using the properties collection.

Anyway, if you want more details I should be able to oblige later.