VBA Code Help - Listbox with DAO

[docholliday]

Hi all,

I am new to the forum and a novice when it comes to VBA. Myself and a few others have been asked to put together a db at work and we have a question regarding DAO and listboxes.

In short, we have a form where the user can select items in a listbox (multiselect listbox). Each time the user selects an item, we have code that changes the item status (Yes/No field = TRUE) so the items can be added into a temporary table to run further analysis on (the analysis is driven by user selection).

Here is that code:

Private Sub CustomerList_AfterUpdate()

 Dim i As Integer
     i = Forms("BusinessReview").Controls("CustomerList").MultiSelect
        If i = 0 Then
            Call CheckMeOut_Single
        Else
            Call CheckMeOut_Multi
        End If
End Sub

.

This runs a module which marks the selections as TRUE in a Yes/No boolean field (called "Selected"), which then allows a select query to add records with "Selected" = TRUE to a temporary table.


Here's where I'm having issues. If a user wants to UNSELECT items in a listbox, then the Yes/No fields that were checked as TRUE need to be changed to FALSE when the user unchecks them from the listbox on the form.

I was able to create an "UNSELECT ALL" button to remove all of the items in the listbox from the temporary table:

Private Sub Command18_Click()


Dim n As Integer
Dim ctrl As Control


Set ctrl = Me.CustomerList


For n = 0 To ctrl.ListCount - 1
ctrl.Selected(n) = False
Next n


Dim i As Integer
     i = Forms("BusinessReview").Controls("CustomerList").MultiSelect
        If i = 1 Then
            Call CheckMeOutFalse_Multi
        End If


End Sub

.

This code calls the following public function to change the Yes/No status of all items in the listbox to FALSE:

Public Sub CheckMeOutFalse_Multi()
    Dim db As DAO.Database, rs As DAO.Recordset
    Dim frm As Form, ctrl As Control, strCustomer As Double, varItem As Variant
    
    Set frm = Forms!BusinessReview
    Set db = CurrentDb
    Set rs = db.OpenRecordset("SELECT t_TempCustBR.CUSTID, t_TempCustBR.Chain, t_TempCustBR.DivName, t_TempCustBR.CUSNUM, " & _
                              "t_TempCustBR.STORENAME, t_TempCustBR.STATUS, t_TempCustBR.LASTACTIVE, t_TempCustBR.CHAINID, t_TempCustBR.Selected " & _
                              "FROM t_TempCustBR WHERE " & strWHERE & ";")
    rs.MoveFirst
    
    Do Until rs.EOF = True
            rs.Edit
                rs!Selected = False
            rs.Update
        rs.MoveNext
    Loop
    rs.Close
    Set rs = Nothing
    db.Close
    Set db = Nothing
End Sub

.

This button works great for handling multiple items in the listbox (we also have a SELECT ALL button to do the opposite function, which works fine).

The issue comes when a user is manually selecting and unselecting items in the listbox. On the initial select, the item is marked Yes/No (the Selected field) = TRUE, which is perfect. However, if the user manually clicks on a selected item to unselect it in the listbox, the Yes/No field doesn't change to FALSE.

Any ideas for possible code to solve the manual unselect issue?

[pbaldy]

I do this type of thing in the after update event of the listbox:

  Dim ctl                     As Control
  Dim db                      As DAO.Database
  Dim rs                      As DAO.Recordset

  On Error GoTo ErrorHandler

  Set db = CurrentDb()
  Set rs = db.OpenRecordset("TableName", dbOpenDynaset)

  Set ctl = Me.lstCharges
  If (ctl.Selected(ctl.ListIndex)) Then 'item got selected
    rs.AddNew
    ...  
    rs.Update
  Else  'item got unselected
    db.Execute "DELETE * FROM TableName " _
                    & "WHERE KeyField = " & ctl.Column(1) 
  End If

  ...

[docholliday]

I tried that code and am getting this error message at "Me.lstCharges": Compile Error: Method or Data Member not found.

Private Sub CustomerList_AfterUpdate()

 Dim i As Integer
     i = Forms("BusinessReview").Controls("CustomerList").MultiSelect
        If i = 0 Then
            Call CheckMeOut_Single
        Else
            Call CheckMeOut_Multi
        End If


  Dim ctl As Control
  Dim db  As DAO.Database
  Dim rs  As DAO.Recordset


  On Error GoTo ErrorHandler


  Set db = CurrentDb()
  Set rs = db.OpenRecordset("t_TempCustBR", dbOpenDynaset)


  Set ctl = Me.lstCharges
  If (ctl.Selected(ctl.ListIndex)) Then 'item got selected
    rs.AddNew
    ...
    rs.Update
  Else  'item got unselected
    db.Execute "DELETE * FROM t_TempCustBR " _
                    & "WHERE KeyField = " & ctl.Column(1)
  End If


End Sub

I'm still a novice with writing code, so it could very well be an error on my part.

Is there a way to keep the top section of my code and edit your code so that it is only changing the "Selected" status of the items in the table rather than completely deleting them from the table?

[pbaldy]

You would replace "lstCharges" with the name of your listbox. That was the name of mine. You could run UPDATE SQL instead of DELETE to edit the record.

[docholliday]

Thanks a lot for all of your help Paul! I had no idea where to start with this and its giving me some good direction.

Now I'm getting a syntax error on the "..." after "rs.addnew".

I removed that and changed the code to:

Private Sub CustomerList_AfterUpdate()

 Dim i As Integer
     i = Forms("BusinessReview").Controls("CustomerList").MultiSelect
        If i = 0 Then
            Call CheckMeOut_Single
        Else
            Call CheckMeOut_Multi
        End If


 Dim ctl As Control
 Dim db  As DAO.Database
 Dim rs  As DAO.Recordset


  Set db = CurrentDb()
  Set rs = db.OpenRecordset("t_TempCustBR", dbOpenDynaset)


  Set ctl = Me.CustomerList
  If (ctl.Selected(ctl.ListIndex)) Then 'item got selected
    rs.Update
  Else  'item got unselected
    db.Execute "UPDATE SQL * FROM t_TempCustBR " _
                    & "WHERE Selected = " & ctl.Column(1)
  End If


End Sub

.

Now I'm getting "runtime error 3144 syntax error in UPDATE statement."

Guessing that I need to rewrite that part somehow but I'm not sure where it's messing up at (again, novice with VBA code).

[pbaldy]

Well, the ... was simply to take the place of the irrelevant code removed for space savings. The proper syntax for an update query is:

UPDATE TableName
SET FieldName = DesiredValue
WHERE KeyField = Whatever

[docholliday]

I got that syntax from looking at SQL view for a query I set up. Here is the SQL view for what I want the query to do when items selected are clicked to be unselected:

UPDATE t_TempCustBR SET t_TempCustBR.Selected = False
WHERE (((t_TempCustBR.Selected)=True));

But when I tried to translate that into the db.Execute portion of the VBA code, I keep getting the same error about the update field not being right. So I tried to go with this solution to the code:

Private Sub CustomerList_AfterUpdate()


 Dim i As Integer
     i = Forms("BusinessReview").Controls("CustomerList").MultiSelect
        If i = 0 Then
            Call CheckMeOut_Single
        Else
            Call CheckMeOut_Multi
        
        End If
        
 Dim ctl As Control
 Dim db  As DAO.Database
 Dim rs  As DAO.Recordset


  Set db = CurrentDb()
  Set rs = db.OpenRecordset("t_TempCustBR", dbOpenDynaset)


  Set ctl = Me.CustomerList
  If (ctl.Selected(ctl.ListIndex)) Then 'item got selected
    rs.Edit
        rs!Selected = True
    rs.Update
  Else  'item got unselected
    DoCmd.SetWarnings False
    
        DoCmd.OpenQuery "q_UpdateSelectedBR"
    
    DoCmd.SetWarnings True
    
  End If


End Sub

.

So that query "q_UpdateSelectedBR" is the SQL view of what I want the VBA code to do.

I don't get any errors on the listbox update. Problem is now I have to select ALL of the items in the list for anything to show up as "Selected = TRUE" in the table. Otherwise, the update query runs and all items are shown in the t_TempCustBR table as "Selected = FALSE" - even though I had some selected in the listbox.

Is there a way to translate the SQL code in the query to the VBA code method for db.Execute?

[pbaldy]

Well, your SQL is changing all records in the table that are currently True to False. I would think you'd want to restrict to the record just deselected in the listbox. You may want to do something similar with the first part. You can't be sure the recordset is on the record selected in the listbox, so you are likely editing the wrong one.

[docholliday]

That's exactly what I want to do.

I want to use the template of this part of the code...

  Else  'item got unselected
    db.Execute "UPDATE SQL * FROM t_TempCustBR " _
                    & "WHERE KeyField = " & ctl.Column(1)
  End If

... and make it work under the elements of this SQL:

UPDATE t_TempCustBR SET t_TempCustBR.Selected = False
WHERE (((t_TempCustBR.Selected)=True));

My problem is I can't get the proper code to work in the VBA view for db.Execute.

[pbaldy]

You could put that all inside the quotes and it would work fine. In other words (the parentheses and semi-colon aren't necessary, deleted for clarity):

db.Execute "UPDATE t_TempCustBR SET t_TempCustBR.Selected = False WHERE t_TempCustBR.Selected=True"

My point is that is not restricting to a particular record, it's updating any record where Selected = True.

[docholliday]

Got it.

That's been my biggest problem with this so far - getting the code to recognize that when a selected record is clicked on again, the user wants to remove it from the selection pool, and thus change the "Selected" status.

I see this kind of multiselect listbox on websites all of the time where users can select and unselect multiple entries. Doesn't seem like something that should be too difficult to do in Access - or maybe I'm just missing something.

Really appreciate all of your input. Any thoughts on what's missing from it?

[pbaldy]

The more common method is to allow the user to make their selections, then deal with them all at once. That code is simpler, it just loops the selected items of the listbox. If you want to do it dynamically as they make selections, it's trickier. The code snippet I gave first is how I add/delete from a temp table as the user selects or deselects items. If you have records already there and you just want to modify the Selected field, you'd run the update SQL instead, but you'd have to identify the record. The WHERE clause for that would look similar to the one in the DELETE query I was using.

[docholliday]

I'm open for the more simpler method.

The main idea is the user should make their selections on the listbox, then click a button to run the analysis on whatever items are selected at that point. So that would eliminate the need to constantly update when they select/unselect.

Under the simpler method, my assumption is the user can select and unselect all they want, but once they hit the analysis button, whatever selections they have at that point are going to be used.

If that's the case, couldn't I just use this portion of the code in the 'on click' event for the analysis?

Dim i As Integer
     i = Forms("BusinessReview").Controls("CustomerList").MultiSelect
        If i = 0 Then
            Call CheckMeOut_Single
        Else
            Call CheckMeOut_Multi
        End If

[pbaldy]

If by analysis you mean a report, this is one way:

http://www.baldyweb.com/multiselect.htm

[docholliday]

Thanks Paul! Went with the simple method and now have the listbox functioning normally