multi-select listbox problem

[Guest]

Hello. I have an unbound form that is used to enter data into a composite
table (tblClientJurisdiction) for a many to many relationship. On the form
is a combobox (cboClient) for the user to select the client name. There is a
listbox (lstJurisdiction) that shows all the jurisdictions. It is set to
multiselect as a client can be in more then one jurisdiction. There is also
button (btnAddJurisdiction) that is used to add all the selected
jurisdictions and the one client to the table. But, guess what, it isn't
working completely. Information is placed in the table but it isn't the
selected information. Here is the coding that is behind the button on the
form. The 2 tables used are tblClient and tblJurisdiction that form the
tblClientJurisdiction.

Private Sub btnAddJurisdiction_Click()
Dim MyDB As DAO.Database
Dim rstClientJurisdiction As DAO.Recordset
Dim i As Integer

Set MyDB = CurrentDb()
Set rstClientJurisdiction = MyDB.OpenRecordset("tblClientJurisdiction",
DB_OPEN_DYNASET)

For i = 0 To lstJurisdiction.ListCount - 1
If lstJurisdiction.Selected(i) Then
rstClientJurisdiction.AddNew
rstClientJurisdiction!ClientID = cboClient.Column(0, i)
rstClientJurisdiction!JurisdictionID = lstJurisdiction.Column(0,
i)
rstClientJurisdiction.Update
lstJurisdiction.Selected(i) = False 'clears the selection
End If
Next i

End Sub

Can someone look this over to see what is wrong?
I thank anyone who responds.
*** John

 

[Douglas J. Steele]

Presumably the incorrect information is the Client Id, not the Juridiction
Id.

rstClientJurisdiction!ClientID = cboClient.Column(0, i) is incorrect: that's
going to give you a random client.

Try rstClientJurisdiction!ClientID = cboClient.Column(0) instead.

 

[Guest]

Mr Steele, thanks for the info. It worked by removing the 'i' from the line
as you indicated.
*** John