Table Input Mask ??

[Guest]

I have a # and want to know how to make it automagically increment by setting
the input mask in the table. The # is

1234-06-0501

The number up to the -05 is a constant. The 01 portion is the digits I'd
like to have automagically increment as additional records are added to the
table.

Any help will be greatly appreciated.

KenP

 

[John Vinson]

I have a # and want to know how to make it automagically increment by setting
the input mask in the table. The # is

1234-06-0501

The number up to the -05 is a constant. The 01 portion is the digits I'd
like to have automagically increment as additional records are added to the
table.

Any help will be greatly appreciated.

KenP

An input mask is simply not capable of doing this; it's a very limited
tool. You can't do it in a Table, either; tables don't have any usable
events. You can do it with VBA code in a Form, but let me ask...

Are you CERTAIN that you want to violate all sorts of normalization
principles by having a composite field?

If the first eight digits are always going to be the same, why store
them in the field at all? You can just use an Integer field and use a
Format clause to display the unchanging boilerplate.

And are you CERTAIN that - over the entire course of the use of this
database - you cannot *possibly* have 100 records, overflowing your
two digits?


John W. Vinson[MVP]

 

[John Vinson]

John, the # will not go beyond 99.

Famous last words... <g> (seriously, I've been told similar things,
only to have the user come back three months later and say "well, the
database makes things so much easier that we've changed our
process...")

Can you provide a sample of the VBA code
I'd have to use? Thanks for your help.

If the rest of the "number" is indeed constant, I'd just use a Long
Integer field. Update the table using a Form (again, tables don't have
usable events; you must ensure that ONLY your Form is used to add
data).

In the form's BeforeInsert event put code like

Private Sub Form_BeforeInsert(Cancel as Integer)
Dim iNext As Integer
iNext = NZ(DMax("[ID]", "[YourTableName]"))+1
If iNext < 100 Then
Me.txtID = iNext
Else
Cancel = True
MsgBox "See, I told you so!"
End If
End Sub

This will look up the largest ID in the table; increment it by one;
and store the result in the textbox named txtID, bound to your ID
field.

Set txtID's Format property to

"\1\2\3\4\-\0\6\-\0\500"

to display a literal 1234-06-05 preceding the two numeric digits (the
00 format).

John W. Vinson[MVP]