Code to Update Checkbox on Table

**UTLee** · 10-08-2014, 08:32 AM

Need some help and have very limited programming knowledge. I have created the following:

- a form with a listbox (List6) that shows a list of all clients (along with other data not relevant to this question).

- a query (qryPortfolioReview) that displays the same list of clients and also two fields from tblClients: PortfolioReviewed (a yes/no checkbox field) and DateofLastReview (a date/time field). - I'm not sure I even need this for my question.

- a button (Command18).

I would like to set the On Click event of the command18 button to put a check in the tblClients.PortfolioReviewed field for the list of clients that I have selected in List6, and also add today's date to the DateofLastReview field. I have some code (below) that someone else created which runs a report from the selected list of clients in List6 and I was hoping I could simply alter it to update the two fields mentioned above.

Code:

Dim i, s As Integer    Dim strfilter As String
    For i = 0 To Me.List6.ListCount
        If Me.List6.Selected(i) = True Then
            s = s + 1
            If s = 1 Then strfilter = "ClientID = '" & Me.List6.Column(0, i) & "'"
            If s > 1 Then
                strfilter = strfilter & " or " & "ClientID = '" & Me.List6.Column(0, i) & "'"
            End If
        
        End If
    Next
    If s = 0 Then Exit Sub
    
    If Len(strfilter) > 255 Then
        MsgBox "You must select fewer clients. Try selecting 5 to 10 at a time."
        Exit Sub
    End If
    
    DoCmd.OpenReport "rptAllAccountsActivity", acViewPreview, "qryRptAllAccountsActivity", strfilter

Any ideas? Thanks in advance!

**ranman256** · 10-08-2014, 08:52 AM

Note: you can name these buttons / listboxs BEFORE coding so they are easily make sense, instead of command6, or list6
place a button on form, properties box, other tab, NAME = btnMark , (or similar) lstEmps

anyway, update everyones marker status as you go thru the list...

Code:

    For i = 0 To Me.List6.ListCount
      sSql = "Update [table] Set [marker] = " &  Me.List6.Selected(i) & " where clientid = " &  List6.Column(0, i)
        docmd.runsql sSql    

    If Me.List6.Selected(i) = True Then

**UTLee** · 10-08-2014, 09:46 AM

Thanks for your reply. So I changed the button name from Command18 to cmdMarkReviewed. Thanks for the tip.

I added your code but its not working. I get a pop up stating "enter parameter value ClientID"

Here is what the resulting code looks like and I'm not sure if I modified my original code properly with your suggestion:

Code:

Private Sub cmdMarkReviewed_Click()Dim i, s As Integer
    Dim strfilter As String
    For i = 0 To Me.List6.ListCount
       sSql = "Update tblClients Set tblClients.PortfolioReviewed = " & Me.List6.Selected(i) & " where ClientID = " & List6.Column(0, i)
        DoCmd.RunSQL sSql
        
        If Me.List6.Selected(i) = True Then
            s = s + 1
            If s = 1 Then strfilter = "ClientID = '" & Me.List6.Column(0, i) & "'"
            If s > 1 Then
                strfilter = strfilter & " or " & "ClientID = '" & Me.List6.Column(0, i) & "'"
            End If
            
        End If
    Next
    If s = 0 Then Exit Sub
    
    If Len(strfilter) > 255 Then
        MsgBox "You must select fewer clients. Try selecting 5 to 10 at a time."
        Exit Sub
    End If
    
End Sub

**ranman256** · 10-08-2014, 11:36 AM

Since I dont know your tables...I assume ClientID are numeric. But I dont even know if thats the field name. You will.
chg:
" where ClientID =

**UTLee** · 10-08-2014, 12:35 PM

the field on tblClients is named client and its a text field. When I change:

" where ClientID =
to:
"where client =
I get syntax error (comma) in query expression

**ssanfu** · 10-08-2014, 01:25 PM

This line isn't doing what you think it is doing:

Code:

Dim i, s As Integer

i is declared as a varient
s is declared as an Integer

What is the PK field of "tblClients"?
Is the PK field included in the row source of "List6"?
Is the PK field the bound field?

I modified the code a little more:

Code:

Private Sub cmdMarkReviewed_Click()
    Dim i As Integer, s As Integer
    Dim sSQL As String
    Dim strfilter As String

    For i = 0 To Me.List6.ListCount

        If Me.List6.Selected(i) = True Then
            s = s + 1
            If s = 1 Then strfilter = "ClientID = '" & Me.List6.Column(0, i) & "'"
            If s > 1 Then
                strfilter = strfilter & " or " & "ClientID = '" & Me.List6.Column(0, i) & "'"
            End If

            sSQL = "UPDATE tblClients Set tblClients.PortfolioReviewed = True, tblClients.[DateofLastReview] = #" & Date & "#"
            sSQL = sSQL & " WHERE ClientID = '" & List6.Column(0, i) & "';"
            'Debug.Print sSQL
            CurrentDb.Execute sSQL, dbFailOnError
        End If
    Next

    If s > 0 Then
        If Len(strfilter) > 255 Then
            MsgBox "You must select fewer clients. Try selecting 5 to 10 at a time."
            Exit Sub
        End If
    End If

End Sub

This is untested. Besure and use a copy of the dB for testing!
I would like to see the SQL of the "List6" row source and would need to know the bound column, the PK field type (string/Long) and the PK field of "tblClients".

**UTLee** · 10-08-2014, 02:09 PM

Thanks, Steve. I don't know much about SQL but I am at least smart enough to experiment on a copy of my db!

I tried your code and got "run-time error. too few parameters. Expected 1." So I hit debug and this line is highlighted: CurrentDb.Execute sSQL, dbFailOnError.

Pardon my ignorance, but what does PK mean?

**ssanfu** · 10-08-2014, 03:24 PM

PK = Primary Key.

but I am at least smart enough to experiment on a copy of my db!

You'd be amazed at the number pf people that don't experiment on a copy!

Un-comment the "Debug" and see what the SQL statement look like in the Immediate window.

**UTLee** · 10-08-2014, 03:49 PM

here's what it looks like in the immediate window:

UPDATE tblClients Set tblClients.PortfolioReviewed = True, tblClients.[DateofLastReview] = #10/8/2014# WHERE ClientID = 'ClientLastName, First';

I obviously removed the client's actual name from the end of the sql statement. I notice DateofLastReview is in brackets and PortfolioReviewed is not...what's the correct syntax? Any other thoughts?

**ssanfu** · 10-08-2014, 05:32 PM

I notice DateofLastReview is in brackets and PortfolioReviewed is not...what's the correct syntax?

Brackets are optional if there are no spaces in the name.

Using the client name to update fields is questionable. What happens if there are 2 "Smith, John" in the table? Do you update both?

Questions:
1) Is the client last name AND the first name in the field "ClientID"?
2) What is the bound column of the list box?
3) What is the SQL of the list box row source?
4) What is the primary key field of "tblClients"?

**UTLee** · 10-09-2014, 07:57 AM

Now that you've asked me about "ClientID" and tblClients, I realize that "ClientID" is actually a field from another table that contains the exact same data as tblClients.client. After I changed your code to use Client instead of ClientID, it works! It's putting check in the checkbox field tblClients.PortfolioReviewed and adding today's date in the DateofLastReview field. Thanks so much for your help with this!

I hate to get greedy here but if I were to want this sql to toggle the checkbox between true and false each time its clicked, how would I do that? Right now it only marks as true.

Here are the answers to your questions:

What happens if there are 2 "Smith, John" in the table?

tblClients won't allow me to create two records with the exact same client name.

Questions:
1) Is the client last name AND the first name in the field "ClientID"? - this is more of a short name field so it would show "Smith, J" for a client named John Smith.

2) What is the bound column of the list box? - Bound column is 2

3) What is the SQL of the list box row source? - Get ready for this one!:

Code:

SELECT tblMonthlyReview.client AS Client, tblMonthlyReview.Manager AS Mgr, Format([qryClientsWithHoldings].[TotalPortfolio],"$#,##0;($#,##0)") AS [Value], tblMonthlyReview.EquityTarget AS Trgt, tblMonthlyReview.PortfolioModel AS Model, (IIf(Sum(IIf([CashReview]='OK',0,1))>0,'CHECK CASH','OK')) AS Cash, tblMonthlyReview.EquityDriftReview AS [Equity Drift], tblMonthlyReview.DomesticExposureReview AS [US / Int'l], tblMonthlyReview.AssetClassDriftReview AS [Asset Class Drift], qryTestReview.PortfolioReviewed, qryTestReview.DateofLastReviewFROM (tblMonthlyReview INNER JOIN qryClientsWithHoldings ON tblMonthlyReview.client = qryClientsWithHoldings.client) INNER JOIN qryTestReview ON qryClientsWithHoldings.client = qryTestReview.client
GROUP BY tblMonthlyReview.client, tblMonthlyReview.Manager, tblMonthlyReview.EquityTarget, tblMonthlyReview.PortfolioModel, tblMonthlyReview.EquityDriftReview, tblMonthlyReview.DomesticExposureReview, tblMonthlyReview.AssetClassDriftReview, qryTestReview.PortfolioReviewed, qryTestReview.DateofLastReview, qryClientsWithHoldings.TotalPortfolio
HAVING (((tblMonthlyReview.Manager) Like [Forms]![frmMonthlyReview]![Combo141]) AND (((IIf(Sum(IIf([CashReview]='OK',0,1))>0,'CHECK CASH','OK'))) Like [Forms]![frmMonthlyReview]![ComboCashReview]) AND ((tblMonthlyReview.EquityDriftReview) Like [Forms]![frmMonthlyReview]![ComboEquityDrift]) AND ((tblMonthlyReview.DomesticExposureReview) Like [Forms]![frmMonthlyReview]![ComboUSReview]) AND ((tblMonthlyReview.AssetClassDriftReview) Like [Forms]![frmMonthlyReview]![ComboAssetClassDriftReview]))
ORDER BY tblMonthlyReview.client;

4) What is the primary key field of "tblClients"? the primary key for tblClients is tblClients.client

