Number Incrementation 2

[rasticle]

Hi,

I have a textbox that I have hold the value for a case number. The textbox name is caseNum. I want it to be in this format "07KS2007" + caseNum for example 07KS2007123, where the case number is incremented up by 1 with every new case. I have a button that needs to be clicked to add the case number to the case. It has been a while sense I have done this, so I'm a bit rusty. Also, does VBA allow initialization of a variable in the declaration? Here is my code:

Private Sub caseNum_Click()
Dim caseNum As String
Dim number As Integer

number = 500
number = number + 1
caseNum = "07KS" & Year(Date) & number
Me.caseNumber = caseNum

End Sub

Any help would be great! Thanks!

 

[MichaelRed]

search the faqs ...



MichaelRed

 

[maxhugen]

There are a few issues you may need to consider. One major issue with creating your own sequence number is the possibility that 2 users try to get and save a sequence number at the same time.

There are various solutions, depending on the nature of your app. If you could advise on the following, a method may be suggested:

1. Does more that one user add cases?
2. Is there only ever the one prefix, ie, "07KS"?
3. Does the sequence number reset each year back to 001?

Max Hugen
Australia

 

[rasticle]

I did search the FAQs before I posted this time. I didn't find anything.

There are going to be about 15 users of this database, pretty small. Yes several people will be adding cases to the database, so it is possible that two people might try to get a case number at the same time. The only prefix is going to be 07KS.. followed by year and then the sequence of number does reset to 001 every year.

Thanks,
Shawn

 

[MichaelRed]

widen your search

MichaelRed

 

[maxhugen]

Rasticle, I had a look in FAQs too... after the third try I did find something finally, at

http://www.tek-tips.com/faqs.cfm?fid=4634.

Max Hugen
Australia

 

[PHV]

I'd look here too:
faq700-184

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[maxhugen]

Thanks PHV, a link is a lot more helpful!

Max Hugen
Australia

 

[maxhugen]

G'day Rasticle

Hopefully, you're well aware that Access isn't ideal for ~15 concurrent users, and have designed your app to take that into account.

Since you can only add a maximum of 999 cases per year, it sounds like concurrency issues with an incremental sequence number should not become a major issue. In that case, you may find the following a little simpler.

First, create a sequence table in your BE, and link to the table in the FE:

[tt]table sequence
seqYear Text
seqNo Text
seqUsed Yes/No[/tt]

Create a dual-field Primary Key for the new table by selecting both seqYear and seqNo.

Copy the following code into a new Module. The first of the two functions, gfCreateSequenceNumbers, will populate the new 'sequence' table. Note that you can change the constants StartYear and EndYear to suit. Run this function in the Immediate Window by typing:
[blue]?gfCreateSequenceNumbers[/blue]
and clicking <Enter>.

The second function gfGetSequenceNumberis used in your code to retrieve the next available SeqNo, by finding the first number that has not been tagged as used. It tags that record, closes the recordset asap, and returns the SeqNo.

Option Compare Database: Option Explicit: Const cMODULE$ = "basSequence"

Public Function gfCreateSequenceNumbers%()
'
' Creates 3-digit sequence numbers by year.
' Change start and end years as required.
'
On Error GoTo err_gfCreateSequenceNumbers: Const cPROC = "gfCreateSequenceNumbers"
Dim db As Database: Set db = CurrentDb
Dim rs As Recordset, y%, n%
Const StartNumber% = 1
Const EndNumber% = 999
Const StartYear% = 2007
Const EndYear% = 2010

    Set rs = db.OpenRecordset("sequence"): With rs
        For y = StartYear To EndYear
            For n = StartNumber To EndNumber
                .AddNew
                    !seqYear = y
                    !seqNo = String(3 - Len(Trim(n)), "0") & n
                    Debug.Print !seqYear, !seqNo
                .Update
            Next n
        Next y
    .Close: End With

    MsgBox "Finished adding Sequence Numbers"
    gfCreateSequenceNumbers = True
    
exit_gfCreateSequenceNumbers:
    Set db = Nothing: Set rs = Nothing
    Exit Function
err_gfCreateSequenceNumbers:
    MsgBox cMODULE & vbCrLf & cPROC & vbCrLf & vbCrLf & _
            Err & ": " & Err.Description
    Resume exit_gfCreateSequenceNumbers
End Function

Public Function gfGetSequenceNumber$(sYear$)
'
' Get the next sequence number for the year specified.
' The year parameter is a string.
'
On Error GoTo err_gfGetSequenceNumber: Const cPROC = "gfGetSequenceNumber"
Dim db As Database: Set db = CurrentDb
Dim rs As Recordset, s$, rv
Dim NoYear%, NoNumber%

    s = "SELECT seqNo, seqUsed FROM sequence WHERE seqYear='" & sYear & "' ORDER by seqNo"
    Set rs = db.OpenRecordset(s, dbOpenDynaset, dbPessimistic): With rs
        If Not .EOF Then
            .FindFirst "Not seqUsed"
            If Not .NoMatch Then
                .Edit
                    !seqUsed = True
                .Update
                .Bookmark = .LastModified
                rv = !seqNo
            Else
                NoNumber = True
            End If
        Else
            NoYear = True
        End If
    .Close: End With
    
    s = ""
    If NoNumber% Then s = "There are no unused Sequence Numbers for the Year you have specified"
    If NoYear% Then s = "No Sequence Numbers have been created for the Year you have specified"
    If s <> "" Then MsgBox s, vbInformation, "Sequence Number Not Available"

    gfGetSequenceNumber = rv
    
exit_gfGetSequenceNumber:
    Set db = Nothing: Set rs = Nothing
    Exit Function
err_gfGetSequenceNumber:
    MsgBox cMODULE & vbCrLf & cPROC & vbCrLf & vbCrLf & _
            Err & ": " & Err.Description
    Resume exit_gfGetSequenceNumber
End Function

That's it. In your code example, you can use the function like this:

Private Sub caseNum_Click()
Dim sYear$
Dim seqNo
    
    sYear = Format(Date(),"yyyy")
    seqNo = gfGetSequenceNumber(sYear)
    
    ' check that the function returned a value
    if IsNull(seqNo) then 
        msgbox "Houston, we have a problem!"
        exit Sub
    End If

    Me.caseNumber = "07KS" & sYear & seqNo 

End Sub

Max Hugen
Australia

 

[rasticle]

Thank you for everyone who had something constructive to say!

(Michael, I'm not an idiot, I did look around to find an answer, or I wouldn't have asked. The point of forums like this is to get help and give help, not make people look stupid.)

 

[Remou]

MichaelRed has given a great deal of time to these fora, but is, unfortunately , too modest to give a direct reference to his own often-recommended FAQ. I am quite sure that many people have profited by his valuable and well-considered advice. I very much doubt that such an old hand had any intention of making anyone look stupid.