Problem with duplicate items in list box and deleting from table 1

[Asspin]

Ok, so I have a table with the following columns:
UserName, ProgramName, OrderInList

What I am trying to do is setup some code to update the "OrderInList" field, everytime a row is deleted.

Here is how I am adding the data to the table...

    DoCmd.SetWarnings False
    DoCmd.RunSQL "INSERT INTO tData (sUser, sProgram, iRow) VALUES ('" & strAlias & "', '" & lstPrograms.Value & "', '" & lstStart.ListCount + 1 & "');"
    DoCmd.SetWarnings True
    lstStart.Requery

lstStart.ListCount + 1 gives me the current row number, which I am using with the UserName to create my primary key, as there may be duplicates with the sProgram field.

Here is how I am currently deleting the records...

    DoCmd.SetWarnings False
    DoCmd.RunSQL "DELETE * FROM tData WHERE sUser ='" & strUser & "' AND iRow =" & lstStart.ListIndex + 1
    DoCmd.SetWarnings True
    lstStart.Requery

strUser is a global variable pulled on load with...

strUser = VBA.Environ("UserName")

There may be an easier way to do what I am trying to do, so I am open to options. Really I am just making a list, unique to each user, which they can edit, but I am having issues because of the duplicate items in the list. Obviously as it sits right now, after you delete one row, the rows after it can't be deleted as the "row number" in the table won't match the row in the list box.

This really feels like a Monday morning issue to me! Grrr!

Dan

 

[Remou]

Is there a reason why you do not wish to use an autonumber to identify the rows? It would save a lot of trouble.

http://lessthandot.com

 

[Asspin]

Because I didn't think about it!

Good catch!

Dan