Duplicates error in a field that isn't indexed

[JPnyc]

I'm getting the duplicates error, in a field that isn't indexed, and I've even tried indexing and allowing duplicates in that field of the access table. Same error. When you're using bound controls, do you have to manually move the recordset?

[Mega_Man]

Check to ensure that the backend field is not set to unique values.

Mega.

[JPnyc]

I think I've discovered the problem, but I can't solve it, If you would be so kind, check this thread:
http://vbforums.com/showthread.php?s=&threadid=210682

[Mega_Man]

The error 'Insufficient key column' means that the VB is expecting to see a Primary key in the table.
If you dont have one, then add one. It doesn't matter that it may be a auto increment and you may never use it but it still needs it.

Mega.

[JPnyc]

No it has a primary key. I'm not getting that error anymore. I'm getting a "Would cause duplicates " error. What I'm tryin to do is double click on a Hier. flexgrid, and open that rs in a form, and be able to update it. It only works with the 1st rs. All others give the above error. I'm not able to change the rs that is being pointed to. Would you look over my code in the above link/thread?

[Mega_Man]

I am unfamilliar with the way you are querying your recordset as I use unbound controls with ADO. However, I think is't possibly your bookmark that's causing the problem. Try changing your code so that your recordset returns 1 value.

rscomOES.Open ("SELECT [ISBN] WHERE [ISBN] = " & strISBN
rscomOES.Update

Mega.

[JPnyc]

I get "Object or variable not set". Do you have to open the rs with bound controls? Do I have to set the value of rscomOES? That's the name of a query command in the de. I know the connection is open, because the 1st rs will update, and if I add "MoveNext" to the event, I can update the next. But I need to get the cursor to whatever the user selects, which is why I set the srtISBN = txtISBN.text. I just don't know how to say it in SQL to get the DB to reference that rs.

[Mega_Man]

Could you do...

Dim I as integer
For I = 1 to rscomOES.RecordCount
 
If rscomOES.Fields ("ISBN") = strISBN then
    rscomOES.Update
Else
    rscomOES.Movenext
End if
 
Next

Mega.

[JPnyc]

It gives the duplicate error again. I actually tried that earlier. Been trying to solve this since this morning. It gives the duplicate error because my form is filled with a different rs, but the DB's cursor is still on the 1st rs. Therefore to put the rs I have, into the 1st row in the DB would create a duplicate in the prim. key field. That's why it's rejecting it.

[Mega_Man]

I fail to see why .Movenext is not working. I have to go now as it is 01:20am now. I will ponder on this and If you are still having problems tomorrow, I will get back in touch when I'm at work. I will be in a better position there to recreate your problem.
Sorry I can't be of further assistance at the moment. Please post here if you get it solved in the meantime.

Mega.

[JPnyc]

MoveNext is working. If I include that it moves to the next rs. But I need it to move to the one the user clicks in a flexgrid, which opens the form that is bound to the DB. The FG is also dragged and dropped.

[Mega_Man]

Still having problems?

[JPnyc]

Well yes and no. I'm not having THAT problem. But I'm only programming about 4 weeks, so problems are a constant. The previous problem was solved when I learned that the find method of a rs doesn't require a requery. Just the field and the comparative string. Now I'm having probs with a search and find next sub.