By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,689 Members | 1,731 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,689 IT Pros & Developers. It's quick & easy.

Change Input mask in code

P: n/a
I have a db with 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!
Nov 13 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a
Michael Miller,
Input masks are part of the UI for Access and not commonly supported by
client-server database management systems. You could try writing a stored
procedure in SQL Server to do what you want.
--
Alan Webb
kn*******@SPAMhotmail.com
"It's not IT, it's IS
"F. Michael Miller" <fm***@netzero.net> wrote in message
news:11*************@corp.supernews.com...
I have a db with 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!

Nov 13 '05 #2

P: n/a
I realize that. That's what I am trying to change.

For example, I store a phone # in the sql table as a 10 digit string. I
want to be able to manipulate the input mask property on the linked table in
Access. This is purely an Access issue.
"Alan Webb" <kn*******@hotSPAMmail.com> wrote in message
news:GJ********************@comcast.com...
Michael Miller,
Input masks are part of the UI for Access and not commonly supported by
client-server database management systems. You could try writing a stored
procedure in SQL Server to do what you want.
--
Alan Webb
kn*******@SPAMhotmail.com
"It's not IT, it's IS
"F. Michael Miller" <fm***@netzero.net> wrote in message
news:11*************@corp.supernews.com...
I have a db with 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!


Nov 13 '05 #3

P: n/a

"F. Michael Miller" <fm***@netzero.net> wrote in message
news:11*************@corp.supernews.com...
I realize that. That's what I am trying to change.

For example, I store a phone # in the sql table as a 10 digit string. I
want to be able to manipulate the input mask property on the linked table in Access. This is purely an Access issue.


It does not sound "purely an Access issue" to me, if you want to manipulate
the Input Mask at the table level, and the server DB does not support Input
Masks at the table level. Consider using the Input Mask property of the
Control where you display/edit the data, if you want it to be purely an
Access issue.

That said, Input Masks are rather inflexible and my users have always
thought they were a hindrance rather than a help -- we used them, primarily,
to satisfy Y2K standards, and otherwise handled the needed functionality in
validation rules and validation code. YMMV.

Larry Linson
Microsoft Access MVP
Nov 13 '05 #4

P: n/a
Access already allows me to set an input mask at the table level on a linked
sql table, independent of the server db. Access just changes how the data
is displayed.

I need to do this at the table level so that I don't ahve to worry about how
the data is displayed on any form that might be created. I'm trying to
build a generic db that I can copy, and then just change the table
references from the model to the actual sql db. Unfortunately, the input
masks don't make the trip. If I can't change the input masks at the table
level, I need to do it on each form.

"Larry Linson" <bo*****@localhost.not> wrote in message
news:tjdge.23173$hh6.2364@trnddc01...

"F. Michael Miller" <fm***@netzero.net> wrote in message
news:11*************@corp.supernews.com...
I realize that. That's what I am trying to change.

For example, I store a phone # in the sql table as a 10 digit string. I
want to be able to manipulate the input mask property on the linked table

in
Access. This is purely an Access issue.


It does not sound "purely an Access issue" to me, if you want to
manipulate
the Input Mask at the table level, and the server DB does not support
Input
Masks at the table level. Consider using the Input Mask property of the
Control where you display/edit the data, if you want it to be purely an
Access issue.

That said, Input Masks are rather inflexible and my users have always
thought they were a hindrance rather than a help -- we used them,
primarily,
to satisfy Y2K standards, and otherwise handled the needed functionality
in
validation rules and validation code. YMMV.

Larry Linson
Microsoft Access MVP

Nov 13 '05 #5

P: n/a
F. Michael Miller,
If you had a check constraint which refused to insert rows because a value
in the row didn't match an input mask and the process attempting to insert a
row was code and not a human then any row that violated the constraint would
not make it into the table. Now the problem is you have less data in the
destination than you should and there has to be a follow-on bit of work to
evaluate the rows that didn't insert and decide what to do. All in a day's
work for a DBA but I for one would rather have crappy data I can analyze and
address what's crappy than less than expected or no data in my destination.
For example, if only some of the line items on an invoice make it into my
table to calculate commissions for variable pay then a bunch of my sales
people are going to have smaller than expected paychecks and I will be
famous in way's I'd rather avoid.

Access bends the rules and allows you to do things that other dbms software
doesn't support. As I suggested in another post, if you must have this then
a check constraint tied to a stored procedure is your best option.
--
Alan Webb
kn*******@SPAMhotmail.com
"It's not IT, it's IS"

