Change Input mask in code

[fmm]

I have a model db (Access front end, sql back, linked tables). I need to be able to change input masks at the table level in code.

Any ideas?


Thanks!

 

[MarkK]

Why at the table level? If it'll be changing on the fly, I'd consider leaving the table free of input masks and assigning them to controls as required. But that sort of defeats a prime reason for using an input mask, is consistent formatting of a particular field.
What are you actually trying to do?

 

[Haroundb]

I don't think it would be possible, and besides doing this in code will be very complicated and may case lots of errors! can you tell us what you want to do? I think this would better help to understand your point of view. I don't think changing the mask in code is something really serious, the mask is one of the things that shouldn't be changed requently in Access.

 

[fmm]

It won't cause any errors, because it's only done at the time a db is setup.

Here's what I'm trying to do. In the next 3 months, I will have to roll out approx. 600 databases, all with Access front ends and sql back ends. They all start with the same tables and Access objects.

To set up a new db, I create the db on the sql server. I run an sql script that sets up all of my keys, indexes, roels, and permissions. Then I create a dsn to the db, and a new Access db. I copy the template Access db's objects to the new db, and using the lonked table manager I change the db that the linked tables point to. I change 2 project specific lines of code on my splash form and everything in the new Access db points to the right place.

The problem is that when I copy the table objects from the template Access db, the input masks don't make the trip. Since each db gets additional forms (many of which are not created by me) there is no guarentee that these forms will be built with the input masks in place. Putting them in at the table level insures that they will be present in all forms created after they've been placed in the table.

These masks do not change once I'm finished with setup.

An obvious extension to this would be to create code that would loop through the fields in a table and apply an input mask to and field name ending with 'date'.

While I have never had to do this, I assume that Access can create tables in code. I find it difficult to beleive that I can't modify the input mask properties in code as well.

 

[Bat17]

here is some code that I got from somewhere, not exactly what you are after but it may point you in the right direction.
>>>>
You must use the DAO Properties collection of the Field object. A property such as Format does not exist a priori, if it has never been set before, you must first create it, and append it to the Properties collection. You can use the following function for this purpose:

Sub SetFieldProperty(fld As DAO.Field, strName As String, intType As DAO.DataTypeEnum, varValue As Variant)
On Error GoTo ErrHandler

fld.Properties(strName) = varValue
Exit Sub

ErrHandler:
If Err = 3270 Then
' Property does not exist
fld.Properties.Append fld.CreateProperty(strName, intType, varValue)
Resume Next
Else
MsgBox Err.Description, vbExclamation
End If
End Sub

Apply it like this in your code (in the For Each tfld In tdf.Fields loop):

SetFieldProperty tfld, "Format", dbText, ">"
SetFieldProperty tfld, "InputMask", dbText, ">CCCC;0;_"

 

[fmm]

Thank you, I will give this a shot.

 

[fmm]

Thanks for the code, but I'm stuck again.

I haven't worked at all with DAO (but I'm reasearching it over lunch).

The calling code is giving me problems.

What I'd like to do is select a table by name, and then set the properties on a subset of the fields, so that (for example) for an address table I'd have the following:

'for the Address1 field
SetFieldProperty tfld, dbText, ">"

'for the DateAdded field
SetFieldProperty tfld, dbText, "99/99/0000;0;_"

It's the tfld I'm having trouble setting up correctly.

btw: at present, I plan to individually set the tables and fields I want set. I won't be looping through the collection.


Thanks in advance!

 

[Bat17]

I hope the DAO did not spoil your lunch

see if this helps, I have not added any error checking to it though!

Sub SetProperties()
Dim fld As DAO.Field
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Set dbs = CurrentDb
Set tdf = dbs.TableDefs("YourTableName")
Set fld = tdf.Fields("DateAdded")
fld.Properties.Append fld.CreateProperty("InputMask", dbText, "99/99/0000;0;_")
Set fld = tdf.Fields("Address1")
fld.Properties.Append fld.CreateProperty("InputMask", dbText, ">")
Set fld = Nothing
Set tdf = Nothing
Set dbs = Nothing
End Sub

 

[fmm]

I actually figured it out last night, but your method is much cleaner.

Thanks for your help!

fmm