473,327 Members | 2,065 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

Change Input mask in code

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
7 6996
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
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

"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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: dude | last post by:
hello how would i make an input mask that only makes the first letter a capitol one? i've been playing around and testing various masks, also tried the wizard, but i've had no luck. could...
2
by: Ellen Manning | last post by:
I've got an A2K database with a link to a table in another A2K database. This linked table contains SSN formatted with the SSN input mask. I'm trying to use a dlookup using this linked table. ...
1
by: Miranda Evans | last post by:
In my application, a text box control resides in a form. The text box control is unbound, but--assuming all edits are passed when the user click a command button on the form--the contents of the...
9
by: Paul | last post by:
hi, is there an input mask i could use on a report to do the following: (1) if i enter "THISISATEST" on my form, i want the text box on my report to display: "T H I S I S A T E S T". (2) if...
11
by: MS | last post by:
The simplest input mask for peoples names is.... >L<?????????????? But what about when you have names like MacDonald, or Mary-Anne? Anyone come up with a good "all round" "idiots" mask that...
3
by: AA Arens | last post by:
When I want the first character of a field to be Uppercased, I need to make an input mask, like >L< followed by ??????? for example. But this mask creates ____ in an unfilled field, which I don't...
9
by: msnews.microsoft.com | last post by:
Hello. How can I set input mask for TextBox? Or can I use for mask input some other control?
7
by: Vlad | last post by:
I have a problem of different date formats in an international study, where the data entry module and error checking module are delivered to the data collecting centres. Error checking module...
7
desklamp
by: desklamp | last post by:
I'm a total Access newbie, please bear with me! Using Win2K/Access 2003. I'm trying to create a table in which I can store IP addresses and other information. According to Microsoft, there is no...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.