Still struggling

[JLF]

I've been on other forum sites and have gotten some very viable solutions.
However, my last solution did not pass by management in creating a ticket no
that is to be pulled from a separate table and update each time the unique
number is used.

This is the code they did not like:
Private Sub Form_BeforeInsert(Cancel As Integer)
Me!HelpdeskTicketNo = Nz(DMax("[HelpdeskTicketNo]", "[Help Desk Tickets]")) +
1
Me.HelpdeskTicketNo.Requery
End Sub

It keeps jumping back to the first record and they do not want the Help Desk
Tickets table to be the table from which to pull the autonumber from. Here
is an alternative I am working on for the autonumber to come from a table
called tblNextNum, but it is not updating that table each time a record is
added and now it is not even showing a ticket number on the form:

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim db As DAO.Database
Dim rst As DAO.Recordset

If IsNull(Me![HelpdeskTicketNo]) Then
Set db = CurrentDb
Set rst = db.OpenRecordset("tblNextNum")
rst.MoveFirst
Me![HelpdeskTicketNo] = (rst![nextnum])
rst.Edit
rst![tblNextNum.nextnum] = rst![tblNextNum.nextnum] + 1
rst.Update
rst.Close
End If
End Sub

Any suggestions is greatly appreciated and I apologize in advance for posting
this issue once too many.

Thanks,
JLF

 

[Klatuu]

Try this variety:

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim strSQL As String

Me!HelpdeskTicketNo = Nz(Dlookup("[nextnum]","tblNextNum"), 0) + 1
strSQL = "UPDATE tblNextNum SET [nextnum] = " & Me.HelpdeskTicketNo & ";"
Currentdb.Execute strSQL, dbFailOnError
End Sub

 

[JLF]

OMG, you are awesome. I can put the tears away and hope this passes, but it
worked like a charm. Sorry for being such a pain, just wish I knew what I
was doing or had training. Thank you again.
Johnny

Try this variety:

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim strSQL As String

Me!HelpdeskTicketNo = Nz(Dlookup("[nextnum]","tblNextNum"), 0) + 1
strSQL = "UPDATE tblNextNum SET [nextnum] = " & Me.HelpdeskTicketNo & ";"
Currentdb.Execute strSQL, dbFailOnError
End Sub

I've been on other forum sites and have gotten some very viable solutions.
However, my last solution did not pass by management in creating a ticket no

[quoted text clipped - 35 lines]

Thanks,
JLF

 

[Klatuu]

If your management doesn't accept this version, get your resume out.
Work form someone with some intelligence <g>
--
Dave Hargis, Microsoft Access MVP

OMG, you are awesome. I can put the tears away and hope this passes, but it
worked like a charm. Sorry for being such a pain, just wish I knew what I
was doing or had training. Thank you again.
Johnny

Try this variety:

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim strSQL As String

Me!HelpdeskTicketNo = Nz(Dlookup("[nextnum]","tblNextNum"), 0) + 1
strSQL = "UPDATE tblNextNum SET [nextnum] = " & Me.HelpdeskTicketNo & ";"
Currentdb.Execute strSQL, dbFailOnError
End Sub

I've been on other forum sites and have gotten some very viable solutions.
However, my last solution did not pass by management in creating a ticket no

[quoted text clipped - 35 lines]

Thanks,
JLF

 

[JLF]

That may be the case. Thank you.

If your management doesn't accept this version, get your resume out.

OMG, you are awesome. I can put the tears away and hope this passes, but it
worked like a charm. Sorry for being such a pain, just wish I knew what I

[quoted text clipped - 16 lines]

 

[Klatuu]

Let me know what they say, please. I am curious, now.
If they need an explanation, I will be happy to provide a detailed
description of how it works and why it is a good method.
--
Dave Hargis, Microsoft Access MVP

That may be the case. Thank you.

If your management doesn't accept this version, get your resume out.

OMG, you are awesome. I can put the tears away and hope this passes, but it
worked like a charm. Sorry for being such a pain, just wish I knew what I

[quoted text clipped - 16 lines]

Thanks,
JLF

 

[JLF]

A one liner: "This seems to work."
That's it, but I'll take it. I was going nuts on this. Thanks again.
However, for my purposes, it would be helpful just to me to know what the
code does at this string level: Currentdb.Execute strSQL, dbFailOnError. I
understand the rest, but did not think of using DLookup. Was only going the
route I was told to go.
Johnny

Let me know what they say, please. I am curious, now.
If they need an explanation, I will be happy to provide a detailed
description of how it works and why it is a good method.

That may be the case. Thank you.

[quoted text clipped - 5 lines]

 

[Klatuu]

