Ckeck Number

[Bardia]

I have expense form in Access 2003,
1. I need to automate the check number control that if check number 100 has
been entered previously when user is entering the next check, number 101
would be automatically appear in check number control.
2. I would like to preserve the right for database administrator to reset
the starting number if becomes necessary.
3. Since check number control would be automated what I have to do that when
cursor enters the check number control and the next number appears
automatically cursor move to the next control on form without the need for
user to press the tab key on keyboard.
Please advise.

 

[Allen Browne]

Use the BeforeInsert event procedure of the form to look up the maximum
number used so far, and add 1:

Private Sub Form_BeforeInsert(Cancel As Integer)
Me![Check number] = Nz(DMax("[Check number]", "Table1"), 0) + 1
End Sub

This assumes the Check Number field is a Number type (not Text) in your
table.

If 2 users are adding new records at the same time, they will be given the
same check number.

 

[ken]

You'll find at example of one way of generating the next number in
sequence and preventing conflicts in a multi-user environment at:

http://community.netscape.com/n/pfx...yMessages&tsn=1&tid=23839&webtag=ws-msdevapps

This also allows for the next number in sequence to be reset.

Roger Carlson has a method for preventing conflicts while using the
simpler DMax method at:

http://www.rogersaccesslibrary.com/...?TID=395&SID=83z7c11zc7b721d2a1e51989c53d7ffb

With either method, by using the BeforeInsert event procedure you
don't need to move focus to the check number control at all. One a
user enters data in any control the check number will appear. Set the
check number control's Locked property to True (Yes) and its Enabled
property to False (No) to deny users access to the control.

Ken Sheridan
Stafford, England

 

[Tom van Stiphout]

On Mon, 4 May 2009 17:29:44 +0800, "Allen Browne"

True, but fortunately you will have a Unique Index on that field, so
the second insert will not happen.
Then with a good error handler in place, you can try the next number
up to see if if can be inserted properly, and repeat this until the
Insert succeeds.

-Tom.
Microsoft Access MVP

 

[Bardia]

Thank you very much Allen.
It works but I had to use it "on enter event" of control instead of the
"before insert" of the form event because there are two other methods of
payment (Credit Cards & Wire Transfer) on this form and when user adds a
record using any one of other two method check number would appear too

--
Ba finrdia

Use the BeforeInsert event procedure of the form to look up the maximum
number used so far, and add 1:

Private Sub Form_BeforeInsert(Cancel As Integer)
Me![Check number] = Nz(DMax("[Check number]", "Table1"), 0) + 1
End Sub

This assumes the Check Number field is a Number type (not Text) in your
table.

If 2 users are adding new records at the same time, they will be given the
same check number.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have expense form in Access 2003,
1. I need to automate the check number control that if check number 100
has
been entered previously when user is entering the next check, number 101
would be automatically appear in check number control.
2. I would like to preserve the right for database administrator to reset
the starting number if becomes necessary.
3. Since check number control would be automated what I have to do that
when
cursor enters the check number control and the next number appears
automatically cursor move to the next control on form without the need for
user to press the tab key on keyboard.
Please advise.

 

[Allen Browne]

Ouch! What happens if you go *back* into this control later?

Does it assign a new (incorrect) check number again because you entered the
control again?

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thank you very much Allen.
It works but I had to use it "on enter event" of control instead of the
"before insert" of the form event because there are two other methods of
payment (Credit Cards & Wire Transfer) on this form and when user adds a
record using any one of other two method check number would appear too

--
Ba finrdia

Use the BeforeInsert event procedure of the form to look up the maximum
number used so far, and add 1:

Private Sub Form_BeforeInsert(Cancel As Integer)
Me![Check number] = Nz(DMax("[Check number]", "Table1"), 0) + 1
End Sub

This assumes the Check Number field is a Number type (not Text) in your
table.

If 2 users are adding new records at the same time, they will be given
the
same check number.

I have expense form in Access 2003,
1. I need to automate the check number control that if check number 100
has
been entered previously when user is entering the next check, number
101
would be automatically appear in check number control.
2. I would like to preserve the right for database administrator to
reset
the starting number if becomes necessary.
3. Since check number control would be automated what I have to do that
when
cursor enters the check number control and the next number appears
automatically cursor move to the next control on form without the need
for
user to press the tab key on keyboard.
Please advise.

 

[John W. Vinson]

Thank you very much Allen.
It works but I had to use it "on enter event" of control instead of the
"before insert" of the form event because there are two other methods of
payment (Credit Cards & Wire Transfer) on this form and when user adds a
record using any one of other two method check number would appear too

As Allen suggests, the Enter event is too "hair trigger" in this case! I'd
suggest using the DoubleClick event instead (with a label on the form to tell
the user they can double click the textbox to assign a new check number). If
not, then the code should check first to see that the field is currently
empty.

 

[Bardia]

Thank you John,
could you please forward the code it would make it easier for the users.

 

[John W. Vinson]

Thank you John,
could you please forward the code it would make it easier for the users.

The doubleclick code would be:

Private Sub Check_Number_DblClick()
If IsNull(Me![Check Number]) Then
Me![Check number] = Nz(DMax("[Check number]", "Table1"), 0) + 1
End If
End Sub

Open the form in design view, select the check number control, view its
Properties, and find the Double Click event on its Events tab. Click the ...
icon by the property and choose Code Builder. Access will give you the
properly formatted Sub and End Sub lines, just edit the code to suit.