Auto-incrementing id number, that resets to 1 each year, with a year prefix

[cantarski]

What I want to do is create an incrementing id that resets to 1 every year with a prefix. The prefix is "10yy/", where 10 refers to the table and "yy" is the current year. For 2015 I want it to go:
1015/0001
1015/0002
etc.
In 2016 I want it to go:
1016/0001
1016/0002
etc.
The table is "RoadIM" and the id field is "RIMID"
My code is:

[B]Private Sub Form_BeforeInsert(Cancel As Integer)
Dim vLast As Variant
Dim iNext As Integer
vLast = DMax("[RIMID]", "[RoadIM]", "[RIMID] LIKE "[COLOR="Red"][SIZE="6"]I don't know[/SIZE][/COLOR]")
If IsNull(vLast) Then
iNext = 1
Else
iNext = Val(Mid(vLast, 4)) + 1
End If
Me![RIMID] = "10" & Format(Date, "yy") & "/" & Format(iNext, "0000")
End Sub
[/B]

Every valid expression I have put after the LIKE has resulted in every new record taking the 1015/0001 id.
What I want to put after the LIKE is something that means "10yy/*", where "yy" is the current year, so that it checks the last entry from the current year.

I would really appreciate any help, because this has caused me hours of frustration, since I'm new to basic.
Thank you!

 

[plog]

Let me be the first to harass you with this question: Why?

Is this id going to be used externally to the database? Will people in meetings reference 1015/0712 and others know what they are talking about? In short, does this really serve a real need?

Regardless, the way to accomplish what you want is to not to not do it all at once, nor in one field (RIMID). You wouldn't have one field in your table that stores this value (e.g. "1015/0712"). Instead you would store it in 2 fields--1 for each discrete piece of data that goes into making it. Let's call those RIYEAR and RIINC.

In RIYEAR you would store the actual year (e.g. 2015, 2016, etc.). Then in RIINC you would hold the incrementing portion. This would be an integer data type and store values like such: 1, 2, 3...712...). Then to get the next RIINC value its a simple DMAX call using RIYEAR as criteria.

Then, when you wanted to display this id, you would create a function that you pass both RIYEAR and RIINC values. It would then construct your RMID in the manner you prescribed.

Again, though, why?

 

[cantarski]

Firstly, thank you for your reply.

I need to format the id like that because the database will be used in another program that stores the records in that manner.

Your 2-field solution seems much more logical that the 1-field clusterfuck. Most of the code I posted was copied from a thread from another forum with a similar issue, because, like I said I'm new to basic (started today).

Would you be so kind as to explain to me where I should put the dmax function in the form field, and what it would contain?
I know I'm asking almost everything spelled out, but I'm really clueless here.

Thanks again for your time!

 

[plog]

