On Feb 7, 8:46 am, "Keith Wilby" <h...@there.comwrote:
"Jana" <Bauer.J...@gmail.comwrote in message
news:11**********************@l53g2000cwa.googlegr oups.com...
Hi Jana, here's the code in its entirity - it now works but note the
commented line Me.Undo:
Private Sub cboManagerName_NotInList(NewData As String, Response As Integer)
If MsgBox("You entered " & NewData & " - is this the manager's network logon
ID?", vbYesNo, "Confrim Manager ID") = vbYes Then
Dim rs As DAO.Recordset, strCriteria As String
Set rs = Me.RecordsetClone
strCriteria = "[SurveyTakenID] = " & Me.txtSurveyTakenID
With rs
.FindFirst strCriteria
.Edit
![ManagerName] = NewData
.Update
End With
rs.Close
Set rs = Nothing
Response = acDataErrAdded
Me.Undo 'I've no idea why this is needed, but omiting it generates a
"write confilct" error when you click a subform
Else
Response = acDataErrContinue
Me.cboManagerName.Undo
End If
End Sub
Regards,
Keith.
Keith:
I may be having an off day, but I am really confused. You are using a
bound form with a combo box on it. You then use the combo box to add
a record to the data that your form is already bound to. Thus, you
are entering two instances of the same data into your data source.
Once is done through the form, the other is done through your code.
This is causing the error, as you're writing twice to the same data
set. I think that's why the Me.Undo fixes your issue. You're undoing
the record made/changed by the form. My confusion lies in your use of
the combo box. What is the Row Source of your combo box? Is it
always blank, meaning that the NotInList event is triggered every time
someone enters a record? What, exactly, are you trying to accomplish
with this combo box?
Here's some background on form basics: A form that's bound to data
(meaning it's Record Source property is not blank) is ALREADY writing
to your data when you complete the fields. So, creating a form based
on a table or a writeable query is basically a pretty way for you to
type in new records or to edit existing ones. No different than
typing directly in the table, just prettier and with the ability to
validate data, etc.
A combo box is usually linked to a different data source than your
form is, unless you're using it to locate a record in your form. For
instance, say you have a movie rental business. Your database would
have a table of customers, a table of movies, and a table of the
movies a customer has rented out. Each customer has an ID and each
movie has an ID. One customer can rent multiple movies. You don't
want to force your employees to memorize all the customer and movie
IDs, so you create a form called Customers, and a subform called
MoviesRented. On your Customers form, you have an unbound combo box
that lets you type in a customer's name. When you exit that combo
box, it finds and displays the customer's record. Now, on your
MoviesRented subform, you have a combo box that is linked to your
table of movies. Your employee enters the name of the movie, and the
combo box then grabs the ID that matches that title and adds it to the
table of rented movies.
Usually, the NotInList event is used to add a record to the Row Source
of a combo box. So, in our movie database example, if an employee
entered a movie title that wasn't in the list, you could give them the
option to add the movie to the movies table and then make it instantly
available in the combo box for them to select it for the customer to
rent. That would also make the title available to other employees who
might be renting out the same title.
I hope this rambling message helps you to sort out what you need to do
on your end, or at least guide you to new questions to ask. Let me
know the overall idea of what you're trying to do, and perhaps we can
steer you in the right direction.
HTH,
Jana