What you are wanting is a many-to-many relationship. This is done by using a
third table as a linking table. You need a minimum of two fields in this
linking table. The fields are the unique ID fields from each of the other
two tables. Make both fields simultaneously the Key field for this table and
link each of them to their associated field in the two tables you already
have.
As you select clients who want to track a bill, you would place the client's
id and the bill's id in the linking table as a record. To see which clients
want to track which bill, you would then check the linking table for all
client IDs that match a particular bill ID. The link between the linking
table and the clients table would then allow you to use this ID value to get
the rest of the information about each client.
To do what you're wanting with the listbox, you will need to program the
listbox to operate somewhat independently of your form. You will need code
in the form's Current event to select the clients in the listbox that are
associated with the bill in the linking table. Also, when you make changes
to the listbox, you will need to use code to write those changes to the
linking table. There needs to be at least 2 columns in the listbox, the
unique ID field from the clients table and the text description from the
clients table that you want to see. The unique ID field would be the Bound
Column.
To show the current selections, in the form's Current event, open a
recordset on the linking table filtered by the form's current bill ID. Step
through the recordset and select each matching ID in the listbox.
Example:
Dim db As DAO.Database, rst As DAO.Recordset
Dim strSQL As String, ctl As Control, i As Long
'This SQL syntax assumes a number data type for BillID
strSQL = "SELECT ClientID FROM tblLinkingTable WHERE BillID = " &
Me.txtBillID
Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)
Set ctl = Me.lstMyListbox
'Set the selections
For i = 0 To ctl.ListCount - 1
'This syntax assumes a number data type for ClientID
rst.FindFirst "ClientID = " & ctl.ItemData(i)
'NoMatch will tell us if the item was not
'found in the recordset (True or False). But we
'want to know if it was found, so the result we're
'looking for is actually the opposite of NoMatch
ctl.Selected(i) = Not rst.NoMatch
Next
Set ctl = Nothing
rst.Close
Set rst = Nothing
Set db = Nothing
The above is untested, if the FindFirst errors on a recordset that has no
records, then check for no records and if that's the case, set Selected for
all listbox entries to False.
To write the selections when you change them back to the table, clear the
table of all entries that currently exist for that BillID then add the
changed selections.
Example:
Dim db As DAO.Database, rst As DAO.Recordset
Dim strSQL As String, varItem As Variant, ctl As Control
'This SQL syntax assumes a number data type for BillID
strSQL = "DELETE * FROM tblLinkingTable WHERE BillID = " & Me.txtBillID
Set db = CurrentDb
db.Execute strSQL, dbFailOnError
Set rst = db.OpenRecordset("tblLinkingTable", dbOpenDynaset)
Set ctl = Me.lstMyListbox
For Each varItem in ctl.ItemsSelected
With rst
.AddNew
!ClientID = ctl.ItemData(varItem)
!BillID = Me.txtBillID
.Update
End With
Next
Set ctl = Nothing
rst.Close
Set rst = Nothing
Set db = Nothing
This all assumes that the RowSource for the listbox lists all clients in the
listbox whether or not they are associated with the current bill displayed
on the form. This will then highlight those clients that are associated with
the current bill.