"F. Michael Miller" <fm***@netzero.net> wrote in message
news:11*************@corp.supernews.com...
Access already allows me to set an input mask at the table level on a
linked sql table, independent of the server db. Access just changes how
the data is displayed.

I need to do this at the table level so that I don't ahve to worry about
how the data is displayed on any form that might be created. I'm trying
to build a generic db that I can copy, and then just change the table
references from the model to the actual sql db. Unfortunately, the input
masks don't make the trip. If I can't change the input masks at the table
level, I need to do it on each form.

"Larry Linson" <bo*****@localhost.not> wrote in message
news:tjdge.23173$hh6.2364@trnddc01...

"F. Michael Miller" <fm***@netzero.net> wrote in message
news:11*************@corp.supernews.com...
I realize that. That's what I am trying to change.

For example, I store a phone # in the sql table as a 10 digit string. I
want to be able to manipulate the input mask property on the linked
table

in
Access. This is purely an Access issue.


It does not sound "purely an Access issue" to me, if you want to
manipulate
the Input Mask at the table level, and the server DB does not support
Input
Masks at the table level. Consider using the Input Mask property of the
Control where you display/edit the data, if you want it to be purely an
Access issue.

That said, Input Masks are rather inflexible and my users have always
thought they were a hindrance rather than a help -- we used them,
primarily,
to satisfy Y2K standards, and otherwise handled the needed functionality
in
validation rules and validation code. YMMV.

Larry Linson
Microsoft Access MVP


Nov 13 '05 #6

P: n/a
Found it:

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



"F. Michael Miller" <fm***@netzero.net> wrote in message
news:11*************@corp.supernews.com...
I have a db with 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!

Nov 13 '05 #7

P: n/a
If you new anything about the business rules I'm implementing, perhaps you'd
have a point, but you don't.


"Alan Webb" <kn*******@hotSPAMmail.com> wrote in message
news:YO********************@comcast.com...
F. Michael Miller,
If you had a check constraint which refused to insert rows because a value
in the row didn't match an input mask and the process attempting to insert
a row was code and not a human then any row that violated the constraint
would not make it into the table. Now the problem is you have less data
in the destination than you should and there has to be a follow-on bit of
work to evaluate the rows that didn't insert and decide what to do. All
in a day's work for a DBA but I for one would rather have crappy data I
can analyze and address what's crappy than less than expected or no data
in my destination. For example, if only some of the line items on an
invoice make it into my table to calculate commissions for variable pay
then a bunch of my sales people are going to have smaller than expected
paychecks and I will be famous in way's I'd rather avoid.

Access bends the rules and allows you to do things that other dbms
software doesn't support. As I suggested in another post, if you must
have this then a check constraint tied to a stored procedure is your best
option.
--
Alan Webb
kn*******@SPAMhotmail.com
"It's not IT, it's IS"

"F. Michael Miller" <fm***@netzero.net> wrote in message
news:11*************@corp.supernews.com...
Access already allows me to set an input mask at the table level on a
linked sql table, independent of the server db. Access just changes how
the data is displayed.

I need to do this at the table level so that I don't ahve to worry about
how the data is displayed on any form that might be created. I'm trying
to build a generic db that I can copy, and then just change the table
references from the model to the actual sql db. Unfortunately, the input
masks don't make the trip. If I can't change the input masks at the
table level, I need to do it on each form.

"Larry Linson" <bo*****@localhost.not> wrote in message
news:tjdge.23173$hh6.2364@trnddc01...

"F. Michael Miller" <fm***@netzero.net> wrote in message
news:11*************@corp.supernews.com...
I realize that. That's what I am trying to change.

For example, I store a phone # in the sql table as a 10 digit string.
I
want to be able to manipulate the input mask property on the linked
table
in
Access. This is purely an Access issue.

It does not sound "purely an Access issue" to me, if you want to
manipulate
the Input Mask at the table level, and the server DB does not support
Input
Masks at the table level. Consider using the Input Mask property of the
Control where you display/edit the data, if you want it to be purely an
Access issue.

That said, Input Masks are rather inflexible and my users have always
thought they were a hindrance rather than a help -- we used them,
primarily,
to satisfy Y2K standards, and otherwise handled the needed functionality
in
validation rules and validation code. YMMV.

Larry Linson
Microsoft Access MVP



Nov 13 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.