Button to add number based on last number

[seirra]

I have a basic form where jobs are entered, each job gets a job number [Job No] last 2 of the year and the next number in sequence (14-0001, 14-0002 etc.) but only jobs we store get an index number [Index No] "I" for index the whole year and then the next number in sequence (I2014-0001, I2014-0002, etc). What happens at the moment to get a storage number we have to go to the last number and then go back to the current form an input the number plus one. One of the biggest issues is that sometimes people with input the number incorrectly and screw the whole thing up. What I would like to do is have a button that will add the next number.

But since I have no real knowledge on how to go about this I am hope someone here would be able to assist. Or point me in the right direction. I'm sure there is a way of doing it but what happens at the start of a new year?

Thanks in advance for any assistance

 

[Dhathaway]

Hi Seirra,

Next time you have the opportunity, use a separate field to store the "I" indicator (and the year prefix), and leave your ID as an autonumber.

Within your current process, you should be able to have the "indexing" event begin with a Max() function that retrieves the largest identifier already in your database. If you have trouble, I can check-back on this thread later in the week.

Good luck.

David

 

[seirra]

Thanks for the reply
I am unable to use the autonumber ID as part of it because the number is reset every Jan 1 go back to zero.
I am unsure how using the Max() function in this instance.

Thanks again

 

[Dhathaway]

Use Max() to find the largest number already in your table, and then increment that number by one to find your new ID.

You'll want to tie this procedure to the button on your form.

Private Sub Command1_Click()
Dim StrSQL, ID1 As String
Dim Rs As Recordset
Dim ID As Long

'The function in this query is so long, because it needs to extract the numeric value from your identifier
StrSQL = "SELECT MAX(CLNG(RIGHT([TABLE_NAME].[ID_FIELD_NAME],LEN([TABLE_NAME].[ID_FIELD_NAME])-6))) AS MAX_ID FROM [TABLE_NAME]"

'Open the query
Set Rs = CurrentDb.OpenRecordset(StrSQL)

'Find the query's value and increment it by one
ID = Rs.Fields("MAX_ID").Value + 1

'Changes the numeric ID back into a string with up-to 4 leading zeros
ID1 = Format(ID,"0000")

'Do something with ID1

End Sub