Solved VBA - Form update boolean field (1 Viewer)

[GC1]

Hi,

I am seeking some assistance to edit my VBA code to update boolean field I have in my table(201Assets). I have a working Delete button and I have copied the code and attempted to make some changes to instead of deleting the record, I would like a boolean field (AssetFlag) to be made True instead.

Private Sub cmdSoftd_Click()
'Hide record
    'check existing selected record
    If Not (Me.subformAssets.Form.Recordset.EOF And Me.subformAssets.Form.Recordset.BOF) Then
        'confirm hide
        If MsgBox("Are you sure you want to HIDE this Asset?", vbYesNo) = vbYes Then
            'hide now
            CurrentDb.Execute "UPDATE 201Assets SET AssetFlag = True _
            WHERE stdid=" & Me.subformAssets.Form.Recordset.Fields("stdid")
            'Refresh data in list
            Me.subformAssets.Form.Requery
       End If
    End If
End Sub

Any assistance would be greatly appreciated

 

[pbaldy]

What's happening/not happening? Do you get an error? I'd make 2 changes. Use a variable for the SQL so you can see how it's ending up:

Debugging

And add an argument to the Execute so it throws an error if there is one. The part to add is in red:

CurrentDb.Execute strSQL, dbFailOnError

 

[Eugene-LS]

I would like a boolean field (AssetFlag) to be made True instead.

You have two erroneous lines in your code.
Replace them with the ones below:

            CurrentDb.Execute "UPDATE 201Assets SET AssetFlag = True " & _
                "WHERE stdid=" & Me.subformAssets.Form.Recordset.Fields("stdid")

 

[arnelgp]

if the table you are updating happens to be the same table as in the subform:

Private Sub cmdSoftd_Click()
Dim db As dao.Database
Dim rs As dao.Recordset
Dim id As Long
Set db = CurrentDb
Set rs = db.OpenRecordset("201Assets", dbOpenDynaset)
With rs
    If Not (.BOF And .EOF) Then
        id = Me.subformAssets.Form!stdid
        .FindFirst "stdid = " & id
         'confirm hide
         If MsgBox("Are you sure you want to HIDE this Asset?", vbYesNo) = vbYes Then
             'hide now
             .Edit
             !AssetFlag = True
            .Update
        End If
    End If
    .Close
End With
Set rs = Nothing
Set db = Nothing
Me.subformAssets.Form.Recordset.Requery
End Sub

 

[GC1]

You have two erroneous lines in your code.
Replace them with the ones below:

            CurrentDb.Execute "UPDATE 201Assets SET AssetFlag = True " & _
                "WHERE stdid=" & Me.subformAssets.Form.Recordset.Fields("stdid")

Thanks Eugene-LS, this solved the problem