Change Access field format using vba 2

[bistec]

Hi

Is it possible to change the format of a field via vba?

I'm adding a field to a remote backend db using the following;

<snip>
Function AddNewField()
Dim db As DAO.Database

Set db = OpenDatabase(&quot;D:\nbm\smdata\smdata.mdb&quot
db.Execute &quot;ALTER TABLE jobs ADD COLUMN RecallCheck YESNO;&quot;
db.Execute &quot;UPDATE jobs SET RecallCheck = 'No' ;&quot;
db.Close
Set db = Nothing

End Function
</snip>

This all works accept that the field has no default format and so uses -1/0 as values. I would to change the format so that it shows a checkbox instead.

Haven't been able to find anything on doing so.

Howard

 

[BillPower]

Hi, this should do it for you:

Dim db As Database, tdf As DAO.TableDef, fld As DAO.Field
Dim prp As DAO.Property
On Error Resume Next
Set db = OpenDatabase(&quot;D:\nbm\smdata\smdata.mdb&quot
Set tdf = db.TableDefs!jobs
db.Execute &quot;ALTER TABLE jobs ADD COLUMN RecallCheck YesNo;&quot;
db.Execute &quot;UPDATE jobs SET RecallCheck = 'No' ;&quot;

Set fld = tdf.Fields!RecallCheck
Set prp = fld.CreateProperty(&quot;Format&quot;, dbText, &quot;Yes/No&quot
fld.Properties.Append prp
Set prp = fld.CreateProperty(&quot;DisplayControl&quot;, dbInteger, acCheckBox)
fld.Properties.Append prp

db.Close
Set db = Nothing

Bill

 

[BillPower]

Hi again,

Just remembered thread705-551230, how did you get on with it. Took some time putting that together, would be nice to know!!!

Bill

 

[bistec]

Gooday Bill

How are ya? Thanks for that bit of code, I'll give it a go.

I've been fairly busy learning MySQL/PHP and the like so I haven't been on the forum for a while. Yes, the backend code worked a treat, thanks for that. A few stars are in order ;-)

cheers

Howard