Autonumber just isn't doing it - I need to do things a little differently... (1 Viewer)

[AccessBeginner]

ok, I have a database which has a table called Constituents

There is a primary key called ContactID which is a simple Autonumber

I make a reference number (RefNo) from this which is given to each Constituents Manual Case Folder.

The reference number takes the format.... AR/10/C/585

Where the AR/ portion is constant
the 10 is taken from the last two digits of the current year
The /C/ is constant
and the 585 is the ContactID or autonumber

This is all done on the form when the user leaves (exits) the Full Name field.

(Exit Full Name field) Code is...

Private Sub FullName_Exit(Cancel As Integer)
If Me.NewRecord Then
On Error Resume Next
Me![ContactID] = Nz(DMax("[ContactID]", "Constituents"), 0) + 1
Me![RefNo] = ("AR/" & (Right(Year(Date), 2)) & "/C/" & [ContactID])
End If
End Sub

Now the problem is, if the user deletes a record (lots of reasons, may have a duplicate file or summat, and realises after it has been generated a ref), then the autonumber runs on and the case numbers get a gap (eg next one might be 590 when really it should be 588, etc).

I was thinking, if I could trim the last 3 digits from the actual reference field (RefNo) - put them into a new table column (lets call it TempNumber) and then I could use this as the basis of my RefNo calculation, code as below then....

Private Sub FullName_Exit(Cancel As Integer)
If Me.NewRecord Then
On Error Resume Next
Me![TempNumber] = Nz(DMax("[TempNumber]", "Constituents"), 0) + 1
Me![RefNo] = ("AR/" & (Right(Year(Date), 2)) & "/C/" & [TempNumber])
End If
End Sub


Problem is...

I have no idea how to make this TempNumber column and get the data in there that I need.

I know this is simple stuff, but I use access once or twice a year, and I never get to the bottom of things, or the time to investigate them properly

Any help appreciated, and I'll answer any questions you may have from my poor effort at explanation.

*off to hide to await being told he is as stupid as he thinks he is*

 

[AccessBeginner]

Was actually thinking, I could in theory amend the code (the Full Name Exit Code) so that it works the refno out based upon the previous refno - and trims the 3 digits from there all in the above piece of code, rather than creating a table column to hold this number.

Maybe that will be a little beyond me though.

 

[AccessBeginner]

And one final problem - if I trim 3 digits, the number may be longer once the cases hit 1000.... so I would need to trim everything after the final /

 

[AccessBlaster]

Do yourself a favor and stop thinking about you're autonumber field, just let it be! It has a job to do that has nothing to do with humans
You are on the right track with building your own numbers. Here is a snipit of code I have used.

Private Sub Form_[COLOR=red]BeforeInsert[/COLOR](Cancel As Integer)
Dim strCriteria As String
Me.[COLOR=red]AssetNumber[/COLOR] = Nz(DMax("[COLOR=red]AssetNumber[/COLOR]", "[COLOR=red]tblAssets[/COLOR]", strCriteria), [COLOR=red]104884[/COLOR]) + 1

Now after you get your number to increment correctly then you can concatenated yours fields together. In a query it would look like this: NewFieldName:[Field1]&[Field2] or in unbound controls =[Field1]&", "&[Field2]

hth

Richard

 

[AccessBeginner]

Yep, I think that is the problem. I had it tied to the Autonumber initially, but now need to tie it to the RefNo instead.

So, has anyone any idea what string expression I would need to put into my code above to take the 'number' portion of my RefNo (ie everything after the final /) and incorporate it into my code, instead of adding one to the ContactID.

I know this is basic stuff, but my head has gone so far around in circles, I have lost my way lol.

Thanks

 

[AccessBeginner]

SORTED !!!

Simply made my ContactID field into a standard number field, instead of an autonumber field

Then I put an 'after update' onto the RefNo field (allowing the user to manually amend it etc)

Once they do this, it takes the 'numeric' portion of the RefNo field, and puts it into the contactID field

Next time it comes into a new record, it works as usual, and adds o1 to the max ContactID in the table

Presto, doing exactly what I wanted.

Simples, but man it tortured me to get to that.

thanks for the help, you got me thinking on right track