Solved VBA - Form update boolean field (1 Viewer)

GC1

New member
Local time
Today, 22:46
Joined
Apr 6, 2020
Messages
17
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.

Code:
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

Wino Moderator
Staff member
Local time
Today, 15:46
Joined
Aug 30, 2003
Messages
36,118
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:


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
 
  • Like
Reactions: GC1

Eugene-LS

Registered User.
Local time
Tomorrow, 01:46
Joined
Dec 7, 2018
Messages
481
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:
Code:
            CurrentDb.Execute "UPDATE 201Assets SET AssetFlag = True " & _
                "WHERE stdid=" & Me.subformAssets.Form.Recordset.Fields("stdid")
 
  • Like
Reactions: GC1

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:46
Joined
May 7, 2009
Messages
19,169
if the table you are updating happens to be the same table as in the subform:
Code:
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
 
Last edited:
  • Like
Reactions: GC1

GC1

New member
Local time
Today, 22:46
Joined
Apr 6, 2020
Messages
17
You have two erroneous lines in your code.
Replace them with the ones below:
Code:
            CurrentDb.Execute "UPDATE 201Assets SET AssetFlag = True " & _
                "WHERE stdid=" & Me.subformAssets.Form.Recordset.Fields("stdid")
Thanks Eugene-LS, this solved the problem :)
 

Users who are viewing this thread

Top Bottom