Change table field property with vba, add input mask 1

[Eprice]

Hi,
I am doing a major overhaul on a database, deleting old, unused fields and they also want to change the data properties of the zipcode field. The field was formmated as text with a field size of 5. They want it to be a 10 digit field with the dash now (11111-1111). While I am deleting the old fields is there a way I can update the zip code field with the same module? I can't find anything in research about updating the properties and adding an input mask of an existing field. The code I already have is somthing like this:

Dim db As Database
Dim td As TableDef
Set db = CurrentDb

Set td = db.TableDefs("tbl Former_Employees")
td.Fields.Delete "PULAMT"

Also, if I change the data type for the field, the records already in the database do not have the 10 digit zip code until someone goes into the form and updates them? Will this cause a problem?
Thanks for your help
LIsa

 

[vbajock]

Public Sub Resize_And_AddInputMask(byval someTableName as string, byval someZCField as string)

Dim db As DAO.Database
Dim td As DAO.TableDef
Dim fd As DAO.Field
Dim prp As DAO.Property
Dim sql as string

'Increase field size
sql="ALTER TABLE " & someTableName & " ALTER COLUMN " & someZCField &" Text(10) "
Currentproject.Connection.Execute sql

Set db = CurrentDb
Set td = db.TableDefs(someTableName)
Set fd = td.Fields(someZCField)
Set prp = fd.CreateProperty("InputMask", dbText, "00000-9999")
fd.Properties.Append prp

db.TableDefs.Refresh

Set fd = Nothing
Set td = Nothing
Set db = Nothing

End Sub

Test it first, because I didn't

 

[dhookom]

IMO, you shouldn't use Input Mask properties in reports. If you really want to use an Input Mask, set it on the text box on your forms. I never set Input Mask, Caption, Lookup, or Format properties on fields in table design.

I also recommend you keep it as text.

Duane
Hook'D on Access
MS Access MVP

 

[Eprice]

Thanks, that works great.
Lisa

 

[dhookom]

I said "in reports" when I actually meant "in tables".

Duane
Hook'D on Access
MS Access MVP