I'm attempting to write a command button which will partially "clone" the current record with a new ID. The most obvious way I can think of doing this would be writing an append query. My table uses an Autonumber field as primary key, so the rest can be duplicated. I have considered using the RunSQL command, but having alot of fields, I end up using alot more characters than RunSQL string allows me. Is there a way to write a normal parameter query and give the parameter (the ID of the record to be cloned) to the query via the VBA OpenQuery method ? Perhaps another method ?

You should use ADO Command and Parameters objects to accomplish this.

Example

I can see I'm going to have to really dig into this ADO/DAO - I keep runnning into things nowadays that need them ... Thanks, I'l have a look

Very close to a straight cut and paste job, but here is the code I'm using :

When I try run it, it runs to the rs.close line and I get error 3704. Operation not allowed when object is closed. If I just comment it out, it works fine. I want to know however why is this so ? will it affect me negatively some time in the future if I just comment it out ?

This post may help out also ....

Copy a Record ..

Also ... if you already have code that creates a SQL statement, then instead of using RunSQL use ...

CurrentDB.Execute YourSQLStatement, dbFailOnError

HTH ...

The reason it errors out is because you don't open the recordset anywhere so you are unable to close it. Since you are running an append query, you probably don't need to use a recordset at all. Recordsets are used to retrieve the results of select queries.

You should remove the declaration



change this line



to this



and remove the close statement

Thanks a bunch

CurrentDB.Execute YourSQLStatement, dbFailOnError

worked a charm

Glad to see that worked out ...