mpearce
07-08-2009, 12:20 PM
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.
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.