How can you Change duplicate number if one is found

[Sly600rr]

I have a data entry form with a unique key, A SWO#

I want to be able to search my table to see if a record exists for it, and if it does, change the number entered to the same number with a -1 at the end of it , then check to see if that number exits, untill it finds a unique number to make the new record with..

Private Sub txtSWO_AfterUpdate()
 
Dim newswo As Integer
Dim newswo As String
Counter = counter + 1
NewSWO = Me.txtSWO & "-" & Counter
 
If DCount("*", "tbldata", "SWONumber = '" & Me.txtSWO & "'") = 1 Then
response = MsgBox("Record " & Me.txtSWO & " already exists!! Make this Reworked Record " & newswo & " ?", vbOKCancel, "Duplicated Record!")
        If response = 1 Then
        NewSWO = Me.txtSWO & "-" & Counter
        Me.txtSWO = NewSWO
        End If
txtSWO_AfterUpdate
End If
End Sub

My code works, but it doesn't count right.

Instead of
SWO
SWO-1
SWO-2

It does
SWO
SWO-1
SWO-1-1

My counter wont count and I need to strip off the -1 before I add the -2 but I just can figure out how.

I know that I'm probably just retarded and this is something really simple, please someone help me to stop banging my head into the desk!!

 

[Steve R.]

Without looking at your code too closely, you are adding text to a string, instead of adding +1 to a number. Read up in Access on how to use sting functions such as LEFT and VAL.

I would also suggest that you may need to re-think how you are using your unique SWO number. You didn't identify how it is used.

First, you should have an auto-number that Access assigns automatically. That should be your unique ID for selecting records.

Now if you are using the SWO number to identify a unique product number, that is OK, but do NOT use the SWO number to identify the record. Product numbers can change, so you need to allow for that potential.

For the SWO number enter the tables design view and under indexed select unique value. That should prevent a duplicate number from being created, but won't create a new SWO number. You will still need to program with VBA how the next number is generated.