Custom autonumber?? 2

[progressiverookie]

I am trying to customize a record control autonumber in this format: YYYYRCN00000, where YYYY = year, RCN = remains the same in every record, and 00000 = autoincrement. I am not very confident in writing code without easy instructions. Any assistance would be greatly appriciated. An example of the first three record numbers would be:

2004RCN00001
2004RCN00002
2004RCN00003

-Jeremy

 

[LittleSmudge]

What happens when you get to the end of the year ?

Do you want the first record in 2005 to be

2005RCN00001

or

2005RCNxxxxx+1 where xxxxx was the number of the last record in 2004


The latter option is the easiest

You simply have a field in the table called CreatedDate and set that to the date when the record is created ( set Default value in table design to Now()
Have another field called RecordId set to a simple AutoNumber

Then on all forms and reports where you need to display the Id you put a text box control with the control source set to

=Format([CreatedDate],"YYYY") & "RCN" & [RecordId]




'ope-that-'elps.




G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[progressiverookie]

Hi GLS,

Thanks for the reply. I have tried that and ran into two problems. The first problem is, when I combine the year field and the autonumber field in a form, the new record control number filed drops the zeros before the actual number. I need those zeros to stay. For example, 2004RCN00012 actually displays as 2004RCN12.

The second problem is, I want to be able to search by the record control number. How do I take the combined field in a form and update it into a table for every new record I enter?

Thanks again!

-Jeremy

 

[willir]

Jeremy

Would it help to treat the number as a text string by using the format command?

strID = format(MyAutoNumber,"00000000")
strYear = datepart("yyyy",date())

strMyNewID = strYear & "RCN" & strID

I have broken it out a bit; a more typical way of coding would be the example LittleSmudge provided.

Your second question seems to be a two parter...
- search by control number
- combine the date and autonumber and generate the unique identifier for the new record.

Generating the unique identifier is the easier task. Use the "Before Insert" event. By the time this event is ready to execute, you will have generated your autonumber. Now just plug the code in that you choose to use to generate the unique identifier. For example...

Me.strID = datepart("yyyy",date()) & "RCN" & format(MyAutoNumber,"00000000")

Where Me.strID is the field on the form you use to display the unique identifier.


Querying an intellegent record identifier takes a bit of work. But not too hard. This is one approach.

You can use one or two unbound combo boxes on the form. With two, one would use the year and the second combo box would accept the control number. Then you have to put the two together to retrieve the record. An improvement would be that once a person has selected the year, the second combo box would only include controls for the year.

A variation would be for the second combo box to accept a number, and then use the format command to retrieve the record.

With one combo box, you are limited to entering the year and following through with the typing in the leading zeros and the control number.

Richard

 

[LittleSmudge]

Richard's solution is as good as any for part 1 or your 'problems'

As for "The second problem is, I want to be able to search by the record control number. How do I take the combined field in a form and update it into a table for every new record I enter?
"

You seem to be very confused over what is in Fields and what is in Controls and I think this is adding to your problems.

The data is in FIELDs in the table, you DISPLAY it in CONTROLS on the form.

Once it is in the control ( in the way described by Richard above ) you can search on the entire text string using the standard search/selection techniques.



'ope-that-'elps.


G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[progressiverookie]

Many thanks to both Richard and GLS for the help. I got everything working as planned. Note the stars!

-Jeremy