Input Mask to prevent Gaps?

  • Thread starter Thread starter Randy
  • Start date Start date
R

Randy

I need a way to prevent gaps caused by data entry errors. Example: I have
a control on my form where a contract number is entered. The same contract
number may be identical on numerous records. If one record has a gap in the
contract number caused by data entry errors, the summary I have created will
not include the number with a gap. If I have 10 records with a contract
number of I111222, but one record has a gap or space at the beginning of
the number such as _I111222 or I_111222 the record with the gap will not be
included in my summary. How can I eliminate these errors? I was was hoping
with some kind of input mask. The other issue is the contract numbers do
not always start with a letter or are always 7 digits. Could be: M11122 or
11122 etc...Thanks..Randy
 
Use a combo box with a row source of all contract numbers. Limit the
selection to the list of contract numbers. This will make data entry easier
and will eliminate the entry of "gaps"/spaces.
 
Randy said:
I need a way to prevent gaps caused by data entry errors. Example:
I have a control on my form where a contract number is entered. The
same contract number may be identical on numerous records. If one
record has a gap in the contract number caused by data entry errors,
the summary I have created will not include the number with a gap.
If I have 10 records with a contract number of I111222, but one
record has a gap or space at the beginning of the number such as
_I111222 or I_111222 the record with the gap will not be included in
my summary. How can I eliminate these errors? I was was hoping with
some kind of input mask. The other issue is the contract numbers do
not always start with a letter or are always 7 digits. Could be:
M11122 or 11122 etc...Thanks..Randy
Click to expand...

If you are only worried about spaces I would trap for that character in the
keypress event of the control and cancel it.

If KeyAscii = 39 Then KeyAscii = 0
 
Rick said:
If you are only worried about spaces I would trap for that character
in the keypress event of the control and cancel it.

If KeyAscii = 39 Then KeyAscii = 0
Click to expand...

That should be 32, not 39.
 
Duane said:
Use a combo box
Click to expand...

.... or a list box might work well here -- it can look a bit cleaner when
there's no need to enter new values, which you don't want anyway.
with a row source of all contract numbers. Limit the
selection to the list of contract numbers. This will make data entry easier
and will eliminate the entry of "gaps"/spaces.
Click to expand...

Note that this does NOT ensure that the number will be correct -- the
operator, given a list of 10 numbers, could still choose the wrong one
of those 10. And if that kind of mistake does occur, it could be
difficult to notice, since the number would still be "a" valid contract
number, except it's for "the" wrong contract.

For the person /entering/ new contract numbers, an input mask might be
useful, but you might also consider writing a Query to be used after the
fact to list contract numbers that don't seem to match the usual
pattern, or that have no associated records. (If a number is used, and
a week later there are no other records referring to it, maybe it needs
some attention.)

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.
 
Randy said:
Thanks Rick, I think this will work.
Click to expand...

Front end controls cannot ensure data integrity (does the KeyAscii = 32
thing prevent an illegal character from being pasted in?)

You need at least one Validation rule a.k.a CHECK constraint,
preferably more than one. Going the extra mile would be to handle both
flavors (DAO/ANSI-89 and ADO/ANSI-92) of wildcard character:

ALTER TABLE Contracts
ADD CONSTRAINT contract_num_no_spaces
CHECK (NOT(contract_num LIKE '* *' OR contract_num LIKE '% %'))
;
ALTER TABLE Contracts
ADD CONSTRAINT contract_num_not_zero_length
CHECK (LEN(contract_num) > 0)
;
 
Back
Top