Autonumbers in form for multiple users

J

Johnny

I've been researching this for days, but obviously not getting an
understanding. I have a form where the Autonumber (I know we should not use,
but I am told to do so) is using this code on a help desk form based on one
table Help Desk Tickets; here is the code I am currently using:

Private Sub Form_BeforeInsert(Cancel As Integer)
Me!HelpdeskTicketNo = Nz(DMax("[HelpDeskTicketNo]", "[Help Desk Tickets]"))
+ 1
End Sub

Where HelpdeskTicketNo is the name and control source from table Help Desk
Tickets. The above works like a charm.

However, superiors tell me they want the setup of the of the automated
ticket number to come from another single table, such as NextNum (table name)
and NextNum (field name) +1 and to show up after update or before insert of
the Helpdesk Form so more than one user can share the form at the same time
without causing one or the other to void out their entries due to duplicate
ticket number fields.

Any ideas on this is greatly appreciated. From a novice.
 
R

Ron2006

The problem that you are having, and I had it also before I changed my
code, is that you are establishing the number before the insert, BUT
the record is not really being created UNTIL you get off of the
record. This gives other users time to sneak in and try (sometimes
successfully) to use that same number because the record is NOT yet
created.

One approach that has worked for me, is that I use a reliminary screen
to verify that they want to create a record and when they say yes then
I actually create the record and then put them on an edit screen on
that already created record with the new key number (not really an
autonumber since you are controlling it). This has eliminated that
problem for me.

Even if you use a separate table to hold that number, you have to make
sure that you update the number in that table as SOON as you have the
number. You cannot wait until the user finishes with the record you
are sitting on.

Ron
 
K

Klatuu

Another issue is you are allowing users to share the same mdb (as least that
is how is sound from your post)

To piggyback on Ron's suggestion, just adding a new record to your form will
not do it. The record has yet to be written to the table. It only exists in
the form's recrdset. You have to either requery the form or use an SQL
Insert statement to wrtie directly to the table. In either case, you then
have to navigate to the new record and complete it.
 
J

Johnny

Thank you Klatuu, but do you have some suggested code to use? I am also not
familiar with the reference of requerying. I will continue to try to
research both, but appreciate your guidance. Thanks again.
 
J

Johnny

Klatuu,

