VBA to check a box

[lfarnsworth]

Hello all,

I'm trying to write a bit of VBA code to check a check-box when a certain value to selected in a dropdown box. I've built it as an IF/THEN statement to run an SQL Update query. Here's the code:

Private Sub text0_AfterUpdate()
On Error GoTo Err_text0_AfterUpdate
    Dim frm As Form
    Dim ctl As Control
 
    Set frm = Forms!Form1
    Set ctl = frm!Text0
 
    'DoCmd.SetWarnings False
 
If ctl = "2" Then DoCmd.RunSQL "UPDATE Table1 SET Table1.[yes/no] = 'true' WHERE (((Table1.value)='2'))"
    'DoCmd.SetWarnings True
Exit_text0_AfterUpdate:
    Exit Sub
Err_text0_AfterUpdate:
    MsgBox Err.Description
    Resume Exit_text0_AfterUpdate
End Sub

It almost works... but when I select "2" from the dropdown list for a new record, it updates the Table1.yes/no value for every recond except the new one. Anyone have any tips on how to make it update all the records, including the new one?

Many thanks.

 

[datAdrenaline]

You can not update the "New" record with a SQL statement until the record is committed (saved) to the table.

What you can do is set the New records control that is bound to the field named "Yes/No" to True ...

Private Sub text0_AfterUpdate()
On Error GoTo Err_text0_AfterUpdate
  
If Me.Text0 = 2 Then
    
    CurrentDb.Execute "UPDATE Table1 SET Table1.[yes/no] = True" & _
                     " WHERE Table1.value=2"
    
    If Me.NewRecord Then _
        Me.Controls("Yes/No") = True

Exit_text0_AfterUpdate:
    Exit Sub
Err_text0_AfterUpdate:
    MsgBox Err.Description
    Resume Exit_text0_AfterUpdate
End Sub

....

As a side note, your field name of "Yes/No" is not such a good choice. First off, it has a "special character" (/), plus with a field name like "Yes/No", my first question would be "when its checked what does it mean ... Yes or No?" ... so I would advise a name that has a binary response ... IsClosed, IsSelected, IsPrinted ... etc.

....

 

[lfarnsworth]

You can not update the "New" record with a SQL statement until the record is committed (saved) to the table.

Good to know. Thank you.

Is there a way to run the VBA code when the record is committed, instead of running it after update?

As a side note, your field name of "Yes/No" is not such a good choice.

Thanks, I know. It's just an "experimental table" that I use to play around with the structure of the code. I'll use something different in the real version.

 

[datAdrenaline]

>> It's just an "experimental table" <<

Sounds good ...

>> Is there a way to run the VBA code when the record is committed, instead of running it after update? <<

You can use the FORMs AfterInsert or AfterUpdate event instead of a CONTROLs AfterUpdate.

The AfterInsert event will fire after a new record is committed to the table. The Forms AfterUpdate event will fire after ANY record changes (which includes a New Record) are committed to the table.

 

[lfarnsworth]

Thanks, I'll try that.