Okay, here is the logic behind it:
Firt line.
When you need to return only one field value from one row in a table, the
DLookup is much faster to execute and easier to code than creating, opening,
reading, and closing a recordset. In this case, there is only one row, so no
criteria is required.
The Nz function is used in the event the value returned from the DLookup is
Null. The Nz function looks at the value in the first argument and if it is
not null, it passes the value back. If it is null, it returns the value of
the second argument. In this case, the only time it should ever be null
would be for a brand new database, but you never know, so why not include it?
Then, when the value has been retrieved from the field, 1 is added to it to
make it the next number. So if a Null is returned, it is converted to 0 and
1 is added, so even without a calculator, you can see it will be 1. The
calculated number is then assigned to the form control and thus to whatever
field it is bound to.

Me!HelpdeskTicketNo = Nz(Dlookup("[nextnum]","tblNextNum"), 0) + 1

Second line.
This just builds an SQL string.

strSQL = "UPDATE tblNextNum SET [nextnum] = " & Me.HelpdeskTicketNo & ";"

Third line.
Here we have to update the field in the table with the new number. Again,
using SQL is always faster than recordset processing. And, the Execute
statement has advantages over the RunSql method. It is faster because it
doesn't go through the Access User Interface, it goes directly to Jet. And,
because it bypasses the UI, you don't have to diddle with the SetWarnings.
But, you do have to use the dbFailOnError option because without it, Access
will not know if an error occured. Again because it is bypassed, but
dbFailOnError tells Jet to throw back an error condition if the command
fails. The rest is just standard Jet SQL the updates the field in the table
with the current value of the form control, the one we just populated with
the old number + 1.

Currentdb.Execute strSQL, dbFailOnError

Now the why. And this is where managment had a point. In a multi user
environment, it would be possible for two users doing data entry to grab the
same high number from the actual data table and both be working on a new
record. In this case, a duplicate value could be created. That is because
of the lag time between the start of a record and the time it is saved.

Using this method, there is so little time between retrieving a value and
saving it back, the odds of a duplicate being created or not worth
calculating.

--
Dave Hargis, Microsoft Access MVP

A one liner: "This seems to work."
That's it, but I'll take it. I was going nuts on this. Thanks again.
However, for my purposes, it would be helpful just to me to know what the
code does at this string level: Currentdb.Execute strSQL, dbFailOnError. I
understand the rest, but did not think of using DLookup. Was only going the
route I was told to go.
Johnny

Let me know what they say, please. I am curious, now.
If they need an explanation, I will be happy to provide a detailed
description of how it works and why it is a good method.

That may be the case. Thank you.

[quoted text clipped - 5 lines]

Thanks,
JLF

 

[JLF]

Thanks again. Lesson well learned.

Okay, here is the logic behind it:
Firt line.
When you need to return only one field value from one row in a table, the
DLookup is much faster to execute and easier to code than creating, opening,
reading, and closing a recordset. In this case, there is only one row, so no
criteria is required.
The Nz function is used in the event the value returned from the DLookup is
Null. The Nz function looks at the value in the first argument and if it is
not null, it passes the value back. If it is null, it returns the value of
the second argument. In this case, the only time it should ever be null
would be for a brand new database, but you never know, so why not include it?
Then, when the value has been retrieved from the field, 1 is added to it to
make it the next number. So if a Null is returned, it is converted to 0 and
1 is added, so even without a calculator, you can see it will be 1. The
calculated number is then assigned to the form control and thus to whatever
field it is bound to.

Me!HelpdeskTicketNo = Nz(Dlookup("[nextnum]","tblNextNum"), 0) + 1

Second line.
This just builds an SQL string.

strSQL = "UPDATE tblNextNum SET [nextnum] = " & Me.HelpdeskTicketNo & ";"

Third line.
Here we have to update the field in the table with the new number. Again,
using SQL is always faster than recordset processing. And, the Execute
statement has advantages over the RunSql method. It is faster because it
doesn't go through the Access User Interface, it goes directly to Jet. And,
because it bypasses the UI, you don't have to diddle with the SetWarnings.
But, you do have to use the dbFailOnError option because without it, Access
will not know if an error occured. Again because it is bypassed, but
dbFailOnError tells Jet to throw back an error condition if the command
fails. The rest is just standard Jet SQL the updates the field in the table
with the current value of the form control, the one we just populated with
the old number + 1.

Currentdb.Execute strSQL, dbFailOnError

Now the why. And this is where managment had a point. In a multi user
environment, it would be possible for two users doing data entry to grab the
same high number from the actual data table and both be working on a new
record. In this case, a duplicate value could be created. That is because
of the lag time between the start of a record and the time it is saved.

Using this method, there is so little time between retrieving a value and
saving it back, the odds of a duplicate being created or not worth
calculating.

A one liner: "This seems to work."
That's it, but I'll take it. I was going nuts on this. Thanks again.

[quoted text clipped - 12 lines]