Autonumber based on another field & Restart annually 2

[air0jmb]

The database is used to track publication change requests for numerous technical documents controlled by my department. I have two problems...

1)What's the best way to keep the Request Numbers unique while also ensuring the Sequence# is always 1 higher than the previous record, and starts over each year.

2)Should the Sequence# field data type be Number or Text?

Each record gets assigned a Request Number which used 5 fields - Fleet, Chapter, Document, Year, Sequence#.

Example: 5-27-PSC-07-0249

The Sequence# needs to start over each year, and be consecutive, beginning with 0001. And, it needs to start with 0001 and climb consecutively for each Fleet.

There are 9 Fleet types: 1 through 9.

So if someone enters a record for Fleet 7, the Sequence# needs to be the next higher number for the last one used where Fleet was "7".

The only way I can see to use an autonumber for the Sequence# is to use 9 tables. And, I still don't know how to make it start over each year.

I'm hoping someone can provide some suggestions.

Thank you!

Mike

 

[MichaelRed]

start with a search (or these fora) in the faq section for autonumber. there are a few articles which illustate the technique.



MichaelRed

 

[air0jmb]

MichaelRed,
Thanks for the response. I've searched the forums and have found many approaches to automatically incrementing numbers. I will review these and try to find a solution that works for me. If you could provide an opinion on one question it would be a big help...

Should I leave the Request# as 5 fields or, enter them into the table as one field by concatenating them? And,what are the major advantages/disadvantages to each?

While reviewing many threads, it seems almost everybody is saving all data as one field. However, to me it seems it would be easier to sort and group records if each part of the Request number was left as individual fields.

Thanks for any input!

Mike

 

[MichaelRed]

I have not studied the advantages of one approach over the other. I believe that the results (of such a study) would need to consider more than JUST the data / storage aspects and in doing so would eventually reduce to specific circumstances of the specific application. In my own processing, I believe that I store the composit value, but then I also admit that I have not reviewed this specific procedure that this is closer to speculation than fact. A version of one such procedure I 'authored' is in the faqs, however, as with most of my submissions to these fora, it is meant more to illustrate techniques than to be applied directly to user's problems. While the procedure has been used directly by others, and works as intended, there may be better (more efficient) procedures, or ones which suit your specific requirements more closely.




MichaelRed

 

[air0jmb]

I've got an "autonumber" working with some code from "Access Cookbook"... yeah!

But what I need is for the Seq# to be dependent on txtYr field. If txtYr is "05", then the Seq# should be 1 higher than the last record entered with "05". And, when the next year rolls around and the first record is entered with "08" as the year, the Seq# would be 1. If someone added a request with "06", it would automatically increment the Seq# to one higher than the highest Seq# where txtYr is "06".

Public Function acbGetCounter_767() As Long
    ' Get a value from the counters table and
    ' increment it
    
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim intLocked As Integer
    Dim intRetries As Integer
    Dim lngTime As Long
    Dim lngCnt As Long
    
    ' Set number of retries
    Const conMaxRetries = 5
    Const conMinDelay = 1
    Const conMaxDelay = 10
    
    On Error GoTo HandleErr
    
    Set db = CurrentDb()
    intLocked = False
    
    Do While True
        For intRetries = 0 To conMaxRetries
            On Error Resume Next
            Set rst = db.OpenRecordset("tblFlexAutoNum_767", _
             dbOpenTable, dbDenyWrite + dbDenyRead)
            If Err = 0 Then
                intLocked = True
                Exit For
            Else
                lngTime = intRetries ^ 2 * _
                 Int((conMaxDelay - conMinDelay + 1) * Rnd + conMinDelay)
                For lngCnt = 1 To lngTime
                    DoEvents
                Next lngCnt
            End If
        Next intRetries
        On Error GoTo HandleErr
        
        If Not intLocked Then
            If MsgBox("Could not get a counter: Try again?", _
             vbQuestion + vbYesNo) = vbYes Then
                intRetries = 0
            Else
                Exit Do
            End If
        Else
            Exit Do
        End If
    Loop
    
    If intLocked Then
        acbGetCounter_767 = rst![CounterValue]
        rst.Edit
            rst![CounterValue] = rst![CounterValue] + 1
        rst.Update
        rst.Close
    Else
        acbGetCounter_767 = -1
    End If
    Set rst = Nothing
    Set db = Nothing
   
ExitHere:
    Exit Function
   
HandleErr:
    MsgBox Err & ": " & Err.Description, , "acbGetCounter_767"
    Resume ExitHere
End Function

Thanks for any help!!

Mike

 

[PHV]

What about adding a Year column in tblFlexAutoNum_767 ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[MichaelRed]

... or ... go look for the faq ... has some of the same stuff . sone parts simpler (the timeout for getting exclusive access to the tbale foir the value) and some more elaborate (concatenating the value from the table with other 'stuff" and recycling the number value based on the change of the date (or some part thereof)).



MichaelRed

 

[air0jmb]

PHV,

I've added field Yr as text to tblFlexAutoNum_767 and populated like:

Yr CounterValue
06 251
07 1
08 1

I'm guessing I now need to change:

 If intLocked Then
        acbGetCounter_767 = rst![CounterValue]
        rst.Edit
            rst![CounterValue] = rst![CounterValue] + 1
        rst.Update

to something like this:

 If intLocked Then
        acbGetCounter_767 = rst![CounterValue]
        rst.Edit
            rst![CounterValue] = rst![CounterValue] + 1 WHERE [Yr] = Forms![frmTPCR_767]![txtYear]
        rst.Update

Is that correct? And if so, what is the correct way to write it?

Thanks for helping!!

 

[PHV]

In your original code (not the last posted), replace this:
acbGetCounter_767 = rst![CounterValue]
with this:
rst.FindFirst "[Yr]=" & Forms![frmTPCR_767]![txtYear]
acbGetCounter_767 = rst![CounterValue]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[air0jmb]

I get error with acbGetCounter_767 as the caption and
3251: Operation is not supported for this type of object.

 

[PHV]

OK, create an index on the Yr field named, say, idxYr.
And now the new code:
rst.Index = "idxYr"
rst.Seek "=", Forms![frmTPCR_767]![txtYear]
acbGetCounter_767 = rst![CounterValue]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[air0jmb]

Seems to do the trick. What did setting it as an index do to fix? Would the previous worked had it been PK?

Thanks!!

Mike

 

[air0jmb]

MichaelRed,

In addition to reading your FAQ, I've actually reviewed all 88 threads where you've pointed folks to it. Unfortunately, I couldn't find where anybody was ever helped with incorporating your code into their application. They either knew what to do with it, or they banged away with help from other fine folks here until they got some alternative that worked for them.

To me, it's irrelevant whether or not the code I posted is simpler, harder, or more or less elaborate. What's relevant is the one who wrote it took an extra minute to tell the users how to get it into their database and how to use it.

I do appreciate your persistence in trying to keep those other 88 folks on the straight and narrow. Due to that, I abandoned the simple DMax approach and am actualy trying to incorporate something that doesn't exhibit the mult-user problems. In short, thank you (if for no other reason) for getting me started on the right foot).

Mike