Full Version: Set Field using form
UtterAccess Forums > Microsoft® Access > Access Forms
I'm trying to use a form command button to execute an Event which uses code to update a field in the underlying table.
I'm struggling, as the code will not run and I'm unable to debug it. If you can help, Id appreciate it.
The requirements are:
(1) The user should use the form to apply filters as required to restrict the records on screen. (i.e. to restrict the recordset to what they wish to update).
(2) By clicking the Flag command button, the code should update a field in the underlying table to 'x'.
(3) The code should advise the user if a large number (>500) records are about to be updated.
Please note that the setting of the table field to 'x' is an input criteria to several queries which work perfectly. I appreciate that a more experienced access programmer would probably use a different method to achieve the same end and would possibly redesign the entire database. However, it is important to me that the basic process stays the same. I need help to get the code working correctly from a syntax perspective.
From experimentation, it seems to work okay in Access 2000, but using access2002/3, it bugs out with various error messages, such as 'object variable not set'. I have tried the Microsoft help and have tried every syntax I can think of, but I'm stuck.
The code I'm using is:
Private Sub Cmd_FlagSet1_Click()
Dim rst As Recordset
Dim strSQL As String

DoCmd.Hourglass True
DoCmd.SetWarnings False
Set rst = Me.RecordsetClone

If rst.RecordCount > 500 Then
Response = MsgBox("You are about to mark " & rst.RecordCount & " records." & Chr(10) & Chr(13) _
& "Do you want to continue?", vbYesNo + vbCritical, "Mark Records")
If Response = vbNo Then DoCmd.Hourglass False: Exit Sub
End If

While Not (rst.EOF)
With rst
![xAssetLocDrill.Flag1] = "x"
End With

DoCmd.SetWarnings True
DoCmd.Hourglass False
End Sub
Any help is appreciated.
As a starting point, I would make sure that your 2002/2003 DB has the proper libraries referenced in VBA and then explicitly define your recordset, like:

Dim rst As DAO.Recordset
Thanks. I'll start there.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.