I have a comboBox (Make) on a form which has a Row source from another table listing laptop makes.
The user can add a new 'Make' which doesn't appear in the dropdown.
I am using NotInList event which updates the Make in the table, but the current record which is being created is saved and moves to a New record.

Is it possible to keep the current record after updating the new 'Make' in another table.

Private Sub combo78_NotInList(NewData As String, Response As Integer)
'Allow user to save non-list items.
Dim cnn As New ADODB.Connection
Dim strSQL As String
Dim bytUpdate As Byte

On Error GoTo ErrHandler

Set cnn = CurrentProject.Connection
bytUpdate = MsgBox("Do you want to add " & Me.Combo78.Text & " to the list?", vbYesNo, "Non-list item!")

If bytUpdate = vbYes Then

strSQL = "INSERT INTO tblMakeModel(Make) " & "VALUES ('" & NewData & "')"
Me!Combo78 = NewData
Debug.Print strSQL

cnn.Execute strSQL

Response = acDataErrAdded

ElseIf bytUpdate = vbNo Then

Response = acDataErrContinue

Me!Combo78.Undo

End If

Exit Sub

ErrHandler:

MsgBox Err.Number & ": " & Err.Description, vbOKOnly, "Error"

End Sub

Hi

Try

Thanks for quick reply, but that procedure seems to be doing the same. Moves to next "new" record after moving from combobox (data is added to table from combo78)

Steve

Hi Steve

I guess there's something else going on then, as there's nothing in there to move to any record - new or otherwise.

Do you have any code in any of the other events of the combo ?

No other events on Combo78.

Combo78 has control source from table 'Job sheet' as all other combo/textboxes.

Row Source for Combo78 is:

SELECT DISTINCT [tblMakeModel].ID, [tblMakeModel].Make FROM tblMakeModel ORDER BY [Make];

Steve

Easiest way to solve the mystery would be to take a look for you. Can you post a copy of your db (zipped to less than 500k and no sensitive data) and I'll have a look..........

Doh!

As I was stripping the database down to post, left the form with only a few combo boxes, tried adding new data and it worked ! Will try again on main form!

Thanks for you help Alan

Not sure if this was the problem, but combo78 started as a textBox and I changed it to a comboBox. When I added a combo box to form and used above code, all worked okay ?????

Steve

Very strange - but as long as it's working for you, as Homer would say, WhooHoo

Still need help on this one. When adding a value to combo box which is not in the list, it asks if you would like to add to list, if yes, then the dropdown is initiates on the combobox but the value added is not in list. The value is however added to to the table referred to in row source.

Try changing: Response = acDataErrorAdded
To: Response = acDataErrAdded

Thanks, that solves that problem. I am having the problem mentioned earler on this post were after selected yes to add to list, the form moves on to next 'new' record. I need to continue adding data to the current record.

In the Form Properties, look for "Cycle".

If it is currently set to "All Records", change it to "Current Record".

Once again thank you for your help. This done the trick.

Steve

You're Welcome! Good luck with your project!