I'd start by learning what your code is doing. It's 90% of what you need, you just need to customize it to your new id fields (RIYEAR and RIINC fields). Dmax (http://www.techonthenet.com/access/functions/domain/dmax.php) gets the largest value of a field, in a table that meets your criteria.

Your's would look like this:

vLast=Dmax("[RIINC]", "RoadIM", "[RIYEAR]=" & Year(Date))

 

[cantarski]

Thanks!
I will try it.

 

[cantarski]

Here's where I am:

In the table RoadIM, I created the number field [RIMInc] with the format 0000. I can't create the RIMYear field in the table because the table can't take the variable Date() function.

In the query qryRoadIM, I created the field RIMYear: Right(Year(Date());2)

Then I created a form with qryRoadIM as a source and the non-visible field RIMInc and I wrote the code:

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim vLast As Variant
Dim iNext As Integer
vLast = DMax("[RIMINC]", "[qryRoadIM]", "[RIMYEAR]=" & Right(Year(Date), 2))
If IsNull(vLast) Then
iNext = 1
Else
iNext = vLast + 1
End If
Me![RIMINC] = Format(iNext, "0000")
End Sub

Lastly, I have the field RIMID ="10" & [RIMYear] & "/" & Format([RIMINC];"0000"), which is the only visible id field.

All this works up to a point, but the big problem is that, when the year changes, all the RIMYear entries change to the current year, because RIMYear is a query field. As a result, the RIMInc can't reset yearly.

How can I lock the records of the previous year so that, when the year changes, RIMInc becomes 0001 again?

Thanks again.

 

[plog]

I can't create the RIMYear field in the table because the table can't take the variable Date() function.

Huh? You should be able to create an integer field in your table.

Here's a few things I see with that function:

1. Your Dmax shouldn't be looking into a query, it should look into a table.

2. You never calculate the RIMYEAR value. You need to determine it in the function and set it on the form.


3. RIMINC doesn't need to be formatted--its not visible. Why put on clothes if no one is coming over?

As for reseting the next year, your function needs to figure that out. If your Dmax returns no values, well there you go, you know you need to start at your beginning value.

 

[cantarski]

OK I (you) did it!

Correct code looks like this:

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim vLast As Variant
Dim iNext As Integer
[B]Me![RIMYear] = Right(Year(Date), 2)[/B]
vLast = DMax("[RIMINC]", "[RoadIM]", "[RIMYEAR]=" & Right(Year(Date), 2))
If IsNull(vLast) Then
iNext = 1
Else
iNext = vLast + 1
End If
Me![RIMINC] = iNext
End Sub

You're great!

 

[cantarski]

One more issue, that I don't know if it can be solved:

When I try to paste more than 1 records on the form, they all take the id 1015/0001, because the function BeforeInsert runs once for all of them.

Any ideas?

 

[plog]

I'd reevaluate how you are doing data input. Why are you pasting and what? Can you post a screenshot of your form? How many are you pasting at once?

 

[cantarski]

I'm pasting from an excel that has the 2015 records up to now.
There are 196, so manual copying is pretty much ruled out.
I'm attaching the empty database that I used in order to figure this out. I have only put in a name field, so that I can simulate the whole situation.

 

[plog]

You shouldn't be using a form for this. You should use a staging table somehow. Either import the data directly there or past it in there. Then run an UPDATE query to move it to your main table.

I really don't know what your data is for or what your process is so I can't really comment specifically. But a form isn't the most efficient way to do this.

 

[cantarski]

OK. I pasted the records to the table with the 2 separate fields (Inc and Year) and the form was updated fine.

I think that's all. Thanks again for all your help and patience.

 

[jai kushwaha]

sorry i read all the things above but i really new to the database in ms access. And same as above i also want to make the Id no like Year+Idno in my form what to do now?

 

[jai kushwaha]

one more thing how to post a new quote here friends????

 

[cantarski]

In order to reset the incrementing number yearly, here's what I ended up doing, with the help of plog:

In a table I created the number fields (Long Integer) [Inc] and [fldYear].

In a query based on said table I created a calculated field ID: [FldYear] & "/" & Format([INC];"0000") , that will be the only ID number the user will see. Obviously, if you want the INC number to be formatted otherwise, you can change the Format function or not use it at all.

In the form, which will be the way records will be accessed and created, I wrote the code:

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim vLast As Variant
Dim iNext As Integer
Me![fldYear] = Right(Year(Date), 2)
vLast = DMax("[INC]", "[YourTable]", "[fldYEAR]=" & Right(Year(Date), 2))
If IsNull(vLast) Then
iNext = 1
Else
iNext = vLast + 1
End If
Me![INC] = iNext
End Sub

To write this code for the form (one of many ways), you open the form in design mode and, in the design tab, you click on the "View Code", on the very right.

This code will run every time you try to fill a new record.
First, it will set the field [fldYear] to the current year.
Then it will check if there is another record with the same [fldYear].
If there isn't, it will set the [INC] field to 1.
If there is, it will set the [INC] field to the next integer.

In short, the records will go like this:

15/0001, 15/0002, 15/0003....16/0001, 16/0002, 16/0003....

As for the quote thing, there is a quote button after every reply, or you can copy paste the text and put [ QUOTE] and [/ QUOTE] around it (without the spaces before the Qs).

 

[jai kushwaha]

I'm pasting from an excel that has the 2015 records up to now.
There are 196, so manual copying is pretty much ruled out.
I'm attaching the empty database that I used in order to figure this out. I have only put in a name field, so that I can simulate the whole situation.

Thank you friend it is working fine but i want to ask one more thing that i want to change the id no again from start with the change of year. my mean is that if the year change 2015 to 2016 and id no is 2015/0056 it will also change 2016/0001 and again the id no increase automaticaly. thank you again dear.....

 

[jai kushwaha]

i am sorry i gt my answer. thank you again.....

 

[jai kushwaha]

one more thing i want to ask fyoou friend... i have a table and i am using it for combo box in my form and i want if i choose the "canine" field than another text box "species_name"will enable to edit but if i choose any other it will disable.

i use
after update cbo_species
if [cbo_species] = "canine" then
Species_name.enable = true

else
Species_name.enable = false
end if
end sub



but it is not working
i dont know why it is not working when i choose any of the field from the combo the textbox "species_name" disabled.
in short in all condition the textbox disabled it does'nt matter weather it is "canine" or any other field i am choosing from the combo box , its not working

Thank you in advanced...

 

[AccessBlaster]

sorry i read all the things above but i really new to the database in ms access. And same as above i also want to make the Id no like Year+Idno in my form what to do now?

Have a look here for some ideas. http://regina-whipp.com/blog/