Finding the next record

[mlamont]

I have a difficult task to complete. What I need to do is on my form I have a field called Claims ID which is the primary key in the table that is associated with the form. The Claims ID looks like the following: 65-A-2002-01

What I need to do is have the textbox automatically display the next Claims ID number ie 65-A-2002-02. I have no clue where to start.

Can anybody help me?

 

[cogent1]

What Textbox? When do you want the ID to appear? What will you do with it when it does appear? Does it have to relate to an existing record or a new record? More info, purrr-lease!

 

[mlamont]

On the form there is a textbox which the Claims ID will be displayed in, what I need if for when the form loads the Claims ID should automatically show up in the textbox.

When a new record is created I need the textbox to show a new Claims ID number which follows the next number sequence. i.e. if the first Claims ID is 65-A-2002-01, I need the second claims ID to be 65-A-2002-02.

I hope this helps.

 

[cogent1]

Ok, what is the variable part of this ID? is it only the last group of two digits? Can the group continue on to hundreds and thousands? ( e.g.65-A-2002-102.) I need to know this before I can advise you.

 

[mlamont]

Yes it is the last group of digits. I would say that the number could possibly reach in the 1000's but not any higher.

 

[cogent1]

The best way to approach this is to have a command button on the form. When the button is clicked, the numeric part of the last record is stored as a variable,1 is added to it and the 65-A-2002- prefix is appended. A new record is added and the variable is assigned to it.


Private Sub CmdAddID_Click()

Dim Prefix As String
Dim NewID As String
DoCmd.GoToRecord , , acLast
Prefix = Left(ID, 10)
NewID = Right(ID, Len(Me!ID) - 10)
NewID = NewID + 1
DoCmd.GoToRecord , , acNewRec
Me!ID = Prefix & NewID

End Sub

Notes:

The default value of the field can be set to the prefix.

Form should have Views Allowed Property set to Form, Default View to Single Form,Record selectors disabled, Border Style Thin.

Ideally, the Navigation Buttons should be disabled to prevent new records being entered without the code being invoked. If you require to navigate this form, add some customised buttons .
(Many Access books contain the necessary code).

If you want leading zeroes in the numeric group, the code will have to be expanded. Let me know

 

[simongallop]

Guessing that the 2002 bit is the year then to get it to automaticaly shift years then you need to amend the code to:

Private Sub CmdAddID_Click()

Dim Prefix As String
Dim NewID As String
Dim YearTest
DoCmd.GoToRecord , , acLast
Prefix = Left(ID, 9) ' Changed from 10 to get rid of last -
If Val(Right(Prefix,4)) = Year(Date) Then
NewPrefix = Left(Prefix,5) & year(date) & "-1"
DoCmd.GoToRecord , , acNewRec
Me!ID = NewPrefix
else
NewID = Right(ID, Len(Me!ID) - 10)
NewID = NewID + 1
DoCmd.GoToRecord , , acNewRec
Me!ID = Prefix & "-" & NewID 'as prefix now does not have final -

End Sub

HTH