Incrementing field by one

[Jonny]

Increment field by one is not working

I have some code:

Private Sub SamplesReceived_Click()
    Dim SLFIDUpdate As String

    ' avoiding "Write Conflict Error"
    Me.Dirty = False

    ' open modal form with a current record
    DoCmd.OpenForm "SamplesReceived", , , "[SamplesID]=" & Me![SamplesID]

    ' initiate new SLF for new arrived samples, increment last SLF by '1'
    SLFIDUpdate = "UPDATE Samples " & _
                  "SET SLFID = " & CInt(DMax("[SLFID]", "Samples")) + 1 & _
                  " WHERE SamplesID = " & SamplesID

    ' update table 'Samples' , turn updating prompt off
    With DoCmd
      .SetWarnings False
      .RunSQL SLFIDUpdate
      .SetWarnings True
    End With
End Sub

Everytime when I'm clicking this event I want to see that SLFID is incrementing by 1.
The problem is that occur till 9 only and then staying on 10.

 

[pbaldy]

What is the data type of the field SLFID? It sounds like it's text.

 

[Jonny]

You're right, it's text.
I tried to to make CInt(...), but it doesn't help.

Is any way to change it to Number, if I alredy have tables with relations?

 

[pbaldy]

I think you'd have to remove the relationship, change the type on both sides, and add back the relationship. The CInt() should work, but you'd need it inside the DMax(), and there can't be any Null values in that field. That said, since you really want a numeric value, I think the best solution is to change the data type. It's the "pay it now or pay it later" situation.

 

[gemma-the-husky]

you may be better with longs that ints- ints overflow at about 32000