Cascading/linked list boxes

[JChandler22]

I'm trying to add two linked list boxes to the 2nd tab (labeled "Program Interest") of my main form, which is called Contacts. I'll attach the db here:

View attachment db3.zip

I've formed the list boxes, as well as the control boxes between them, but the control buttons don't seem to work at all. The code for the so-called "add" button is:

Private Sub addBtn5_Click()
    Dim ProgListCounter As Integer, ProgCurrentCounter As Integer
    Dim ProgListItems As Integer, ProgCurrentItems As Integer
    Dim ListStr As String, FoundInList As Integer
    ProgListItems = [ProgAvailable].ListCount - 1
    ProgCurrentItems = [ProgSelected].ListCount - 1
    For ProgListCounter = 0 To ProgListItems
        If [ProgAvailable].Selected(ProgListCounter) = True Then
            If IsNull([ProgSelected].RowSource) Then
                ListStr = [ProgAvailable].Column(0, ProgListCounter) & ";"
                [ProgSelected].RowSource = ListStr
            Else
                FoundInList = False
                For ProgCurrentCounter = 0 To ProgCurrentItems
                    If [ProgSelected].Column(0, ProgCurrentCounter) = [ProgAvailable].Column(0, ProgListCounter) Then
                        FoundInList = True
                    End If
                Next ProgCurrentCounter
                If Not FoundInList Then
                    ListStr = [ProgSelected].RowSource & [ProgAvailable].Column(0, ProgListCounter) & ";"
                    [ProgSelected].RowSource = ""
                    [ProgSelected].RowSource = ListStr
                End If
            End If
        End If
    Next ProgListCounter
End Sub

and for the "remove" button:

Private Sub remove_btn_Click()
    Dim ListStr As String
    Dim ProgCurrentItems As Integer, ProgCurrentCounter As Integer
    Dim ProgListItems As Integer
    ProgListItems = [ProgSelected].ListCount - 1
    ListStr = ""
    For ProgCurrentCounter = 0 To ProgListItems
        If [ProgSelected].Selected(ProgCurrentCounter) = False Then
            ListStr = ListStr & [ProgSelected].Column(0, ProgCurrentCounter) & ";"
        End If
    Next ProgCurrentCounter
    [ProgSelected].RowSource = ""
    [ProgSelected].RowSource = ListStr
End Sub

I've double checked the name of the list boxes and combed through the code to try to figure it out, but to no avail - admittedly, I'm certainly no VBA expert when it comes to Access.

If someone would kindly take a look and see if they might be able to offer any suggestions or point me in the right direction, I would greatly appreciate it!

Thanks,
JChandler

 

[JChandler22]

I actually figured that one out. It had to do with the properties for my second list box - the row source type should have been set to "Value List."

Now that that's fixed, the 2nd list box is showing numbers (the PK Program ID) instead of the program names when program data is transfered from list box 1 to list box 2. Any suggestions on this one?

Thanks again!

 

[Tim L]

Just changing the line

ListStr = [ProgSelected].RowSource & [ProgAvailable].Column([B]0[/B], ProgListCounter) & ";"

to:

ListStr = [ProgSelected].RowSource & [ProgAvailable].Column([B]1[/B], ProgListCounter) & ";"

Will ensure that the text is transferred, if you want to transfer both the text and the ID then you could concatenate the information, but I'm not at all sure whether it is possible to have multiple column when the list box is in Value List mode, nor how to concatenate the values so that the list box realises they are multiple columns; merely concatenating the two values will leave you with a number followed by the text.

Possibly the answer would be to use a table behind the scenes for the list box and add and delete rows as required, requerying the list box after each addition/deletion?

As I've never seen how to do this I am interested and will follow the thread. Can't look at it any more tonight but hopefully will have time tomorrow.

Regards,

Tim

 

[JChandler22]

Hi Tim,

Thanks so much for that simple fix. Worked like a charm.

