Solved: if..then logic add item to listbox if it does not exist in the list

[mpearce]

Private Sub btnAddtoErrors_Click()
Dim PhoneNumber As String
Dim StrSQL As String
Dim ctlList As Control, varItem As Variant

PhoneNumber = txtPhoneFormat.Value

' Return Control object variable pointing to list box.
Set ctlList = Me.LstErrorList


StrSQL = "insert into errors(phonenumber)values('" & [PhoneNumber] & "')"
Set db = CurrentDb
MsgBox PhoneNumber
' Enumerate through selected items.
For varItem = 0 To ctlList.ListCount - 1
' Print value of bound column.

If PhoneNumber = ctlList.ItemData(varItem) Then
MsgBox "format in list", vbCritical, "Format in list"
Exit Sub
Else
db.Execute StrSQL
MsgBox "format added", vbInformation, "format added"
Exit Sub
End If
Next varItem
LstErrorList.RowSource = "errors"

End Sub
I have a small problem here. basically i have a form with a textbox, listbox and button. The listbox gets populated from a table that contains a list of bad phone number formats. I want the user to be able to add other formats to the listbox. But i dont want the format added if it is already in the list.

Based on the code above it looks like it work smoothly as duplicates are not added to the list, but the user is still told that the format was added even though it wasn't.

Any ideas where i went wrong?

Thanks

ps i borrowed some of that from a post on here i believe, i forget who posted it, but i feel i need to credit them for it.

[Kafrin]

Your loop is not worling the way round you want it to. It will look at the first list item, see if it matches the entry, then throw up a message box and exit. I don't see how it ever looks beyond the first list entry. Try something like this:

' Enumerate through selected items.
varItem = 0
Do Until varItem > ctlList.ListCount - 1
If PhoneNumber = ctlList.ItemData(varItem) Then
MsgBox "format in list", vbCritical, "Format in list"
Exit Sub
End If
Loop
'If match not found then loop will exit and can add new format
db.Execute StrSQL
MsgBox "format added", vbInformation, "format added"

[mpearce]

that was exactly what i need, it works perfectly.

[Kafrin]

Excellent, glad I could help :sparkle: