Next Number issue

[Johnny]

I have a help desk form that needs the very next number to show in the help
desk control source text field. I am trying to pull this number from a table
so the help desk form can support more than one user simultaneously entering
data. I started with a table that starts with 20080001 for the first help
desk number and so on. Here is my code:
Private Sub Form_BeforeInsert(Cancel As Integer)
If Me.NewRecord Then
Me.HelpdeskTicketNo = Nz(DMax("NextNum", "tblNextNum"), 0) + 1
End If

End Sub

The problem I have is how to clear the table so that if one user starts an
entry at 20080002, a second user now starts with the next available number
20080003, so on. The table is not clearing the number once a user takes on
20080002. Any suggestions on how to do this so each entry is a unique
number and multiple users can be doing data entry at the same time?

 

[Roger Carlson]

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "AutonumberProblem.mdb" which illustrates how to do this.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Johnny]

Roger,

I made my default value identical to how your DB was set up and added the
err issue. Only problem is I am pulling the next number directly from a
separate table where the only field in the table is nextnum. When I click on
new record, it keeps populating the same ticket number as the previous
ticket. Example: previous ticket populated to 20080001 since the tblNextNum
field started with 20080000, then the DMax added the +1 for that ticket,
which is what I wanted. However, when I clicked on add new record, it just
populated the 20080001 to that ticket as well, which of course you can't save
due to duplicate primary keys. What I need, I think, is a code that will
bring the latest used ticket number into it. So when ticket 20080001 number
is used, the table will reflect 20080001. I had this form working with a
Dmax based on the HelpdeskTicketNo table, but that's not way I was told by
the IT supervisor he wanted it done. He want's pulled from a separate table.
Is this possible?

Thanks for your assistance and for also a great web site that could help a
novice like me.

 

[Roger Carlson]

Before I went any farther, I have to ask the IT supervisor why it is
necessary to use a separate table when there is simple and workable
alternative that doesn't use one.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Johnny]

The main thing he was trying to avoid was when we are both in the DB at the
same time, one or the other would not get the error Type Mismatch due to both
of us trying to use the same helpdesk ticket no at the same time. Maybe I
soud change it from Before Update to another function like maybe after update
or after insert so that once one of us has assigned a ticket number by either
entering data or clicking on new record it pulls in the next number. I did
insert your DB suggestion on the "on error", but still get the type mismatch
because the same tickets numbers are being grabbed at the same time.

This is what I was using to begin with instead of pulling a ticket number
from a separate table all together, just can't have people entering data at
same time:

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

Which works fine using only one table for everything, but that's it. What
are your thoughts and thank you for your time.

 

[Johnny]

I neglected to answer your question in my other post. The reasoning behind
using a separate table for the ticket no is because the IT supv. saw it being
done on a PO Form, but was not sure how it was being done and I'm too new at
this to assist much. Thanks.

 

[Roger Carlson]

You shouldn't get a Type Mismatch error for colliding primary key values.
Something else is going on. The On Error should take care of any collisions
as the first one to get the number gets it and the second one get the error
which results in a new number.

I'm not saying it can't be done. I'm sure it can. I just can't easily
adapt what I've done it my sample to do it.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

The main thing he was trying to avoid was when we are both in the DB at
the
same time, one or the other would not get the error Type Mismatch due to
both
of us trying to use the same helpdesk ticket no at the same time. Maybe I
soud change it from Before Update to another function like maybe after
update
or after insert so that once one of us has assigned a ticket number by
either
entering data or clicking on new record it pulls in the next number. I
did
insert your DB suggestion on the "on error", but still get the type
mismatch
because the same tickets numbers are being grabbed at the same time.

This is what I was using to begin with instead of pulling a ticket number
from a separate table all together, just can't have people entering data
at
same time:

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

Which works fine using only one table for everything, but that's it. What
are your thoughts and thank you for your time.
--
Johnny

Before I went any farther, I have to ask the IT supervisor why it is
necessary to use a separate table when there is simple and workable
alternative that doesn't use one.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Roger Carlson]

Seems to me the two of you should be digging around in the PO form, then.
You can often learn a lot by walking through someone else's code.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Michael Gramelspacher]

I neglected to answer your question in my other post. The reasoning behind
using a separate table for the ticket no is because the IT supv. saw it being
done on a PO Form, but was not sure how it was being done and I'm too new at
this to assist much. Thanks.

There was a Generating Sequential Number question in the SQL Server newsgroup
today. This is an attempt to duplicate it in Access.

It requires a separate table for the key.

CREATE TABLE SomeTableKey
(SomeTableKey LONG NOT NULL);

Edit the table and enter the value 1.

In the form event (using some test form as an example):

Private Sub Form_BeforeInsert(Cancel As Integer)

Dim n As Long

n = GetKey
If Not (n = 0) Then
Me.some_key = n
Me.some_code.SetFocus
Else
MsgBox "Error in table SomeTableKey!", vbCritical
Cancel = True
End If

End Sub

Function GetKey()

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim n As Long

On Error GoTo ErrorHandler

Set db = DBEngine(0)(0)
Set rst = db.OpenRecordset("SELECT SomeTableKey FROM SomeTableKey;")

With rst

.MoveFirst
n = .Fields(0)
.Edit
.Fields(0) = n + 1
.Update

End With

GetKey = n

ExitProcedure:
On Error Resume Next
rst.Close
Set rst = Nothing
Set db = Nothing
Exit Function

ErrorHandler:
GetKey = 0
Resume ExitProcedure

End Function

Consider it just something to play with.