NotinList event

[Andy]

In the Below code I am attempting to update 3 fields int
the table Top50 as a result of the NOTINLIST event. I can
get 2 (Accountname & City) but not the third (phone)to
update. Thank you in advance for your help.
Andy

Private Sub Account_Name_NotInList(NewData As String,
Response As Integer)
Dim strMsg As String, strTitle As String
Dim strsql As String
Dim ctl As Control
Dim stAns As String


Set ctl = Me.[Account Name]
strMsg = NewData & " Is Not Recognised By The System." &
vbCrLf & vbCrLf & _
"Do You Wish To Add This Data To The System?"
strTitle = " Add Status"

If MsgBox(strMsg, vbYesNo + vbDefaultButton2 +
vbQuestion, strTitle) = vbYes Then
stAns = InputBox("Enter The Account Location")
strsql = "INSERT INTO Top50 (AccountName, City, Phone)
VALUES (""" & NewData & """, """ & stAns & """)"

 

[Graham Mandeno]

Hi Andy

What value are you expecting to be inserted into the Phone field? You are
prompting the user for a City value, using InputBox, but you are not asking
for a phone number. Neither are you including one in the list of VALUES in
your SQL statement.

You should either: 1) prompt for a phone number in the same way you are
prompting for a city, or 2) make yourself a small dialog form to ask for
both the City *and* Phone.

 

[Guest]

Seems to me that your use of the input box will only
obtain one needed field. What I do is design a small form
with all required fields necessary for the new list item -
ask the question if they want to add the new list item -
if vbyes - open the form to a new record using newdata in
the openargs. Upon opening the form - in the form load
event - put openargs in the correct field. It's sounds
more complex than it is. This way, you can make sure they
fill out all necessary fields before saving the record.
You can have an Ok and Cancel button on the form so they
can bail if they want - if they cancel - when you go back
to the originating form, it will know they didn't add the
record and fire the notinlist event again. It works real
good. If you need an example, email me at
(e-mail address removed). Hope this helped.