NotInList event on combo box causing problems

[Guest]

I've got a combo box (UnitSN) which displays values from a table (tblUnit)
where status = "Available" and the limit to list property is set to Yes.

If the user types in a different entry, the not in list event fires which
asks the user if they want to add the new data to the list. If they say yes,
then tblUnit recordset is opened and the new value is added to it. However
the default value of status in tblUnit is "NotAvailable" which is fine. So
I've then set the limit to list property of the combo box to No so that the
new value SHOULD be accepted, but it isn't. I get a message that the field
can't contain a null value as it's the primary key.

Here's the code, any ideas?

DoCmd.SetWarnings False
If intNewBoard = vbYes Then
Set db = CurrentProject.Connection
rs.Open "tblUnit", db, adOpenKeyset, adLockOptimistic,
adCmdTableDirect
rs.AddNew
rs.Fields("UnitSN") = NewData
rs.Update
Response = acDataErrContinue
Set db = Nothing
rs.Close
Me.UnitSN.LimitToList = False
Me.UnitSN.Value = NewData
Else etc....

P.S. I've tried changing acDataErrContinue to Added but I just get Access'
own error message that I've chosen something not from the list....

Help!!
Thanks
Julia

 

[Guest]

Sorry folks, it's a problem with my table! Please ignore this post.