Amending list box items

[Carlos1815]

Hello! Not newbie, but not an expert by any means; I have a
question about reordering items in a list box. I have a table called
tblMainMenu and a form called frmMenuAmend.

tblMainMenu has three fields, ItemID (Number, Primary Key), ItemNumber
(Number) and ItemText (text). The form, frmMenuAmend, has a list box
called MainMenuItems that shows the contents of the table, an Add
button to add a new record on the table and hence it is shown in the
list box; a Delete button that removes a record; Move Up and Move Down
buttons. The Add and Delete buttons work fine, as does the Move
buttons for how the list box was designed. However, I want to do more
than just move an item on a list box and renumber the ItemNum and
ItemID numbers because the illusion is that an item is moved up and
down a list box, but on the actual table itself, the item in ItemText
doesn't move at all, its ItemNumber and ItemID numbers are just
adjusted. I got this from somewhere online, I don't remember.
However, what I'd like to do is have the ItemID numbers adjusted, not
just so a list in the listbox is reordered, but that the actual data
in the table is moved. So for instance, ItemID 1, Orders/Invoice is
moved down one step so it becomes number 2 on the list and the
previous item in the table, Customers Menu, is made item 1. Not only
do I want the numbers reordered on the table, I would like the actual
itemtext, Orders/Invoice, move to the second record and Customers Menu
is moved to the first record to match the ItemIDs. I can accomplish
this if I go into the table and manually change the numbers in the
ItemID field, then close and reopen the table to see the results, but
I would rather have that accomplished using the frmMenuAppend form.

The only way I can see it is by having the code changing the ItemID
(Key) depending on which Move button is pressed, the rest of the data
in the ItemText field will follow (like when doing it manually), but I
can't get the code to do it. Like I said, someone else wrote this
code and while I think I understand it, I can't change it without
errors.

Here is the code for the Move Down Button (the Move Up Button code is
the same, except CP2 is set to CP - 1) :

Private Sub cmdMoveDown_Click()
On Error GoTo ErrHandler

' Declare variables
Dim strsql, strsql2 As String
Dim ItemID, ItemID2 As Integer
Dim CP, CP2 As Integer

If Me.MainMenuItems <> "" Then
' Set CP to equal Item Number of Item Selected
ItemID = Me.MainMenuItems
CP = Me.MainMenuItems.Column(1)
'MsgBox "Current Position " & CP & vbCrLf & "MaxList " &
DMax("ItemNumber", "tblMainMenu")

If CP < CDbl(DMax("ItemNumber", "tblMainMenu")) Then ' Not already
at top of list

CP2 = CP + 1 ' Set Marker to CurrentPosition plus 1
ItemID2 = DLookup("ItemID", "tblMainMenu", "ItemNumber = " &
CP2)
' SQL to reduce ItemNumber of Item Selected by one
strsql = "UPDATE tblMainMenu SET tblMainMenu.ItemNumber = " &
CP2
strsql = strsql & " WHERE [tblMainMenu].[ItemID] = " & ItemID

' SQL2 to increase ItemNumber of Item above current by one
strsql2 = "UPDATE tblMainMenu SET tblMainMenu.ItemNumber = " &
CP
strsql2 = strsql2 & " WHERE [tblMainMenu].[ItemID] = " &
ItemID2

If MsgBox("Are you sure you want to move " &
Me.MainMenuItems.Column(2) & "?", vbYesNo + vbExclamation, "Confirm
Move!") = vbYes Then
CurrentDb.Execute strsql
CurrentDb.Execute strsql2
'Requery list
Me.MainMenuItems.Requery
Else
End If
Else
' Item already at top of list - display message and quit
MsgBox "Cannot move down - already bottom of list!"
End If

Else
MsgBox "Select an Item to Move"
End If

Exit_cmdMoveDown:
Exit Sub

ErrHandler:
MsgBox Error$
Resume Exit_cmdMoveDown
End Sub
-----------------------------------------------------------------------------------------------------

If anyone can see what I'm not seeing or point me in the right
direction to accomplish my goal, I would certainly appreciate it!!
Thank you!

Carlos