> 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" wrote:
>
>> 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 [MVP]" wrote:
>>
>> > 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>> > "Charlie Shaffer" <CharlieShaffer[ at ]discussions.microsoft.com> wrote in
>> > message news:2B5DA52A-EF61-4849-8972-7ACC8C2B593B[ at ]microsoft.com...
>> > > 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
>> > > --
>> > > "Never, EVER let your co-worker "Fix" your computer for you. It may
>> > > tick
>> > > off your LAN Guy.
>> >
>> >
>> >