Automatically incriment the referance number by 1

[Robin Hood]

Hello,

Can someone help!

I have a table where the referance numbers are as follows;

CCB0012008
CCB0022008
CCB0032008

and so on.

What I would like to do is when a new entry is entered in the form the referance number is automatically incrimented by 1 and shown on the form for the new entry.

I did try to make the Referance number the primary key but it put the numbers out of sequence.

many thanks for any replies

 

[Rabbie]

Because you have a mixture of letters and numbers in your reference number it is stored as a string. It would make your task easier if you split the number into its component parts - Prefix = "CCB", Seqno = 001 etc, and Yearfield = 2008. by storing these in different fields you would find them easier to manipulate and you could use a function like DMAX("Seqno","TableName") + 1 to get the next Seqno.

You can then merge them together to get a reference number by using something like

"CCB" & format(Seqno,"000") & Yearfield

This should give you something to build on

 

[Robin Hood]

Hello Rabbie,

Thanks for you quick reply. This is all new to me and I have no training in this field. I will try and work around what you have said, but it may take me a while as I am a complete novice.

I will let you know how I got on.

many thanks

Robin

 

[Rabbie]

Good Luck. keep us posted with any queries you may have

 

[Robin Hood]

Hello Rabbie,

Unfortunately I have got myself tied up in knots. I am not as clever as I thought.

I have created 2 new fields in my table called 'Seqno' and 'Year next to the field called 'Reference Number'. In the 'Reference Number' field in design view I have set the 'default value' to 'CMM' but after that I do not have the know how to link the number from the 'Seqno' field and the date from the 'Year' field. So that they all show up in the Reference Number field. e.g CMM1232008. At the moment I just have CMM.

Can you help?

Many thanks

Robin

 

[Rabbie]

I have attached a demo DB that shows how you can do this in a form(Form1) that adds records to Table1. Post here if you have any queries

 

[Robin Hood]

Rabbie,

Many thanks for that. Its just what I needed. I will study and learn from it.

Once again, Thank you

Robin