listbox add items

  • Thread starter Thread starter JEM
  • Start date Start date
J

JEM

Help! What is wrong with my code? I have a listbox that is supposed
to allow the users to select several items in the list and click a
button and it will add those items (contacts) to their record. But,
what is happening is that if i pick 3 different items, I get 3
records, but they are all the same item instead of the 3 different
ones. Any ideas on what's wrong with my code? Thanks for the help!

For i = 0 To Me.lstCaseContacts.ListCount - 1
If Me.lstCaseContacts.Selected(i) Then
rs.AddNew
rs.Fields("ClientID") = Me.OpenArgs
rs.Fields("ContactID") = Me.lstCaseContacts.Column(0)
If Me.lstCaseContacts.Column(9) <> "" Then
rs.Fields("OrganizationID") = Me.lstCaseContacts.Column(9)
End If
rs.Fields("ContactType") = Me.lstCaseContacts.Column(1)
rs.Update
End If
Next i
 
When using the Column collection dealing with list boxes, you need to supply
a row number too:

For i = 0 To Me.lstCaseContacts.ListCount - 1
If Me.lstCaseContacts.Selected(i) Then
rs.AddNew
rs.Fields("ClientID") = Me.OpenArgs
rs.Fields("ContactID") = Me.lstCaseContacts.Column(0, i)
If Me.lstCaseContacts.Column(9, i) <> "" Then
rs.Fields("OrganizationID") = Me.lstCaseContacts.Column(9, i)
End If
rs.Fields("ContactType") = Me.lstCaseContacts.Column(1, i)
rs.Update
End If
Next i

BTW, depending on how large the list box is, you might find it faster to use
the ItemsSelected collection:

Dim varSelected As Variant

For Each varSelected In Me.lstCaseContacts.ItemsSelected
rs.AddNew
rs.Fields("ClientID") = Me.OpenArgs
rs.Fields("ContactID") = Me.lstCaseContacts.Column(0, varSelected)
If Me.lstCaseContacts.Column(9, varSelected) <> "" Then
rs.Fields("OrganizationID") = Me.lstCaseContacts.Column(9,
varSelected)
End If
rs.Fields("ContactType") = Me.lstCaseContacts.Column(1, varSelected)
rs.Update
Next varSelected

Also, are you sure that the 10th column is going to be a zero-length string?
If it could be Null, use

If Len(Me.lstCaseContacts.Column(9, varSelected) & "") > 0 Then
 
When using the Column collection dealing with list boxes, you need to supply
a row number too:

For i = 0 To Me.lstCaseContacts.ListCount - 1
If Me.lstCaseContacts.Selected(i) Then
rs.AddNew
rs.Fields("ClientID") = Me.OpenArgs
rs.Fields("ContactID") = Me.lstCaseContacts.Column(0, i)
If Me.lstCaseContacts.Column(9, i) <> "" Then
rs.Fields("OrganizationID") = Me.lstCaseContacts.Column(9, i)
End If
rs.Fields("ContactType") = Me.lstCaseContacts.Column(1, i)
rs.Update
End If
Next i

BTW, depending on how large the list box is, you might find it faster to use
the ItemsSelected collection:

Dim varSelected As Variant

For Each varSelected In Me.lstCaseContacts.ItemsSelected
rs.AddNew
rs.Fields("ClientID") = Me.OpenArgs
rs.Fields("ContactID") = Me.lstCaseContacts.Column(0, varSelected)
If Me.lstCaseContacts.Column(9, varSelected) <> "" Then
rs.Fields("OrganizationID") = Me.lstCaseContacts.Column(9,
varSelected)
End If
rs.Fields("ContactType") = Me.lstCaseContacts.Column(1, varSelected)
rs.Update
Next varSelected

Also, are you sure that the 10th column is going to be a zero-length string?
If it could be Null, use

If Len(Me.lstCaseContacts.Column(9, varSelected) & "") > 0 Then

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no private e-mails, please)







- Show quoted text -
Click to expand...

Ah, ha! I knew it had to be something simple that i was missing.
Thanks so much! Yes, i used the ItemsSelected before and had errors
so i switched, but i think all along it was missing the row in the
column collection as it now works beautifully! Thanks again for the
help.
Jenn
 
Back
Top