combo box trouble in 2007

[kimchilee]

I have an UNBOUND combo box called cboSubjectID (text format) on a form that I am using to look up existing record. Under AfterUpdate event, I wrote following code to try to do this but does not work.

Private Sub cboSubjectID_AfterUpdate()
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[SubjectID] = '" & Me![cboSubjectID] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
' Find the record that matches the control.
End Sub


When I try to save this record, it says "the changes you requested to the table were not successful because they would create duplicate values in the index, primary key or relationship"


Can anyone tell me what I'm doing wrong?

 

[pbaldy]

It sounds like the combo is bound to a field in the table, which a "find" combo should not be.

 

[kimchilee]

Thanks Paul for your kind reply. I double check the combo box and the ControlSource is currently set to blank. What about the RowSource? It is simply set to a query named qryDemographics. Is that okay?

 

[pbaldy]

That should be okay. The row source is where it gets its selections; the control source is where it saves the selection. Can you post the db?

 

[kimchilee]

Here is my database. I would appreciate any help you can offer on this. I used to do this with no problem on Access 2003 but a lot of things changed with Access 2007. Thanks again!!!

 

[Galaxiom]

I suspect your EOF test is the problem.
FindFirst in a DAO recordset is tested by the NoMatch property.
EOF is used for ADO recordsets.

Edit: Actually I might be thinking of Filter there but it is late and I need sleep more than I need the answer. I am sure you will tell me tomorrow.


You have only declared rs as an object so it will default to a DAO.Recordset when it is Set.

You really should be more specific in the variable declaration (or instantiate it with late binding).

 

[pbaldy]

Where do you get an error? When I change the data entry property of the form, the combo appears to work normally.

 

[Galaxiom]

I have had a look at your database. As Paul alluded you have the DataEntry property set to Yes so the only record in the recordset is the NewRecord and you will never match the combo value.

I also checked my comment about the use of Not rs.EOF to indicate that there has been a match. My original advice was correct though slightly misdirected since apparently your intention was not to check for a match but the possibility of an emptry recordset.

However a NoMatch test would have indicated a problem.

The problem manifests when a new value is added to the list via your NotInList event procedure. This new value is not added to the form's Recordset until the form is requeried.

Add Me.Requery to the end of the NotInList procedure and I think your problem will be solved.