Setting CheckBox value in form using VBA/Event

[Monterey_Manzer]

Hi All,



Okay I know this shouldn't be so difficult but apparently it is. I am trying to set the value of a checkbox on one of my forms based on other information on that form. The code is pretty simple, some variation of:

If control A = criteria Then
   Me.CheckBox.Value = -1
Else
   Me.CheckBox.Value = 0
End If

This is set to the On Current event.

I've seen some posts saying you can't set values of checkboxes or other controls after the form has loaded but here is the confusing part, I have another form that does almost the exact same thing and it works just fine. The code for that is shown below:

Private Sub btnACC_Click()
    Dim strSQL, strINSERT As String, rst As DAO.Recordset, db As DAO.Database, Response As Variant
    
    ' Collecting any Outside Employment entries that are part of this Annual Report
    strSQL = "SELECT * FROM ARACC WHERE ARACC.ARPTID = " & fARPTID & ";"
    
    Set db = CurrentDb
    Set rst = db.OpenRecordset(strSQL)
    ' If there is at least 1 Accomplishment record then we just go to the form
    If Not rst.BOF And Not rst.EOF Then
        GoTo FormOpen
    Else ' If there are no records of Outside Employment for this annual report, we initialize one
        'Checking to make sure this is what they meant to do
        Response = MsgBox("No Accomplishment records exist for this Annual Report.  Do you want to create one?", _
                    vbYesNo + vbQuestion, "No Current Records")
           Select Case Response
                Case vbYes ' If we want to add a record, we add it
                    strINSERT = "INSERT INTO ARACC (ARPTID) VALUES (" & fARPTID & ")"
                    'Debug.Print strINSERT
                    CurrentDb.Execute strINSERT
                    Me.ARACC.Value = -1
                    GoTo FormOpen
                Case vbNo 'Whoops! My mistake, let's get out of here
                    GoTo MyExit
                Case Else
                    GoTo MyExit
           End Select
    End If

    
    
MyExit:
    Set db = Nothing
    Set rst = Nothing
    Exit Sub
    
FormOpen:
    Set db = Nothing
    Set rst = Nothing
    DoCmd.OpenForm "frmARACC", acNormal, , "[ARPTID] = " & fARPTID & ""
End Sub

See that Me. ARACC.Value = -1? Yup, that's my checkbox control and this code effectively sets the value of that checkbox with no hangups. Why does it work without a hiccup here but not before? I'd really appreciate any light anyone might feel like shedding on this.

Thanks!
Ryan

[June7]

What is the exact code that fails?

[Monterey_Manzer]

Me.CheckBox.Value = -1

I get run-time error 2448: "You can't assign a value to this object"

[June7]

The actual name of the control is 'CheckBox'? Well, I certainly don't see anything wrong with that syntax! What is the complete procedure? If you want to provide db for analysis, follow instructions at bottom of my post.

The .Value is unnecesary because it is the default property of data controls.

[Monterey_Manzer]

The complete code is below:

Private Sub txtTransRec_AfterUpdate()
    
    If Me.txtTransRec.Value = "Yes" Then
        Me.TransRec = -1
    ElseIf Me.txtTransRec.Value = "No" Then
        Me.TransRec = 0
    End If
End Sub

pretty short and sweet, as I mentioned. I went ahead and removed the .Value per your suggestion.

[June7]

The .Value doesn't hurt, could have left, just save yourself a couple keystrokes in the future.

Can't bind txtTransRec to TransRec field?

Is txtTransRec formatted for Yes/No value?

Then:

If Me.txtTransRec = True Then

or simpler, no If Then Else needed:

Me.TransRec = Me.txtTransRec

[Monterey_Manzer]

Thanks June!

I actually had a brain wave yesterday as I puzzled over this and would appreciate your assessment. I realized that I was adding controls to this form because I needed to set the values those controls were bound to due to something in the form, but I did not necessarily need to see that control or have users directly manipulate it. I have been applying old (i.e. bad) habits from when I understood Access even less than I do now. My fix was to delete the TransRec checkbox and simply set the value of that field via an SQL string and the CurrentDb.Execute method.

My question for you is that, in general, is it preferable to use things like SQL statements and either the CurrentDb.Execute method or DAO recordsets to set table values and retrieve query data rather than adding extra controls such as checkboxes and comboboxes? I assume so since whenever I run the Analyze Performance tool on my forms it always tells me to reduce my controls but I would like to get an expert opinion. I currently have a combo box that returns a set of four values related to each record in this table that I use on this form to help set the TransRec value but I wonder if simply using a DAO recordset approach would be better.

With regard to my code for setting the TransRec value without including the checkbox on the form (still using the combobox), I will show the code below in case it helps someone else later on. This is within the On Current event which has a bunch of other, non-relevant, code so I will not post the entire subroutine but just the parts that pertain to this issue.

' collecting the ID number for this record to use with the update query
' trapping the Invalid Use of Null error
   If IsNull(Me.RecID.Value) Then
        GoTo MyExit
   End If

    RecID = Me.RecID.Value
    
    strSQL = "UPDATE ProbTracker SET TransRec = -1 WHERE [ID Number] = " & RecID & ";"
' notifying user if transcripts for the Probation term are in and recording it in the Probation table
    Select Case Me.ProbationTermcbo.Column(0)
        Case "Fall"
            If Me.Transcriptscbo.Column(1) <> "" Then
                Me.txtTransRec.Value = "Yes"
                CurrentDb.Execute strSQL
            Else
                Me.txtTransRec.Value = "No"
            End If
        Case "Winter"
            If Me.Transcriptscbo.Column(2) <> "" Then
                Me.txtTransRec.Value = "Yes"
               CurrentDb.Execute strSQL
            Else
                Me.txtTransRec.Value = "No"
            End If
        Case "Spring"
            If Me.Transcriptscbo.Column(3) <> "" Then
                Me.txtTransRec.Value = "Yes"
                CurrentDb.Execute strSQL
            Else
                Me.txtTransRec.Value = "No"
            End If
        Case "Summer"
            If Me.Transcriptscbo.Column(4) <> "" Then
                Me.txtTransRec.Value = "Yes"
                CurrentDb.Execute strSQL
            Else
                Me.txtTransRec.Value = "No"
            End If
    End Select

If anyone sees any glaring errors with this approach I would be quite happy to hear them. Hopefully this is a valid method for solving the problem and the above code will help some other Access newbie or the community can hash out some other method for retrieving/setting these values that is better and we can pass that along.

Thanks!
Ryan

[June7]

Saving data based on value in other field(s) is contrary to conventional wisdom. Calculate when needed. Having said that, there can be situations that justify saving calculated data, however, I am not sure this is one of them.

If the field that needs to be populated is included in the form's RecordSource then why would SQL action be needed?

I just still do not understand your db well enough to give specific recommendation.