Update Checkbox status

chinaboy

New Member
Joined
Jun 28, 2010
Messages
27
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.

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

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

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
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

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

HTH
 
Upvote 0
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

Code:
.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.
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.

Code:
Me.chkClasses.Value = Me.cboClasses.Column(3)
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,293
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top