Using AutoNumber to get part of a Reference Field (1 Viewer)

[AccessBeginner]

I have a database which I am trying to setup which has an autonumber field (which the user will not see) giving just a standard number.

From this number, I want to be able to give the user a 'file reference', which will be in the format BB/09/C/1234, and have this stored in another field.

BB will be static
09 will be the current year
C will be static
1234 will be the number generated by the autonumber field

What is the easiest way of adding this to my database - ie Do I use a calculated field, or performa a calculation on my form when they are adding a record etc.

Please be gentle with me, as I am a relative noob with regards to Access, though I have set some little databases up for myself over the years.

Thanks in advance.

 

[Rabbie]

There have been several threads recently about using Autonumber as part of a reference number.

You need to consider if you can accept a jump in the number sequence as this can occur with autonumbers.

Personally I would use an Autonumber field to use as the record Primary Key to link it to other records and a separate field for the sequence part of your reerence number. This I would calculate as required by adding 1 to the previous highest used number. This also makes it easier to reset the sequence number at the start of each new year if that is what you require.

I would store the Seqno in my table and generate the full reference as required.

 

[AccessBeginner]

Yeah, the sequence would not be too much of an issue, since it is only a 'manual file' reference, but I like the idea of storing the number and working from there.

How would this be best done..... I was looking at trying to create a field that stored the number portion, then simply put an 'on entry' expression onto the actual calculated reference field.

Still not 100% sure how I would go about this though, any help or pointers would be much appreciated.

 

[jsv2002]

Have a look at the sample database here:

http://www.utterangel.com/utterangel.aspx

Its got a pretty good example for you to study

Good luck john

EDIT:

In the format of the control you could put something like "BB/09/C/"0000 which would display what you want but have no idea how to insert current year played around with (yyyy) but nothing so far so open to suggestions....

 

[AccessBeginner]

That's definitely useful, though my code experience is pretty dire tbh.

I simply replace my own field/table names into the 'On current' code and that should sort me?

So, taking this to the next step, I could then also put a value into my actualy 'file reference' field on the same piece of code, by adding the year, and a little text? If so, could someone give me the syntax for this.

I appreciate your patience with me, I am really meeting a huge learning curve here

 

[Mike375]

This will give you the last two digits of the current year

Right(Year(Date()),2)

 

[AccessBeginner]

ok, so if I am correct then.... I'll put the following into my #On Current' part of my form.....

(Note Form & Table = "Cases", Field = "CaseID", File Ref Field = "RefNo")

Private Sub Form_Current()
If Me.NewRecord Then
On Error Resume Next
Me!CaseID.DefaultValue = Nz(DMax("[CaseID]", "Cases"), 0) + 1
Me!RefNo.DefaultValue = ("BB/" & (Right(Year(Date()),2)) & "/C/" & "[CaseID]")
EndIf
EndSub

Now, the format/syntax of the RefNo Value line is completely wrong, and I find MS Access Help completely hopeless nowadays to navigate around. You seem to have to know what exactly you need, which kinda defeats the purpose of browsing it tbh.

On second thoughts too, I would probably need to put some 'zero's in too, to take all the end numbers up to 3 or 4 digits (ie 1 becomes 0001, etc).

 

[Mike375]

("BB/" & (Right(Year(Date()),2)) & "/C/" & [CaseID])

Remove commas from around [CaseID]

I just triwed the above to make a query field, except I used one of my own fields to replace [CaseID] which was CLSurname and that gives

BB/09/C/Smith

 

[AccessBeginner]

That is great Mike, thanks very much for your help.

I'll try this later tonight, when I get the table structure etc put together.

This will make the whole task much easier, and I'll drop the 'Autonumber' fields altogether as per the original replies.

Super service guys, and thanks for your patience.

 

[AccessBeginner]

ok, I put the code into my form, and nothing happens, either to the numberical ClientID field, or the RefNo field

Private Sub Form_Current()
If Me.NewRecord Then
On Error Resume Next
Me!ClientID.DefaultValue = Nz(DMax("[ClientID]", "Clients"), 0) + 1
Me!RefNo.DefaultValue = ("BB/" & (Right(Year(Date), 2)) & "/C/" & [ClientID])
End If
End Sub

Any ideas why it would not return any values into the appropriate fields?

 

[jsv2002]

Because on new record there is nothing for that code to do as a record has not been created yet I would imagine? try in the afterupdate of your first control/field might work then.

regards john

 

[Mike375]

This worked for me

Me![Text158] = ("BB/" & (right(Year(Date), 2)) & "/C/" & [NameNumber]) and put in Text158

BB/09/C/128909

I did that with an OnClick on a stand alone label. I also put it on OnCurrent and it worked.

 

[Mike375]

Because on new record there is nothing for that code to do as a record has not been created yet I would imagine? try in the afterupdate of your first control/field might work then.

regards john

I think that should still give'

BB/09/C/

 

[AccessBeginner]

If Me.NewRecord Then
On Error Resume Next
Me!ClientID.DefaultValue = Nz(DMax("[ClientID]", "Clients"), 0) + 1
Me!RefNo.DefaultValue = ("BB/" & (Right(Year(Date), 2)) & "/C/" & [ClientID])
End If


The above is the exact code I am using with regards to this, and no matter where I put it (have tried On Current for form, After Update for fields, On click, etc), it gives me blanks

Fields are (table = Clients):

ClientID (Long Integer)
RefNo (Text)

The only thing I see happening is that, when I enter a form, the 'RefNo' field displays #Name?, then when this piece of code runs, from wherever I put it, it goes to a blank.

I have 2 records in there, whereby I entered the appropriate data into the table directly.

The above code is added to my form in numerous places, but I am still getting nowt.

Is my 'Me.Newrecord' bit causing me a problem here, as I have tried to enter the form using the 'Add New' button, as well as navigating to the new record and trying from there.

 

[AccessBeginner]

ok, I got it working by changing the code......

I put the following into an 'After Update' on the Clients Name field, which will suit perfectly well....

Private Sub FullName_AfterUpdate()
If Me.NewRecord Then
On Error Resume Next
Me![ClientID] = Nz(DMax("[ClientID]", "Clients"), 0) + 1
Me![RefNo] = ("AR/" & (Right(Year(Date), 2)) & "/C/" & [ClientID])
End If
End Sub

It seems to put the correct value into the 'ref no field', but nothing into the ClientID field for some reason

EDIT - correction, it is working perfectly now..... thanks again guys, that was a great help !!

100% effort and help on this my first day on this forum

 

[Mike375]

I just noticed you have DefaultValue.

I think the problem might be that the expression won't work as the DefaultValue

 

[Mike375]

As a side note, if you use VBA or a SetValue macro action to change the property of a text box it is only temporary, it reverts to the original settings when you close the form.

To keep properties that are changed by VBA or macro you have the code first open the form in Design view, change the property and then close and save form.

 

[AccessBeginner]

That's what I removed, after a little trial and error, and as my post above, it worked fine

Cheers for the help Mike, I really appreciate it.

 

[Mike375]

Check the post I just made before yours as to change text box properties..save for future reference

 

[AccessBeginner]

Check the post I just made before yours as to change text box properties..save for future reference

Cheers mike, you are a star.

I just used the code as I listed previously, in an 'after update' on the name field. It seems to work ok, and keeps the data after i close the form ok, so no probs there.

This is very useful actually, and is as hard a piece of coding as this miserable little database will require, so a little playing tomorrow and I'll have the job done

The database is simple, but it will actually make life very easy for the poor beggar who will be using it, so this hopefully will do me until the next one (which I am planning next week, so don't go away on hols please) lol