You may be right about needing a table behind the scenes. Ultimately, I'd like the data that's transfered to the 2nd list box to update a table (which I haven't yet built). As an Access novice, this is a bit of a daunting task.

My only problem at this point is that my current design allows for the same program in ListBox#1 to be transfered more than once to ListBox#2. Any quick fixes for this?

Thanks again, Tim.

 

[JChandler22]

The other thing is that when I navigate between records, the list boxes don't change as they should. I'm guessing this is because the ListBox #2 is unbound? I'm not sure how I would bind it so that the data stays once it's entered, and it's unique to that contact.

Here's the most recent version of this db:

View attachment db3.zip

I've also built a new table, called "ContactsPrograms," which I'm hoping is set up correctly to accept data from this 2nd list box.

Any suggestions are appreciated. Many thanks!

 

[Tim L]

You're right about the unbound thing and the listboxes not changing.

I think that you will be able to solve most of your problems (but probably not without coming across others along the way) by ensuring that both of the listboxes have a query as their Row Source.

I think that perhaps what you need is a table (say, tblSelectedProgs), suitably related to the Contacts table: it will only need two fields, both of which are Foreign Keys (that from Contacts and that from the table listing the progs available, let's call it tblProgsAvail).

Use tblSelectedProgs and tblProgsAvailable to create two queries: the first 9qryProgsAvail) will list all of the Progs Available that have not been selected for a particular contact, the second (qryProgSel) will list all of the Progs Available which have been selected.

When you select an item from the ProgsAvail list you will need to create a record in tblSelectedProgs, filling the two fields with the relevant information. When you remove a prog from the 'ProgsSelected' list box you will need to delete the relevant records. Note, for each program selected there will be a separate record in tblSelectedProgs (I believe that Access 2007 is another beast entirely, but let's not get into arguments about whether it was a good idea or not).

In your routines to add/remove records from tblSelectedProgs you will need to Refresh/Requery the two list boxes. They should (I hope) automatically show the correct information when you change to a different contact record, if not, trigger a Refresh/Requery on the list boxes in the On Current event for the form.

Additional Comment:

Just re-read your post, and looked further in to your db. Your table ContactsPrograms is almost the equivalent of tblSelectedProgs that I use above. There is, however, no need to include the third column, 'ProgramName' because if you want this you would combine it in a query with the Programs table. ContactsPrograms (and tblSelectedProgs) is basically a many-to-many (m:n) link table (also known as a relationship table, because it is used to represent the relationship between two other tables), so it should not have any other information stored in it, other than the primary keys of those tables in the relationship). In the relationships editor you appear to have correctly linked Programs, ContactsPrograms and Contacts, however I think that you should really have enforced referential integrity.


HTH

Tim

 

[JChandler22]

Tim,

You've been extremely helpful, thanks.

I'll comb through this and see what I can come up with, then I'll get back to you and let you know how it went. Thanks again!

 

[JChandler22]

Use tblSelectedProgs and tblProgsAvailable to create two queries: the first 9qryProgsAvail) will list all of the Progs Available that have not been selected for a particular contact, the second (qryProgSel) will list all of the Progs Available which have been selected.

Admittedly, I'm pretty new to access and not terribly familiar with building queries. You say to use tblSelectedProgs and tblProgsAvailable to create the queries... if I select New Query, what type would I select?

Also, will I need to make two queries for each contact in my db?

 

[JChandler22]

Ha, I seem to be posting on my own thread more than anyone else! However, I think I'm getting the picture, and I just want to talk (type) through it, and maybe you might be able to verify that I'm on the right track.

The left listbox is a multiselect. When I select entries, and click the command button ">", I should run some code that sends that data to tblContactsPrograms. This is a many:many table with two fields:

tblContactsPrograms
ContactID
ProgramID

...so if for a particular contact, the user selects n programs, then n new records will be created on tblContactsPrograms. Ok.

So, the rowsource of the left listbox on the form should be an "unmatched" query (no need to make an ACTUAL query object, right??) on tblPrograms & tblContactsPrograms to find which programs have not yet been selected, correct? How do I tell the query to search only for that contact ID?

I'll leave it at this for now. Again, thanks for your help in advance.

 

[Tim L]

You don't need to create a new query for each contact.

Query 1: "qryProgsGot"

SELECT Programs.*, ContactsPrograms.ContactID
FROM Contacts INNER JOIN (Programs RIGHT JOIN ContactsPrograms ON Programs.ProgramID = ContactsPrograms.ProgramID) ON Contacts.ContactID = ContactsPrograms.ContactID
WHERE (((Contacts.ContactID)=[Forms]![Contacts]![ContactID]));

Query 2: qryProgsMissing

SELECT Programs.ProgramID, Programs.Interest, Programs.ProgramName, Programs.ProgramDescription
FROM Programs LEFT JOIN qryProgsGot ON Programs.ProgramID = qryProgsGot.ProgramID
WHERE (((qryProgsGot.ProgramID) Is Null));

qryProgsGot is constructed manually and references the ContactsID on the Contacts form, consequently if the Contacts form is not open it will not return any results.

qryProgsMissing is constructed via the Find Unmatched query wizard and looks for all records in the Programs table which do not have a matching record in the ContactsPrograms table.

Note, because qryProgsMissing references qryProgsGot it too requires the Contacts form to be open. If the Contacts form is not open a dialogue asking for the value of Forms!Contacts!ContactID.

Had to go shopping, otherwise the reply would have been quicker.

HTH

Tim

 

[Tim L]

