Primary key: text, year and number increment

[gobogirl]

Hi. I am trying to create a primary key to replace "autonumber". Ideally, it will still auto-increment:

GBQ04-001
GBQ04-002
GBQ04-003

GBQ is an identifier
04 is the year
001 is the increment

The form has a "Date Entered" field.

For the first record of any new year, the year should change accordingly and the incrementing number will start at 001 again. The identifier will remain constant.

I will need to enter records from 2002 to present. Thus when i start entering the final data, i will need to be able to type into the field with the Primary Key until i reach the present. From then on, it should be fully automated.

I've tried Help in Access and also tried searching the archives here, but end up scratching my head. Can someone help or guide me? I'm keen to learn and would greatly appreciate any help especially with a what/where/how/why I put the solution.

Should this be created in a table instead of a form?

I've tried this in a form:

Private Sub Form_BeforeInsert(Cancel As Integer)
Me!GBQQuoteNo = Nz(DMax("[GBQQuoteNo]", "[tblGBQQuotes]"), 0) + 1
End Sub

- it's not working.

I think my primary key should be created in a table instead.

Please help.

 

[Treason]

I think the only method you can go to achieve this is through a form. But once you go form... you never go back *grin*.

I am not too sure on what event this should go, and that all depends on how your form is setup.. I for one almost always use unbound forms.

Anyway here is a working piece of code that will determine the next # in sequence. Again how you get this into your database through your form is something else.

Me.TextBox1 = "GBQ" & Format(Date, "yy") & "-" & Format(CInt(DMax("Right([GBQQuoteNo],3)", "tblGBQQuotes")) + 1, "000")

(Assuming tblGBQQuotes is you table and the GBQQuoteNo field contains data like GBQ04-003.)

Keep in mind you are leaving no option if the client has more than 999 jobs in a year... just something to consider.

good luck

 

[gobogirl]

Will the numbers revert back to 001 in the new year?

Thank you, Treason!
It works a treat.

Will this code force the Quote numbers to revert back to 001 for the first one created in a new year (eg GBQ05-001)?

BTW - they don't create more than 500 quotes a year.

Cheers.

 

[gobogirl]

Oops! Sorry, it's not working ater tests.

After trying the code the first time it seemed to work (i had some previous test entries in the database from my poor attempts to create the sequence). After i posted my "Thank you", i then tried creating more test entries. The numbers didn't increment (ie i had 5 entries in a row each with quote number GBQ04-002). Obviously, i haven't yet set the field as a primary key.

To continue testing, I deleted all test records and compacted the database then tried entering a new record. I am getting the debugger.

I have tried placing the code into:
1. The Form's Before Insert
2. txtGBQQuoteNo - On Enter
3. txtGBQQuoteNo - On Click
4. txtGBQQuoteNo - Before Update

Can anyone help....?

 

[Treason]

well, what I posted for you will only auto-increase if there is already some value in the field. you need alot more code to check the year... yadda yadda yadda.

If you are getting duplicates it's because of how your form is set up. Is this a bound form? I can't really help without nowing how you want to trigger a new record. Is it a command button that will dump all the info on the form into a table? Or, Is it a form bound to the table.

BTW, This is much easier to do with an unbound form, but it requires alot more coding on your part.

Post a sample DB... I'll look at it for you.