Arvin,
I found another way to do what I assumed you were trying to do (pull a
tick
number from a different table, and add one new number to the table you
pulled
the tick number from).
Take a look at this and tell me what you think. I've run it a couple of
times and it seems to work and will even reset the increment. This is
assuming you have the part of the number connected with the date. I was
even
able to eliminate the TicketYear, TicketMonth and TicketIncrement fields
from
the table. I figured since those items were actually IN the ticket number
itself I could simply "strip" them out and use them in a Now() comparison.
Code is as follows:
Private Sub Form_BeforeUpdate(Cancel As Integer)
'Setting up the variables
Dim LastTickNo As String
'Dim LastTickYear As Integer - Held in reserve just in case comparing just
the "Months" doesn't work
Dim LastTickMo As Integer
Dim LastTickInc As Integer
LastTickNo = Nz(DMax("[TicketNumber]", "NetworkJobTicketTBL"), 0)
'If this is the first ticket ever created in this database then you don't
want to get an error over it
If LastTickNo = 0 Then
Me![TicketNumber] = (Year(Now()) & Month(Now()) & "01")
'Comparing the Month of the new ticket with the Year and Month of the
previous ticket
Else
'LastTickYear = Left(LastTickNo, 4) - Again, held in reserve just in
case
LastTickMo = Mid(LastTickNo, 5, 2)
LastTickInc = Mid(LastTickNo, 7)
If LastTickMo <> Month(Now()) Then
Me![TicketNumber] = (Year(Now()) & Month(Now()) & "01")
'This is to check if the LastTickInc number is less than 10 that a "0"
will
be added before the number
ElseIf LastTickInc < 9 Then
Me![TicketNumber] = (Year(Now()) & Month(Now()) & "0" &
(LastTickInc
+ 1))
Else
Me![TicketNumber] = (Year(Now()) & Month(Now()) & (LastTickInc +
1))
End If
End If
End Sub
I still need to code in the Error Handling but I think this will work.
I'd
still like a second opinion though just in case I completely missed
something.
Thanks,
Charlie
--
"Never, EVER let your co-worker "Fix" your computer for you. It may tick
off your LAN Guy.
Charlie Shaffer said:
Arvin, wow! This appears to be a little more than I was expecting. I'm
not
sure how all this comes together. Can you break things down a little for
me
to help me understand? Haven't had my morning caffine and seeing the
coding
you've done here is a little daunting.
Thanks
Charlie
--
"Never, EVER let your co-worker "Fix" your computer for you. It may tick
off your LAN Guy.
Arvin Meyer said:
I use a Ticket number that I reset the beginning of each year, but it
could
be done monthly. I have a table which holds the next number, which I
grab,
the update the table with the next number. Here's the code:
Public Function GetNextNumber() As Long
On Error GoTo Error_Handler
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
strSQL = "Select TicketNumber From tblNextNumber"
Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
With rst
.MoveFirst
.Edit
!TicketNumber = !TicketNumber + 1
.Update
End Select
End With
Exit_Here:
rst.Close
Set rst = Nothing
Set db = Nothing
Exit Function
Error_Handler:
MsgBox Err.Number & ": " & Err.Description
Resume Exit_Here
End Function
Sub ResetJobNumber()
On Error GoTo Error_Handler
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
strSQL = "Select * From tblNextNumber"
Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
With rst
.MoveFirst
.Edit
!TicketNumber = 1
.Update
End With
Exit_Here:
rst.Close
Set rst = Nothing
Set db = Nothing
Exit Sub
Error_Handler:
MsgBox Err.Number & ": " & Err.Description
Resume Exit_Here
End Sub
You would set textbox controlname like:
= Month(Date()) & "-" & GetNextNumber()
or use code in a button to fill the textbox.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com
message What I'm trying to do is set up a Job Ticket auto number. I know I
could
use
Autonumber but I want a better chance of keeping the gaps in
numbering to
a
minimum. Here is what I have:
NetworkJobTicketTBL with the fields:
TicketNumber
TicketYear - Default value Year(Now())
TicketMonth - " " Month(Now())
TicketIncrement - No default value
When the user saves the record, that's when I want the TicketNumber
to be
in
the format of TicketYear-TicketMonth-TicketIncrement. I can do
without
the
"-" for ease of coding. I would like the increment to reset to 1
when the
first ticket of the month is opened. The only way I can think to do
this
is
to have the previous record (assuming need to use Dmax on
[TicketNumber])
made available to compare its [TicketMonth] to the new (unsaved)
ticket's
info.
So, how do I pull up the last record without the user seeing it? I
think
I
can figure out the rest as far as comparing the info using If...
Then...
Or... statements. I can't remember
Thanks for that last tweek; glad