Add to combo box

[Mark Liddle]

I have a form which shows all the details about a college.
Each college has a code (string) to identify it. This code is displayed in a combo box that is limited to list of valid codes.

If the user types in a value that is not in the list, a message asks the user if they want to add the new code (and subsequent details) to the underlying table.

If the user answers "Yes", then add the new value to the list and at the same time, get the user to complete the other fields on the form. (I have set some fields as compulsory - I.E. may NOT be left blank.)

Have tried the following, but its not working.

Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset("tblCollege")
If MsgBox("It seems you have typed in a code which does not exist yet" & Chr(13) & _
"Do you want to add a new college to the list ?", vbOKCancel) = vbOK Then
NewData = UCase(NewData)
With rst
.AddNew 'The following fields may NOT be left blank
!CollegeCode = NewData 'I have therefore filled in "DEFAULT" info
!Position = "Position" 'that must be changed by the user.
!Title = "Title"
!Name = "Name"
!Add1 = "Address"
!Tel = "Telephone"
.Update
End With

Response = acDataErrAdded
rst.Close
Set rst = Nothing
' Find the record that matches the control.
Me.Combo36 = NewData
Me.RecordsetClone.FindFirst "[CollegeCode] = '" & Me![Combo36] & "'"
Me.Bookmark = Me.RecordsetClone.Bookmark

Else
Response = acDataErrContinue
Me.Combo36.SetFocus
End If

Any assistance greatly appreciated