How to show results of a VBA Query.

[Guest]

The code below is triggered by a command button in a dialog form. How do I
write some code to show the results of this code. As it is now, I have to use
another form to see the results after the field is renumbered.

Or should I use a form that has a datasheet in it, I tried that but the
records were not updating after running the code.

Option Compare Database

Private Sub cboCabinet_BeforeUpdate(Cancel As Integer)

End Sub

Private Sub cmdCancel_Click()
DoCmd.Close acForm, "Renumber Order Field"
End Sub

Private Sub cmdEnter_Click()

If IsNull(cboCabinet) Then
MsgBox "You must choose a Cabinet."
Exit Sub
End If

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim i As Integer
strSQL = "SELECT * FROM [Folder Labels] WHERE [Cabinet] = '" &
Me.cboCabinet & "' ORDER BY [Number]"
Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
rst.MoveFirst
i = 0
Do
rst.Edit
rst![Order] = i
rst.Update
rst.MoveNext
i = i + 5
Loop Until rst.EOF
rst.Close
Set rst = Nothing
Set db = Nothing





End Sub




Private Sub Form_Load()

End Sub

 

[Steve Schapel]

Tom,

I would expect any form which you open after the code has run to show
the updated values. However, if the form is already open at the time
you run the code, you may need to use a Requery method to display the
changed data.

 

[Guest]

What about if you don't want the query results to be the records on the form?

For example, one query that I am stuck on now is used to change labels on
textboxes!

Regards,

Toby

Tom,

I would expect any form which you open after the code has run to show
the updated values. However, if the form is already open at the time
you run the code, you may need to use a Requery method to display the
changed data.

--
Steve Schapel, Microsoft Access MVP

The code below is triggered by a command button in a dialog form. How do I
write some code to show the results of this code. As it is now, I have to use
another form to see the results after the field is renumbered.

Or should I use a form that has a datasheet in it, I tried that but the
records were not updating after running the code.

Option Compare Database

Private Sub cboCabinet_BeforeUpdate(Cancel As Integer)

End Sub

Private Sub cmdCancel_Click()
DoCmd.Close acForm, "Renumber Order Field"
End Sub

Private Sub cmdEnter_Click()

If IsNull(cboCabinet) Then
MsgBox "You must choose a Cabinet."
Exit Sub
End If

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim i As Integer
strSQL = "SELECT * FROM [Folder Labels] WHERE [Cabinet] = '" &
Me.cboCabinet & "' ORDER BY [Number]"
Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
rst.MoveFirst
i = 0
Do
rst.Edit
rst![Order] = i
rst.Update
rst.MoveNext
i = i + 5
Loop Until rst.EOF
rst.Close
Set rst = Nothing
Set db = Nothing





End Sub




Private Sub Form_Load()

End Sub