Custom ID Entry help (1 Viewer)

[BassFishinJunkie]

Hi! Hopefully someone can help me here. I was given a project from my MGR and have made it more complicated then I first intended.
I am creating a database for leaders throughout the company to log defective product.
When each entry is created I would like to assign them a unique ID code that would look like this:
01-XXX-2020
02-AAA-2020
03-CCC-2020

First two digits are sequentially and would be formulated by access based off last record entry.
3 digits after dash are selected by user from combo box and are the "detection Location".
last 4 digits are the year.

hopefully I can ask how to without confusing the experts (because it confuses me.

Users will select the detection Location from a combo box.
Users will select the Year from a combo box (current year will be default so they actually wont ever change anything here).
Once the selections are made, the user will push a button that says "create record".

When the user clicks the button, I want two things to happen and this is what I need help with the code:
1.) Access to create the Id from what they selected with the sequential numbering system. (Based off last entered records number).
2.) Open up another form with the Id is just created in a text box and there will be other entry fields they must fill out before saving.

 

[pbaldy]

You won't have more than 99 records a year? I'd store the sequential number and the location separately. Presumably you already have a year or date field. You can use DMax() to find the largest sequential number for the year and add one to it. Concatenate that with the location and year when presenting to the user. This can open the second form:

Open a second form to the record

 

[BassFishinJunkie]

I will have more than 99 records in a month. I have a separate table that I have years listed for the next 10 years. Dates are a selection when you open the 2nd form.


I thought about doing the stored separately deal and then concatenate them but, with using the auto number function, it does not reset at the new year. I figured if I did it with selecting the year as the Id is made, it would be reset.

 

[plog]

I will have more than 99 records in a month

That's over 1200 records per year. Your format (01-XXX-2020) has allocated only enough positions for 100 records.

I have a separate table that I have years listed for the next 10 years.

I don't follow, nor know how that's germane.

... but, with using the auto number function, it ...

You will not be able to use an autonumber. But you will ultimately concatenate your 3 pieces together. That means the real issue is generating a reseting number (the first piece). That has been done numerous times on this site. A quick search pulled up this one:

Custom ID to reset by year

Hi All, First of all, thank you for taking the time to read this. I know there are many threads across the internet on the topic of custom IDs (I think I have read them all!) and I know its a bad idea to start with!... That being said I am updating a database for work (I work in a laboratory)...

www.access-programmers.co.uk

If you use the search functions you will find more.

 

[zeroaccess]

I would still use an autonumber primary key field and make this custom field separate from that.

 

[arnelgp]

on the sample db, on the subform, autonum is autonumber, ID is the custom-generated ID.
these fields are Locked to prevent users from editing.
see the code on the Subform's BeforeInsert.
see Module1 for the custom-id generator.