It seems like you are getting the hang of it. My last post was actually 'under-construction' when you made your last post, so I didn't see it before I posted my reply (as I said, had to go shopping).

So, the rowsource of the left listbox on the form should be an "unmatched" query (no need to make an ACTUAL query object, right??) on tblPrograms & tblContactsPrograms to find which programs have not yet been selected, correct? How do I tell the query to search only for that contact ID?

In response to your question in bold, wrong. As I said before, I think the best way to resolve this issue is to have BOTH of the list boxes referencing (in the Row Source) a query (those that I posted in my previous reply, don't change the name of the first one, because the second one references it (or if you do you'll have to go in and change the references as well)).

Can't spend any more time on this tonight. I'll look for your next posting tomorrow.

Tim

 

[JChandler22]

Wow, thanks again Tim. Extremely helpful. Those queries work great. I think I should be able to get the rest from here. I'll let you know how it goes!

 

[JChandler22]

Whew! Making some good progress here... for some reason, even when I zip my db, it's too big to post here (~500 kb).

Successfully coded the '>' button except that it won't send more than one selection:

Private Sub addBtn5_Click()
        Dim db As DAO.Database, rs As DAO.Recordset, i As Long
            Set db = CurrentDb
            Set rs = db.OpenRecordset("ContactsPrograms")
                With rs
                    .AddNew
                    .Fields("ContactID") = Me.CurrentRecord
                    .Fields("ProgramID") = Me.ProgAvailable.Column(0)
                    .Update
                End With
        Me.ProgAvailable.Requery
        Me.ProgSelected.Requery
End Sub

And started working on the '<' button... sort of works except that it always deletes the entry at the TOP of the right list box (even if it's not selected) instead of the one that IS selected:

Private Sub remove_btn_Click()
    
    Dim db As DAO.Database, rs As DAO.Recordset
    Set db = CurrentDb
    Set rs = db.OpenRecordset("SELECT ProgramID FROM ContactsPrograms WHERE ContactID = " & Me.CurrentRecord)
    rs.Delete
    
    rs.Close
    db.Close
    
    Me.ProgSelected.Selected(0) = True
    Me.Refresh
    
    
    Me.ProgAvailable.Requery
    Me.ProgSelected.Requery
End Sub

I'll pick this up again tomorrow once I'm back in the office. Until then, any suggestions are greatly appreciated... thanks!

 

[Tim L]

Re: the size, have you run Compact And Repair on the db and then tried zipping it?

Tim

 

[JChandler22]

Thanks, that did the trick:

View attachment db5.zip

 

[JChandler22]

Got the '>' button to work with multiple selections:

Private Sub addBtn5_Click()
    Dim db As DAO.Database, rs As DAO.Recordset, var As Variant
    Set db = CurrentDb
    Set rs = db.OpenRecordset("ContactsPrograms")
    For Each var In Me.ProgAvailable.ItemsSelected
        With rs
            .AddNew
            .Fields("ContactID") = Me.CurrentRecord
            .Fields("ProgramID") = Me.ProgAvailable.Column(0, var)
            .Update
        End With
    Next
    Me.ProgAvailable.Requery
    Me.ProgSelected.Requery
End Sub

Still working on the other button.

 

[Tim L]

How's about this then:

Private Sub remove_btn_Click()

    [COLOR="blue"]For Each [/COLOR]var [COLOR="blue"]In [/COLOR]Me.ProgSelected.ItemsSelected
        DoCmd.SetWarnings [COLOR="blue"]False [/COLOR][COLOR="SeaGreen"]' turn off warning of the impending deletion[/COLOR]
        DoCmd.RunSQL ("DELETE from ContactsPrograms WHERE ProgramID = " & Me.ProgSelected.ItemData(var))
        DoCmd.SetWarnings [COLOR="blue"]True [/COLOR][COLOR="seagreen"]' turn warnings back on[/COLOR]
    [COLOR="blue"]Next[/COLOR]

    Me.ProgSelected.Selected(0) = [COLOR="blue"]False[/COLOR]
    Me.Refresh

    Me.ProgAvailable.Requery
    Me.ProgSelected.Requery

[COLOR="blue"]End Sub[/COLOR]

It also prevents the error that occurs if you click the << button without any items available in the list box.

Incidentally, I suggest that you Order the Row Source for ProgSelected by the ProgramID.

HTH

Tim

 

[JChandler22]

Brilliant. Works like a gem! Again, Tim, thank you SO much for your help. You've made it so much easier.

 

[JChandler22]

Actually, it looks like it deletes ALL instances of a particular ProgramID. If contacts #3 and #7 both have program #222, both of those records will be deleted, no matter what record you're on in the form...

 

[pbaldy]

Answered on your duplicate posting of this question.