Add to NotInList - Possible solution & questions (1 Viewer)

[Mark Liddle]

Not in List

I have a form that is based on a single table.
On this form I have 2 combo boxes where the recordsource = CollegeCode (A field in the table)

Combo36 is set to
Limit to list = Yes
Visible = Yes
Tab Order = 0 (First field on the form)

CollegeCode is set to
Limit to list = No
Visible = No
Tab Order = 36 (Last on the form)

Both of these fields are directly on top of one another. as you will see in my code, this creates the illusion that the user has not updated 2 fields.

The following code is assigned to Combo36_NotInList.

Private Sub Combo36_NotInList(NewData As String, Response As Integer)
Dim MyCode As String

On Error GoTo ErrHandler
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 record ?", vbYesNo) = vbYes Then

Me.Combo36.LimitToList = False 'Try to avoid the error saying that its not in the list
MyCode = UCase(NewData) 'MyCode is a public variable
Me.CollegeCode.Visible = True 'Bring the CollegeCode field into view
Me.CollegeCode.Setfocus 'Cannot set visible = false for Combo36 while it has the focus
Me.Combo36.Visible = False 'Hide the Combo36 box
DoCmd.GoToRecord , , acNewRec 'Add a new record to the table
Me.CollegeCode = MyCode 'Assign the value of MyCode to the now visible ColCode field
Me.CollegeName.SetFocus 'User thinks that focus has simply move to the next field.
MyCode = Null 'Reset MyCode to null - Status is validated later.
Response = acDataErrAdded
Else
Response = acDataErrContinue
Me.Combo36.SetFocus
End If

ErrHandler:
Err.Number = 0
Resume Next

End Sub

When the last field on the form loses focus, then the visible properties for Combo36 and CollegeCode are adjusted back to the "default" setting accordingly.


Questions.
1. I still get an error saying that I must choose a record from the list. I cannot fathom what fires up the error as I have tried to eliminate it with the code lines
Me.Combo36.LimitToList = False 'Try to avoid the error saying that its not in the list
Response = acDataErrAdded AND
Err.Number = 0 'which is in the ErrHandler

How do I get rid of this access generated error message ?

2. After the last (visible) field loses focus, how can I update the table using code ?
I have tried using ".Update" but this spits out another error.
Currently the user must press the ENTER key twice (focus moves in and out of CollegeCode.) before the data is added.

3. Finally - how do i get the Combo36 box to see the data that I have just added.

 

[D-Fresh]

For 1 and 3:

Write an Insert SQL statement on the Not in List event.

Dim MyDB as database
Dim MySQL as string

MySQL = "INSERT INTO [TableSource] (CollegeCode) VALUES ('" & me![Combo36] & "')"
'Note: Don't use the apostrophes(Single Tick marks) if CollegeCode is a numeric value!

set MyDB = currentdb
mydb.execute(MySQL)
response = acDataErrAdded

This will insert the record and your combo box will automatically recognize it as being added. Hope that helps.

Doug