Need to increment a number by one

[TiggerDaKat]

I know this is an easy one, I'm new to vb coding.
I have a database that tracks service tickets. When we click a command button to add a new record, we need to have the serviceid field increment it's number by one, but it needs to use the highest number. Any tips would be very much appreciated.

Thanks in advance everyone

 

[dyarwood]

Dim rcd As ADODB.Recordset
Set rcd = New ADODB.Recordset

i=DMax("[Field Name]", "Table Name&quot
i=i+1

rcd.MoveLast

rcd.Fields(a) = i

a is the index of the field. You may have to do rcd.MoveNext after rcd.MoveLast.

dyarwood

 

[TiggerDaKat]

I pasted and modified the code into a command button code.
I get this message when I run it.
"operation is not allowed when the object is closed."

Here's the whole section of code.


Private Sub cmdAddRecord_Click()

On Error GoTo Err_cmdAddRecord_Click
DoCmd.GoToRecord , , acNewRec
'increment service id number
Dim rcd As ADODB.Recordset
Set rcd = New ADODB.Recordset
i = DMax("[ServiceID]", "tblService&quot
i = i + 1
rcd.MoveLast
rcd.MoveLast
rcd.Fields(a) = i
Exit_cmdAddRecord_Click:
Exit Sub
Err_cmdAddRecord_Click:
MsgBox Err.Description
Resume Exit_cmdAddRecord_Click
End Sub

 

[dyarwood]

Try

Private Sub cmdAddRecord_Click()

On Error GoTo Err_cmdAddRecord_Click
DoCmd.GoToRecord , , acNewRec
'increment service id number
Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection
Dim rcd As ADODB.Recordset
Set rcd = New ADODB.Recordset
rcd.Open "tblService", cnn, adOpenForwardOnly,_ adLockOptimistic
i = DMax("[ServiceID]", "tblService&quot
i = i + 1
rcd.MoveLast
rcd.MoveLast
rcd.Fields(a) = i
Exit_cmdAddRecord_Click:
Exit Sub
Err_cmdAddRecord_Click:
MsgBox Err.Description
Resume Exit_cmdAddRecord_Click
End Sub


Don't forget to change a in the rcd.Fields(a) line to the index of the column of the ServiceID

First column is index 0
2nd column is index 1 etc

dyarwood

 

[TiggerDaKat]

It worked!!
In order to have the valie of "i" write into the field on my form, I had to add me![serviceid] = i to the end of the code.
Other than that, it worked well.
Thank you so much for the help!!!

Eric

 

[dyarwood]

Not a problem. Glad you got it sorted

dyarwood