UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

 
 Reply to this topicStart new topic
> Parameter queries and VBA    
 
   
rdbothma
post May 8 2006, 11:09 AM
Post #1

UtterAccess Addict
Posts: 140
From: Cambridge, UK
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 ?
Go to the top of the page
 
+
BrianS
post May 8 2006, 11:27 AM
Post #2

UtterAccess VIP
Posts: 5,597
From: St. Louis, MO
You should use ADO Command and Parameters objects to accomplish this.

Example
Go to the top of the page
 
+
rdbothma
post May 8 2006, 11:39 AM
Post #3

UtterAccess Addict
Posts: 140
From: Cambridge, UK
I can see I'm going to have to really dig into this ADO/DAO - I keep runnning into things nowadays that need them (IMG:http://www.utteraccess.com/forum/style_emoticons/default/crazy.gif) ... Thanks, I'l have a look
Go to the top of the page
 
+
rdbothma
post May 8 2006, 12:06 PM
Post #4

UtterAccess Addict
Posts: 140
From: Cambridge, UK
Very close to a straight cut and paste job, but here is the code I'm using :
CODE
Dim intELT_ID As Integer

intELT_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 ?
Go to the top of the page
 
+
datAdrenaline
post May 8 2006, 12:51 PM
Post #5

UtterAccess Editor
Posts: 15,978
From: Northern Virginia, USA
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 ...
Go to the top of the page
 
+
BrianS
post May 8 2006, 01:19 PM
Post #6

UtterAccess VIP
Posts: 5,597
From: St. Louis, MO
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
Go to the top of the page
 
+
rdbothma
post May 9 2006, 06:55 AM
Post #7

UtterAccess Addict
Posts: 140
From: Cambridge, UK
Thanks a bunch

CurrentDB.Execute YourSQLStatement, dbFailOnError

worked a charm (IMG:http://www.utteraccess.com/forum/style_emoticons/default/thumbup.gif)
Go to the top of the page
 
+
datAdrenaline
post May 9 2006, 09:19 AM
Post #8

UtterAccess Editor
Posts: 15,978
From: Northern Virginia, USA
Glad to see that worked out ...
Go to the top of the page
 
+
« Next Oldest · Access Queries · Next Newest »
 

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 

Display Mode: Standard · Switch to: Linear+ · Switch to: Outline

Track this topic · Email this topic · Print this topic · Subscribe to this forum

RSS Go to Top  ·  Lo-Fi Version Time is now: 25th May 2013 - 11:19 PM