Ok, this problem is killing me. I have a drop down list on my form. It is set to limit to list. If the user enters something not in the list they are prompted if they really want to enter this new value. If they click YES the value is blasted into the table and their entry is excepted. The problem is after I update the table (see code below) I get a write conflict (see attached .bmp)
Private Sub HGS_Model_NotInList(NewData As String, Response As Integer)
Dim strSQL As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
If ReallyEnterNewData = vbYes Then
Set db = CurrentDb
Set rs = db.OpenRecordset("tblPrograms", dbOpenDynaset)
.FindFirst ("[FD Project] = """ & Me.FD_Project & """")
If Not .NoMatch Then
.Fields("HGS Model") = NewData
Response = acDataErrAdded ' confirm record added
Me.[HGS Model].Undo ' clear the entry in the combobox
Response = acDataErrContinue ' confirm the record is not allowed
This particular problem is that you start editing a record (through the form) and then tell access to edit the same record through code while it's still open for editing through the form.
The solution depends on what's going on in your db. I usually have a seperate table (dynamic) or short list (static) to fill combo boxes. If you can tell me a bit more about the combo list and how it relates to the rest of the data in the table, I may be able to help you out.
You may want to either create a seperate table for the contents of your combobox or set limit to list to false and run some code to check if the entry is in the list and confirm that it be added if not.
The combo box just query's the table for distinct list Model Numbers.
Each record (project) has a model number associated with it. There are several projects for each model number. To reduce typing, I wanted to provide a list of model numbers that have already been entered (the query). If they really wanted to enter a new model number for the first time I had to blast the data in using a query of some sort because the limit to list property would not allow the addition.
My current solution was to get rid of the prompt if the user really wanted to enter the new data. I set limit to list = No, and in the after update event I do a dlookup for the combo boxes data in the table and if it retruns null then I save the record and requery the combo box.