Refresh data in list box on form 1

[vamoose]

I am using Access 2000 and Windows XP for this example. I have a database called (Today.mdb) and it contains 1 query (Line) and 1 form (Form1). When the form opens it runs the query (Line) to retrieve and sort the data in table (Daily) and display it in a list box (List2) on form (Form1). This works well so far. The list box (List2) has 10 columns and approximately 20 records which are displayed. I have added up and down arrow control buttons which allow the user to select a specific record and move it up or down in the list box window. I use a column in table (Daily) which contains a number assigned to each record, similar to autonumber and order by this number in the query. This is also working well. So my problem is that the record does not actually shift locations unless the form is closed and re-opened. Is there a way to run a requery or update the data displayed on the form without actually closing and reopening the form ?
Thank you for any and all advice. Regards.

 

[Crowley16]

me.requery

--------------------
Procrastinate Now!

 

[vamoose]

I have tried Me.Requery as the last line in the VBA code, nothing moves up or down in the list box on the form.

and

DoCmd DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70 but this tells me Run-time error '2046': "The command or action 'Refresh' isn't available now.

Am I headed in the right direction ? Thank you.

 

[vamoose]

Here is the actual code I am using and it works somewhat. The issue is now is that the information in the list box does not seem to update very rapidly. It does update sometimes and not others. And when it does update the list box there are duplicate numbers in the first column (Auto) which should be sequential, 1,2,3,4,5,6 etc...
Hopefully the error is obvious to someone more experienced than I. Thank you.

CODE USED:
Option Compare Database
Dim SQLa As String: Dim conn As ADODB.Connection: Dim rs As ADODB.Recordset:

Private Sub Form_Close()
conn.Close: Set rs = Nothing: Set conn = Nothing
End Sub

Private Sub Form_Open(Cancel As Integer)

Set conn = New ADODB.Connection
conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= C:\Temp\4-16-2007.mdb" & ";" & _
"Persist Security Info=False"
conn.Open

End Sub

Private Sub MoveUp_Click()

If Me![List2] <> "" Then
Move = Me![List2]
Else: MsgBox "Please select a line item from the list ", vbOKOnly + vbCritical, "Organize run data": Exit Sub
End If

SQLa = "Select Auto from Copy where '" & Move & "' = Material"
Set rs = conn.Execute(SQLa, , adCmdText): Num = rs.Fields("Auto").Value

NumUp = Num - 1
If NumUp = 0 Then MsgBox "Cannot move this item up any further ", vbOKOnly + vbCritical, "Organize run data": Exit Sub

SQLa = "Update Copy Set Auto = " & Num & " Where Auto = " & NumUp
Set rs = conn.Execute(SQLa, , adCmdText)

SQLa = "Update Copy Set Auto = " & NumUp & " Where Material = '" & Move & "'"
Set rs = conn.Execute(SQLa, , adCmdText)

Me.List2.Requery

End Sub
Private Sub MoveDown_Click()

If Me![List2] <> "" Then
Move = Me![List2]
Else: MsgBox "Please select a line item from the list ", vbOKOnly + vbCritical, "Organize run data": Exit Sub
End If

SQLa = "Select Count(Material) as Total from Copy"
Set rs = conn.Execute(SQLa, , adCmdText): TNum = rs.Fields("Total").Value

SQLa = "Select Auto from Copy where '" & Move & "' = Material"
Set rs = conn.Execute(SQLa, , adCmdText): Num = rs.Fields("Auto").Value

NumUp = Num + 1
If NumUp > TNum Then MsgBox "Cannot move this item down any further ", vbOKOnly + vbCritical, "Organize run data": Exit Sub

SQLa = "Update Copy Set Auto = " & Num & " Where Auto = " & NumUp
Set rs = conn.Execute(SQLa, , adCmdText)

SQLa = "Update Copy Set Auto = " & NumUp & " Where Material = '" & Move & "'"
Set rs = conn.Execute(SQLa, , adCmdText)

Me.List2.Requery

End Sub

QUERY USED in Row Source of Form, List2:

SELECT [Copy].[Auto], [Copy].[Material], [Copy].[Plant], [Copy].[Target Quantity], [Copy].[Target Quantity1], [Copy].[Line], [Copy].[Packing], [Copy].[Label], [Copy].[Rev], [Copy].[Paper] FROM Copy ORDER BY [Copy].[Auto];