update query tied to form

[waymond]

I have the following update query tied to a form

INSERT INTO [Object] ( as400narr, Lib, Name, Type )
SELECT OBJP1.ODOBTX, OBJP1.ODLBNM, OBJP1.ODOBNM, OBJP1.ODOBTP
FROM OBJP1
WHERE (((OBJP1.ODLBNM)=[Forms]![Object]![cboODLBNM]) AND ((OBJP1.ODOBNM)=[Forms]![Object]![cboODOBNM]));
after the last cbo box is run which is called cboType i use a macro called narrMacro that with commands open query and save as soon as I select the data for the last combo box and click record selector to go to next it says the data would produce duplicate records. When I check the table OBJECT the record is in there.
what am I doing wrong that produces these records. I do not see in the macro a place just to run the query

thank you

 

[PHV]

You may try to use the DISTINCT predicate in your SELECT instruction.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[lespaul]

the data would produce duplicate records

so there's obviously a primary key or a unique index on the table OBJECT and the information that you are trying to insert is not valid because it violates the rules of the PK or index.

When I check the table OBJECT the record is in there.

So the record you are trying to insert already exists and as explained above violates the key or index.

If there is already a record, why aren't you using an UPDATE query instead of an INSERT query?

Perhaps you need to:
1. run the query
2. see if any results are returned
3. if results returned run UPDATE query
4. if no results returned run INSERT query

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual