By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,704 Members | 1,867 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,704 IT Pros & Developers. It's quick & easy.

Access problem; "Not in List"

P: 2
Hey all,

I have a problem with my the Form I built, hopefully someone here can help me out! I'm still a beginner, so bear with me!

Here goes...

The form contains many boxes, each of which refer to a specific table. For example, one box is called "City Name" and it gets it info from a 1-column table containing city names.

I was able to get a working "event procedure" code that enables me to add in a city name when it is not already in my list. I was able to apply this code to all the other boxes except one (and this is where I have a problem).

The box I can't apply it too is called "ContactInfo1" and is the result of a query, and shows a result in the format "Prefix FirstName LastName" (obtained from a 3-column table)

How do I modify my code so that when I type a name in using that format, lets say "Mr. John Smith", for example, "Mr." goes in the first column of the list table, "John" goes in the second, and "Smith" goes in the third? Right now, If I type "Mr. John Smith", the whole name appears in the "Prefix" column of my list.

Thanks!!

Here is the code i'm using:

Private Sub ContactInfo1_ID_NotInList(NewData As String, Response As Integer)

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim ContactInfo1_ID As AccessObject

Set db = CurrentDb
Set rs = db.OpenRecordset("ContactInfo1", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!Prefixe = NewData
rs.Update


If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded

End If

rs.Close
Set rs = Nothing
Set db = Nothing


End Sub
Oct 11 '06 #1
Share this Question
Share on Google+
2 Replies


100+
P: 143
On Not In List is fine for adding one record to a table. In your situation, I would suggest opening a form. The Micosoft Knowledge base describes this method in detail with code. You might have to modify the code to enter say three fields, but at least it gives you a start.


http://support.microsoft.com/kb/197526/en-us
Oct 12 '06 #2

P: 2
Thanks for the help, I'll try it out!!

On Not In List is fine for adding one record to a table. In your situation, I would suggest opening a form. The Micosoft Knowledge base describes this method in detail with code. You might have to modify the code to enter say three fields, but at least it gives you a start.


http://support.microsoft.com/kb/197526/en-us
Oct 13 '06 #3

Post your reply

Sign in to post your reply or Sign up for a free account.