Full Version: Parameter queries and VBA
UtterAccess Discussion Forums > Microsoft® Access > Access Queries
rdbothma
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 ?
BrianS
You should use ADO Command and Parameters objects to accomplish this.
Example
rdbothma
I can see I'm going to have to really dig into this ADO/DAO - I keep runnning into things nowadays that need them crazy.gif ... Thanks, I'l have a look
rdbothma
Very close to a straight cut and paste job, but here is the code I'm using :
CODE
Dim intELT_ID As Integer
ntELT_ID = Me.ELT_ID
Dim cat As New ADOX.Catalog
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset
cat.ActiveConnection = CurrentProject.Connection
Set cmd = cat.Procedures("qryDuplicate_ELT").Command
If Not (cmd Is Nothing) Then
  cmd.Parameters("ELT").Value = intELT_ID
  Set rs = cmd.Execute
  rs.Close
End If
Set cmd = Nothing

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 ?
datAdrenaline
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 ...
BrianS
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
CODE
Dim rs As ADODB.Recordset

change this line
CODE
  Set rs = cmd.Execute

to this
CODE
  cmd.Execute

and remove the close statement
CODE
  rs.Close
rdbothma
Thanks a bunch
urrentDB.Execute YourSQLStatement, dbFailOnError
worked a charm thumbup.gif
datAdrenaline
Glad to see that worked out ...
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.