Update Checkbox status

[chinaboy]

Hi all, I know some of what I am doing is not the best possible solution but work with me. I have combobox that is populated from an access table. That works great. Now I have a checkbox that I use to update the checkbox field(Yes/No) in the same table based on the combobox selection. That too works great. My problem is I can't get the checkbox on the excel userform to reflect the correct status of the checkbox field based on the combobox selection. I can update with out problems, but can't always know what the status is. Can you help address this? The checkbox field value is in column 3 of the combobox if that helps.

Private Sub Classcbo()

    Dim src As String
    Dim i As Integer
    
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    
    'connect to the access database
    Set cn = New ADODB.Connection
    myConn = TARGET_DB
    With cn
    .Provider = "Microsoft.ACE.OLEDB.12.0"
    .Open myConn
    End With
    
    Set rs = New ADODB.Recordset
    
    src = "SELECT * FROM tblClasses "
    rs.Open src, cn, adOpenDynamic, adLockBatchOptimistic
    
    
    With Me.cboClasses
        .Clear
        .ColumnCount = 6
        .Column = rs.GetRows
        .ListIndex = -1
        Font.Size = 12
    End With
'End If


'Close ADO objects
rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing


End Sub

Private Sub chkDisable_Click()

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim myConn As String
Dim src As String


    ' connect to the Access database
    Set cn = New ADODB.Connection
    myConn = TARGET_DB
    With cn
     .Provider = "Microsoft.ACE.OLEDB.12.0"
     .Open myConn
   End With
       
    Set rs = New ADODB.Recordset
    
    src = "SELECT * FROM tblClasses WHERE ID=" & Me.cboClasses.Column(5) & ""
    
    rs.Open src, cn, adOpenKeyset, adLockOptimistic, adCmdText
    
     
    If Me.chkClasses.Value = True Then
        .Fields("disClass").Value = True
        Else: .Fields("disClass").Value = False
    
    End If


        rs.Update
    End With
        
    rs.Close
    cn.Close
        
    Set rs = Nothing
    Set cn = Nothing
    Exit Sub
        
End Sub

 

[welshgasman]

Aren't you missing a With for the rs recordset?
Plus is ID is numeric no need for the last set of quotes I would have thought.

Have you tried walking through the code with F8 ?
Could you not just use

.Fields("disClass").Value =  Me.chkClasses.Value

HTH

 

[chinaboy]

Aren't you missing a With for the rs recordset?
Plus is ID is numeric no need for the last set of quotes I would have thought.

Have you tried walking through the code with F8 ?
Could you not just use

.Fields("disClass").Value =  Me.chkClasses.Value

HTH

Thank you for assisting. I am by no means an expert and just learning as I go. I some how omit the With for the recordset in my post, but do have it in my code. Using the f8, I did, but because of my limited knowledge that did not help. I am not getting errors, don't have the no how to lay it out. I tried your code and it still doesn't reflect a check in the box. It does put the check in the table.

Thanks again.

 

[welshgasman]

Ok, not something I have had to do yet.?

However if controls are not bound (as yours are effectively not) then I believe you need to requery after any update.?

So I *think* you would need to call Classcbo each time to get the updated values?

As an aside. allways copy and paste code so we get to see what is *actually* being used.

 

[chinaboy]

Ok, not something I have had to do yet.?

However if controls are not bound (as yours are effectively not) then I believe you need to requery after any update.?

So I *think* you would need to call Classcbo each time to get the updated values?

As an aside. allways copy and paste code so we get to see what is *actually* being used.

Hi...so seems like I should bound. How would I go about doing so?

 

[welshgasman]

No, bound controls refer to a form in Access. You are working in Excel and connecting to Access, so to my way of thinking these are unbound controls, and need to be refreshed.?
Effectively though, after you update anything in the Access DB, you would need to requery the source in Excel to get the latest updates, especially if in a multi user environment.

As I mentioned, something I have yet to do. All of my little experience is vba within Access.

 

[chinaboy]

Ok.. Thank you very much. I haven't been able to populate the box at all, and thats my main reason for requesting help. But once I am able to do so, then I will be sure to include a refresh command.

 

[chinaboy]

Thank you guys for advice and suggestions. I was able to get the status in the checkbox to show correctly by placing the code below in the change() event of the combobox.

Me.chkClasses.Value = Me.cboClasses.Column(3)