**ssanfu** · 10-09-2014, 05:06 PM

You have "qryTestReview.PortfolioReviewed" in the list box row source. So use the value from the list box and flip it.

Code:

Private Sub cmdMarkReviewed_Click()
    Dim i As Integer, s As Integer
    Dim sSQL As String
    Dim strfilter As String
    Dim bReviewed As Boolean

    For i = 0 To Me.List6.ListCount

        'column count is zero based. I think I counted right
        ' get PortfolioReviewed value from list box, column 9
        bReviewed = Not Me.List6.Column(9, i)

        If Me.List6.Selected(i) = True Then
            s = s + 1
            If s = 1 Then strfilter = "Client = '" & Me.List6.Column(0, i) & "'"
            If s > 1 Then
                strfilter = strfilter & " or " & "Client = '" & Me.List6.Column(0, i) & "'"
            End If

            sSQL = "UPDATE tblClients Set tblClients.PortfolioReviewed = " & bReviewed & ", tblClients.[DateofLastReview] = #" & Date & "#"
            sSQL = sSQL & " WHERE Client = '" & List6.Column(0, i) & "';"
            'Debug.Print sSQL
            CurrentDb.Execute sSQL, dbFailOnError
        End If
    Next

    If s > 0 Then
        If Len(strfilter) > 255 Then
            MsgBox "You must select fewer clients. Try selecting 5 to 10 at a time."
            Exit Sub
        End If
    End If

End Sub

Add/Edit the lines in blue

Again... this is untested.

**UTLee** · 10-10-2014, 08:55 AM

Steve - i added your code an am getting "run time error 13 type mismatch" and when I click debug, this is highlighted:
bReviewed = Not Me.List6.Column(9, i)

I counted 9 columns as well when starting with 0

**ssanfu** · 10-10-2014, 12:12 PM

Try this

Code:

Private Sub cmdMarkReviewed_Click()
    Dim i As Integer, s As Integer
    Dim sSQL As String
    Dim strfilter As String
    Dim bReviewed As Boolean

    For i = 0 To Me.List6.ListCount

        If Me.List6.Selected(i) = True Then
            s = s + 1
            If s = 1 Then strfilter = "Client = '" & Me.List6.Column(0, i) & "'"
            If s > 1 Then
                strfilter = strfilter & " or " & "Client = '" & Me.List6.Column(0, i) & "'"
            End If

            'column count is zero based. I think I counted right
            ' get PortfolioReviewed value from list box, column 9
            Msgbox Me.List6.Column(9, i)
            bReviewed = Not Me.List6.Column(9, i)

            sSQL = "UPDATE tblClients Set tblClients.PortfolioReviewed = " & bReviewed & ", tblClients.[DateofLastReview] = #" & Date & "#"
            sSQL = sSQL & " WHERE Client = '" & List6.Column(0, i) & "';"
            'Debug.Print sSQL
            CurrentDb.Execute sSQL, dbFailOnError
        End If
    Next

    If s > 0 Then
        If Len(strfilter) > 255 Then
            MsgBox "You must select fewer clients. Try selecting 5 to 10 at a time."
            Exit Sub
        End If
    End If

End Sub

Note that I moved the BLUE line and the comments.

The msgbox line is for debugging. Is it displaying a Boolean value?

**UTLee** · 10-10-2014, 02:13 PM

It looks like its working now. The message box give me either 0 or -1 and the list box is now showing true or false. Should I just delete the message box line? Thanks again for all of your help.

Code to Update Checkbox on Table

Thread Tools

Code to Update Checkbox on Table

Similar Threads

Update to a table with code

Reusing VBA Code To Update A Table

VBA code to update field in table

Have a ComboBox selection Update/Edit table checkbox value to Yes

VBA Code to auto update a field in another table?

Posting Permissions