Access problem; "Not in List"

[mfilion]

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

[Tanis]

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

[mfilion]

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