Input Mask to prevent Gaps?

[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

 

[Duane Hookom]

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.

 

[Rick Brandt]

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

That should be 32, not 39.

 

[Randy]

Thanks Rick, I think this will work..

That should be 32, not 39.

 

[Vincent Johns]

Use a combo box

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

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.

 

[peregenem]

Thanks Rick, I think this will work.

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)
;