Not in List help

[ggs]

Hi I've lost my evening over this one

tblDriver has IDDriver Surname NameFirst
frmMain has control DriverFK (combo box) Properties are
Control Source DriverFK
Row Source SELECT tblDriver.IDDriver, [Surname] & ", " & [FirstName] AS Driver FROM tblDriver ORDER BY [Surname] & ", " & [FirstName];
Row Source Type Table/Query
Bound Col 1
Limit to List Yes
Allow v l edits No
Format Props

Col Count 2 Col Width 0;

and Not In List
Private Sub DriverFK_NotInList(NewData As String, Response As Integer)
Dim strMsg As String, rst As Recordset, db As Database
strMsg = "'" & NewData & "' Not in Driver List " & vbCrLf & vbCrLf
strMsg = strMsg & "Do you want to add this name?"
strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to link or No to re-type it."


Set db = CurrentDb
Set rs = db.OpenRecordset("tblDriver", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs![DriverFK] = NewData
rs.Update
rs.Close


Set rs = Nothing
Set db = Nothing
End Sub

now when a not in list is entered the Standard Access message comes up not the above VBA on clicking yes and opening Driver Form to add all works but returning to the Main form I am left with an almost blank record with just a comma

so my forms combo box looks like this

Bloggs, Joe
,
Flints, Fred
Rubble, Barney
and so each time my list of commas's grow till I go back to the tblDriver and delete the blank records
help please

[ssanfu]

First, you changed the field name:

tblDriver has IDDriver Surname NameFirst
frmMain has control DriverFK (combo box) Properties are
Control Source DriverFK
Row Source SELECT tblDriver.IDDriver, [Surname] & ", " &  [FirstName] AS Driver FROM tblDriver ORDER BY [Surname] & ", " &  [FirstName];

Don't know what these are:

Allow v l edits No
Format Props

The name of the record set is not the same:

Dim strMsg As String, rst As Recordset, db As Database
.
.

Set rs = db.OpenRecordset("tblDriver", dbOpenDynaset)
rs.AddNew
rs![DriverFK] = NewData
rs.Update
rs.Close
Set rs = Nothing

In the NotInList event, NewData will (should) contain the data that you want entered in a table as a new record. You have two fields for the name, "Surname" & "NameFirst"; but NewData is only one concatenated piece of data. Then you are trying to store text in a number field:

rs![DriverFK] = NewData

In the NotInList event, it will ALWAYS try to add a new record to "tblDriver" because you don't have a response from the msgbox function. It should look something like:

   If MsgBox(strMsg, intButtons, strTitle) = vbNo Then

The setting that indicates how the NotInList event was handled is missing.
See the "NotInList Event"in help...

[ggs]

it was only my post i had reversed the name and first but with the rest of your help i am underway again
thanks