One last question. I am so close. The Me.Requery is working, but sometime
after choosing to enter a new record and I enter the first field (which is a
numberical asset #), the form jumps to the first record. If I go back to the
last record where I left off, I can continue. This is happening frequently.
What did I do wrong?

Private Sub Form_BeforeInsert(Cancel As Integer)
Me!HelpdeskTicketNo = Nz(DMax("[HelpdeskTicketNo]", "[Help Desk Tickets]"))
+ 1
Me.Requery
End Sub
 
K

Klatuu

The Requery causes it to go back to the first record. Here is how you can
solve it. Note, I added a bit to your DMax line, it is missing a 0:

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim lngCurrentTicket As Long

Me!HelpdeskTicketNo = Nz(DMax("[HelpdeskTicketNo]", "[Help Desk
Tickets]"),0)
+ 1
lngCurrentTicket = Me!HelpdeskTicketNo
Me.Requery
With Me.RecordsetClone
.FindFirst "[HelpdeskTicketNo] = " & lngCurrentTicket
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
End Sub

--
Dave Hargis, Microsoft Access MVP


Johnny said:
Klatuu,

One last question. I am so close. The Me.Requery is working, but sometime
after choosing to enter a new record and I enter the first field (which is a
numberical asset #), the form jumps to the first record. If I go back to the
last record where I left off, I can continue. This is happening frequently.
What did I do wrong?

Private Sub Form_BeforeInsert(Cancel As Integer)
Me!HelpdeskTicketNo = Nz(DMax("[HelpdeskTicketNo]", "[Help Desk Tickets]"))
+ 1
Me.Requery
End Sub

--
Johnny


Klatuu said:
A requery writes any unsafed data to the disk, then rereads the disk to get a
fresh set of data in the form's or control's recordset. It is a simple
command:

Me.Requery

You can also requery combo boxes and list boxes
Me.MyCombName.Requery
Click to expand...
Click to expand...
 
J

Johnny

That works great. I can now have multiple users entering data, THANK YOU!
Only one minor little thing is now happening. In the first field for the
asset number, the screen flashed and does not enter the first number.
Example: The asset number is 121. Enter 1 "screen flash" (I assume this is
the requerying?) then the only thing that shows is the number 21. Fixable,
but curious about that one. Thanks.
--
Johnny


Klatuu said:
The Requery causes it to go back to the first record. Here is how you can
solve it. Note, I added a bit to your DMax line, it is missing a 0:

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim lngCurrentTicket As Long

Me!HelpdeskTicketNo = Nz(DMax("[HelpdeskTicketNo]", "[Help Desk
Tickets]"),0)
+ 1
lngCurrentTicket = Me!HelpdeskTicketNo
Me.Requery
With Me.RecordsetClone
.FindFirst "[HelpdeskTicketNo] = " & lngCurrentTicket
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
End Sub

--
Dave Hargis, Microsoft Access MVP


Johnny said:
Klatuu,

One last question. I am so close. The Me.Requery is working, but sometime
after choosing to enter a new record and I enter the first field (which is a
numberical asset #), the form jumps to the first record. If I go back to the
last record where I left off, I can continue. This is happening frequently.
What did I do wrong?

Private Sub Form_BeforeInsert(Cancel As Integer)
Me!HelpdeskTicketNo = Nz(DMax("[HelpdeskTicketNo]", "[Help Desk Tickets]"))
+ 1
Me.Requery
End Sub

--
Johnny


Klatuu said:
A requery writes any unsafed data to the disk, then rereads the disk to get a
fresh set of data in the form's or control's recordset. It is a simple
command:

Me.Requery

You can also requery combo boxes and list boxes
Me.MyCombName.Requery
--
Dave Hargis, Microsoft Access MVP


:

Thank you Klatuu, but do you have some suggested code to use? I am also not
familiar with the reference of requerying. I will continue to try to
research both, but appreciate your guidance. Thanks again.
--
Johnny


:

Another issue is you are allowing users to share the same mdb (as least that
is how is sound from your post)

To piggyback on Ron's suggestion, just adding a new record to your form will
not do it. The record has yet to be written to the table. It only exists in
the form's recrdset. You have to either requery the form or use an SQL
Insert statement to wrtie directly to the table. In either case, you then
have to navigate to the new record and complete it.
--
Dave Hargis, Microsoft Access MVP


:

Great idea. Thank you, makes sense.
--
Johnny


:

The problem that you are having, and I had it also before I changed my
code, is that you are establishing the number before the insert, BUT
the record is not really being created UNTIL you get off of the
record. This gives other users time to sneak in and try (sometimes
successfully) to use that same number because the record is NOT yet
created.

One approach that has worked for me, is that I use a reliminary screen
to verify that they want to create a record and when they say yes then
I actually create the record and then put them on an edit screen on
that already created record with the new key number (not really an
autonumber since you are controlling it). This has eliminated that
problem for me.

Even if you use a separate table to hold that number, you have to make
sure that you update the number in that table as SOON as you have the
number. You cannot wait until the user finishes with the record you
are sitting on.

Ron
Click to expand...
Click to expand...
Click to expand...
 
K

Klatuu

Don't know why you are getting the screen flash. The requery should not take
place until you have entered a complete number.
--
Dave Hargis, Microsoft Access MVP


Johnny said:
That works great. I can now have multiple users entering data, THANK YOU!
Only one minor little thing is now happening. In the first field for the
asset number, the screen flashed and does not enter the first number.
Example: The asset number is 121. Enter 1 "screen flash" (I assume this is
the requerying?) then the only thing that shows is the number 21. Fixable,
but curious about that one. Thanks.
--
Johnny


Klatuu said:
The Requery causes it to go back to the first record. Here is how you can
solve it. Note, I added a bit to your DMax line, it is missing a 0:

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim lngCurrentTicket As Long

Me!HelpdeskTicketNo = Nz(DMax("[HelpdeskTicketNo]", "[Help Desk
Tickets]"),0)
+ 1
lngCurrentTicket = Me!HelpdeskTicketNo
Me.Requery
With Me.RecordsetClone
.FindFirst "[HelpdeskTicketNo] = " & lngCurrentTicket
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
End Sub

--
Dave Hargis, Microsoft Access MVP


Johnny said:
Klatuu,

One last question. I am so close. The Me.Requery is working, but sometime
after choosing to enter a new record and I enter the first field (which is a
numberical asset #), the form jumps to the first record. If I go back to the
last record where I left off, I can continue. This is happening frequently.
What did I do wrong?

Private Sub Form_BeforeInsert(Cancel As Integer)
Me!HelpdeskTicketNo = Nz(DMax("[HelpdeskTicketNo]", "[Help Desk Tickets]"))
+ 1
Me.Requery
End Sub

--
Johnny


:

A requery writes any unsafed data to the disk, then rereads the disk to get a
fresh set of data in the form's or control's recordset. It is a simple
command:

Me.Requery

You can also requery combo boxes and list boxes
Me.MyCombName.Requery
--
Dave Hargis, Microsoft Access MVP


:

Thank you Klatuu, but do you have some suggested code to use? I am also not
familiar with the reference of requerying. I will continue to try to
research both, but appreciate your guidance. Thanks again.
--
Johnny


:

Another issue is you are allowing users to share the same mdb (as least that
is how is sound from your post)

To piggyback on Ron's suggestion, just adding a new record to your form will
not do it. The record has yet to be written to the table. It only exists in
the form's recrdset. You have to either requery the form or use an SQL
Insert statement to wrtie directly to the table. In either case, you then
have to navigate to the new record and complete it.
--
Dave Hargis, Microsoft Access MVP


:

Great idea. Thank you, makes sense.
--
Johnny


:

The problem that you are having, and I had it also before I changed my
code, is that you are establishing the number before the insert, BUT
the record is not really being created UNTIL you get off of the
record. This gives other users time to sneak in and try (sometimes
successfully) to use that same number because the record is NOT yet
created.

One approach that has worked for me, is that I use a reliminary screen
to verify that they want to create a record and when they say yes then
I actually create the record and then put them on an edit screen on
that already created record with the new key number (not really an
autonumber since you are controlling it). This has eliminated that
problem for me.

Even if you use a separate table to hold that number, you have to make
sure that you update the number in that table as SOON as you have the
number. You cannot wait until the user finishes with the record you
are sitting on.

Ron
Click to expand...
Click to expand...
Click to expand...
 
J

Johnny

It quickly flashes the first record and then continues. Only seems to happen
when multi users are entering stuff. It's still workable. When in multiuser
mode it quickly flashes the first record. Either way, I made the deadline of
4pm today thanks to you.
--
Johnny


Klatuu said:
Don't know why you are getting the screen flash. The requery should not take
place until you have entered a complete number.
--
Dave Hargis, Microsoft Access MVP


Johnny said:
That works great. I can now have multiple users entering data, THANK YOU!
Only one minor little thing is now happening. In the first field for the
asset number, the screen flashed and does not enter the first number.
Example: The asset number is 121. Enter 1 "screen flash" (I assume this is
the requerying?) then the only thing that shows is the number 21. Fixable,
but curious about that one. Thanks.
--
Johnny


Klatuu said:
The Requery causes it to go back to the first record. Here is how you can
solve it. Note, I added a bit to your DMax line, it is missing a 0:

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim lngCurrentTicket As Long

Me!HelpdeskTicketNo = Nz(DMax("[HelpdeskTicketNo]", "[Help Desk
Tickets]"),0)
+ 1
lngCurrentTicket = Me!HelpdeskTicketNo
Me.Requery
With Me.RecordsetClone
.FindFirst "[HelpdeskTicketNo] = " & lngCurrentTicket
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
End Sub

--
Dave Hargis, Microsoft Access MVP


:

Klatuu,

One last question. I am so close. The Me.Requery is working, but sometime
after choosing to enter a new record and I enter the first field (which is a
numberical asset #), the form jumps to the first record. If I go back to the
last record where I left off, I can continue. This is happening frequently.
What did I do wrong?

Private Sub Form_BeforeInsert(Cancel As Integer)
Me!HelpdeskTicketNo = Nz(DMax("[HelpdeskTicketNo]", "[Help Desk Tickets]"))
+ 1
Me.Requery
End Sub

--
Johnny


:

A requery writes any unsafed data to the disk, then rereads the disk to get a
fresh set of data in the form's or control's recordset. It is a simple
command:

Me.Requery

You can also requery combo boxes and list boxes
Me.MyCombName.Requery
--
Dave Hargis, Microsoft Access MVP


:

Thank you Klatuu, but do you have some suggested code to use? I am also not
familiar with the reference of requerying. I will continue to try to
research both, but appreciate your guidance. Thanks again.
--
Johnny


:

Another issue is you are allowing users to share the same mdb (as least that
is how is sound from your post)

To piggyback on Ron's suggestion, just adding a new record to your form will
not do it. The record has yet to be written to the table. It only exists in
the form's recrdset. You have to either requery the form or use an SQL
Insert statement to wrtie directly to the table. In either case, you then
have to navigate to the new record and complete it.
--
Dave Hargis, Microsoft Access MVP


:

Great idea. Thank you, makes sense.
--
Johnny


:

The problem that you are having, and I had it also before I changed my
code, is that you are establishing the number before the insert, BUT
the record is not really being created UNTIL you get off of the
record. This gives other users time to sneak in and try (sometimes
successfully) to use that same number because the record is NOT yet
created.

One approach that has worked for me, is that I use a reliminary screen
to verify that they want to create a record and when they say yes then
I actually create the record and then put them on an edit screen on
that already created record with the new key number (not really an
autonumber since you are controlling it). This has eliminated that
problem for me.

Even if you use a separate table to hold that number, you have to make
sure that you update the number in that table as SOON as you have the
number. You cannot wait until the user finishes with the record you
are sitting on.

Ron
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Next Number issue 8
Opened/Closed Weekly count and YTD 1
Refresh data in sub forms when one sub form is edited 4
Form pulling data from table1 to autofill fields in table2 3
Using a subform to show newrecord on the main form 1
Refreshing main form from the subform 1
Search for a Record 4
Requery main form after subform update and not lose place in main form. 1

Top