By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,155 Members | 2,518 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,155 IT Pros & Developers. It's quick & easy.

create a new record and related records simultaneously

WyvsEyeView
P: 46
On my form frmTopics (bound to table tblTopics), I've added a Copy button that copies the current topic record to create a new, identical one...simple enough. However, each topic record can have 1+ related records in tblTopicAttributes (a record for each attribute associated with the topic). So, I also want the copy action to create new and identical related records in tblTopicAttributes for the newly-created tblTopics record. I can do this with SQL but I'm not sure how to "translate" that to VBA. I appreciate any suggestions.
Aug 24 '08 #1
Share this Question
Share on Google+
14 Replies


nico5038
Expert 2.5K+
P: 3,072
You can create the query like:
Expand|Select|Wrap|Line Numbers
  1. INSERT INTO tblX (field1, field2) SELECT field1, field2 from tblY where ID_tlX = 123
  2.  
Now use the currentdb.execute statement in your code like:
Expand|Select|Wrap|Line Numbers
  1. currendb.execute ("INSERT INTO tblX (field1, field2) SELECT field1, field2 from tblY where ID_tlX = 123")
  2.  
Getting the idea ?

Nic;o)
Aug 24 '08 #2

WyvsEyeView
P: 46
I understand the principle, but the logistics/timing confuse me. Say I'm on frmTopics with a record selected. I want to to copy the current topic record and all its associated attributes. So I click my Copy button and voila, the following code executes and I'm taken to the shiny new record:

Expand|Select|Wrap|Line Numbers
  1.   DoCmd.RunCommand acCmdSelectRecord
  2.   DoCmd.RunCommand acCmdCopy
  3.   DoCmd.RunCommand acCmdRecordsGoToNew
  4.   DoCmd.RunCommand acCmdSelectRecord
  5.   DoCmd.RunCommand acCmdPaste
Now, obviously, I have to run the CurrentDb.Execute event before I go to the new record, because I need to choose the associated attributes to copy using the topic ID from the "old" record. But yet, I have to create the new topic record before I can run the CurrentDb.Execute event because I also need the new topic ID for the new associated attribute records. It's kind of a chicken-and-egg thing. Am I making this more complicated than it has to be?
Aug 29 '08 #3

nico5038
Expert 2.5K+
P: 3,072
I understand the principle, but the logistics/timing confuse me. Say I'm on frmTopics with a record selected. I want to to copy the current topic record and all its associated attributes. So I click my Copy button and voila, the following code executes and I'm taken to the shiny new record:

Expand|Select|Wrap|Line Numbers
  1.   DoCmd.RunCommand acCmdSelectRecord
  2.   DoCmd.RunCommand acCmdCopy
  3.   DoCmd.RunCommand acCmdRecordsGoToNew
  4.   DoCmd.RunCommand acCmdSelectRecord
  5.   DoCmd.RunCommand acCmdPaste
Now, obviously, I have to run the CurrentDb.Execute event before I go to the new record, because I need to choose the associated attributes to copy using the topic ID from the "old" record. But yet, I have to create the new topic record before I can run the CurrentDb.Execute event because I also need the new topic ID for the new associated attribute records. It's kind of a chicken-and-egg thing. Am I making this more complicated than it has to be?
The proposed code should be placed "behind" a button.
This will do all the work of creating copies of all rows from the tblTopicAttributes for the same ID from the tblTopics as the form displays.
Of course the new tblTopics entry will have to be inserted first as otherwise the dependents won't be able to refer to their parent.

Do you want to try to create this code yourself or do you need more support ?

Nic;o)
Aug 29 '08 #4

WyvsEyeView
P: 46
I think I need a few more hints. I understand the code needs to be behind my Copy button, it's just that what happens now is that

1. user clicks Copy
2. db copies current topic
3. db moves to new record
4. db pastes copied topic

So at this point the new topic is created and the old topic is no longer in focus. Somehow I need to get back to the old topic to copy its related attributes, then move back to the new topic (so I'll know what that topic ID is) and paste the copied attributes to create new records with the new topic ID. I don't understand how to make that switch, or alternatively, how to copy both the topic and its related attributes at the same time, then create a new topic record, paste the old topic to create a new topic (and hence the new topic ID) then paste the related attributes using the new topic ID.

I'm going to keep plugging away, but if you can give me a little nudge, I would really appreciate it!
Aug 30 '08 #5

nico5038
Expert 2.5K+
P: 3,072
I would advise you not to use these docmd commands as they are interrupted when the user clicks e.g. on another application...

The "new way" will need the button to create a new row in the Master table and read back the created unique key. Then a bulk copy of the related Child records can be performed.

For the "read back" we can use for an autonumber key the DMAX() function after having insert the row.

Just give it a try.

Nic;o)
Aug 30 '08 #6

WyvsEyeView
P: 46
Okay guys, I think this time you have too much confidence in me :) I have finally gotten a query working that copies the record displayed in frmTopics and creates a new record. Only thing is sometimes it works and sometimes it doesn't and I can't figure out why it copies some records and not others. I can't find a pattern. Still working on that one.

But I can't get a query working that copies the attributes from the currently-displayed record and adds corresponding records to tblTopicAttributes using the topic ID of the newly-created topic record. I understand the DMax function, just not how to work it into the query. Here is what I have:

Expand|Select|Wrap|Line Numbers
  1. INSERT INTO tblTopicAttributes ( top_id, attr_id, attrval_id )
  2. SELECT tblTopicAttributes.top_id, tblTopicAttributes.attr_id, tblTopicAttributes.attrval_id
  3. FROM tblTopicAttributes
  4. WHERE (((tblTopicAttributes.top_id)=[Forms]![frmTopics]![nbrTopID]));
This selects all the attributes for the currently-displayed topic but now how do I insert the values of attr_id and attrval_id into the table and create the top_id equal to DMax("topID", "tblTopics")?
Sep 2 '08 #7

WyvsEyeView
P: 46
Also, when I run the query that copies the original topic, as in opening the form displaying a record and running the query directly from Access, it works (mostly). But when I attach it to a button using the CurrentDb.Execute event, I get some cryptic message about not enough parameters, expecting 11. I looked this message up and don't even understand the solution...something about concatenation, which I know how to do, but I don't understand what they are asking for. All in all, I think this whole idea might just be too much for me, so I am going to try to come up with a perhaps less elegant solution but one that I can at least understand and implement. Thanks again.
Sep 2 '08 #8

nico5038
Expert 2.5K+
P: 3,072
The not enough parameters message indicates an error in the defined fieldnames.
Make sure they are "known" in the table!

The "move" with need the "Parent" to be present. Thus first insert the parent, than get the last (MAX) key added and use that for the "move".

Nic;o)
Sep 2 '08 #9

WyvsEyeView
P: 46
I'm not sure what you mean by make sure the fieldnames are known in the table. I have double-checked my table and double-checked the names on the corresponding fields on the form and everything matches. As I said, when I simply run the query directly from Access, it works. It's only when I try to run it via a command button using the CurrentDb.Execute event, that I get the weird "not enough parameters" message. I just don't have the programming or SQL background at this point to understand what's missing or wrong. (I'm completely self-taught and not a programmer by profession.) If I could get some sample code, I could probably reverse-engineer it and understand, but I just cannot write this code myself. Would it be possible for me to upload my database and get some additional help?
Sep 2 '08 #10

Expert Mod 2.5K+
P: 2,545
Hi. The parameter error message is misleading. It arises when running queries that contain where-clause references to controls on forms. These work OK in most circumstances from the Access query editor - but the JET database engine does not recognise the form control reference as a valid field name and as a result raises a parameter error when you try to open a recordset based on them, or execute an action query and so on.

The simplest answer to this is to incorporate the value of the control in the SQL string, not a reference to the control's name.

As I do not know the type for your control I show below the numeric version and the string version of referring to the value of the control in your SQL string. Select whichever is appropriate in your case and substitute this value-based version of the WHERE clause for the current form-referred version:

Expand|Select|Wrap|Line Numbers
  1. "...
  2. WHERE (((tblTopicAttributes.top_id)=" & [Forms]![frmTopics]![nbrTopID] & "));"
Expand|Select|Wrap|Line Numbers
  1. "...
  2. WHERE (((tblTopicAttributes.top_id)='" & [Forms]![frmTopics]![nbrTopID] & "'));"
-Stewart
Sep 2 '08 #11

WyvsEyeView
P: 46
Okay, I am finally returning to this issue because I feel like a little more self-flagellation. I think I might be getting closer to a solution. Essentially, here is the approach:

Use INSERT statement to create the new topic record but do not move to it on frmTopics.
Use INSERT statement to create new attribute records by selecting those associated with the topic currently in focus on frmTopics.
Use DMax() function to get topID of newly-created topic and use that as the topic ID for the new attribute records.
Move frmTopics to the new topic record using strWHERE variable set to DMax value--this isn't exactly what I want because frmTopics is already open. I just want to move to the new record. I think I need DoCmd.GoToRecord instead but not sure how to parameterize it.

I think in concept I'm pretty close. However, the code thus far doesn't work. I get a message saying that the function will create a duplicate record id in tblTopics and I understand why. So how do I copy the current record into a new record in the same table? Obviously I can't duplicate the record id, but don't I have to select the current record using some unique field? Many thanks as always.

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdCopy_Click()
  2.  
  3. DoCmd.SetWarnings (False)
  4.  
  5. Dim strMsg As String
  6. Dim strSQL1 As String
  7. Dim strSQL2 As String
  8. Dim strTopMax As String
  9. Dim strWhere As String
  10.  
  11. strMsg = "You are about to copy this topic and its attributes."
  12.  
  13. If MsgBox(strMsg, vbOKCancel) = vbOK Then
  14.   strSQL1 = "INSERT INTO tblTopics (topID, descr, type, version, status, statusDate, review, reviewDate, libDoc, custom1, custom2, active) SELECT topID, descr, type, version, status, statusDate, review, reviewDate, libDoc, custom1, custom2, active From tblTopics WHERE (((tblTopics.TopID)=" & [Forms]![frmTopics]![nbrTopID] & "));"
  15. strTopMax = DMax("TopID", "tblTopics")
  16.   strSQL2 = "INSERT INTO tblTopicAttributes ( top_id, attr_id, attrval_id ) SELECT tblTopicAttributes.top_id, tblTopicAttributes.attr_id, tblTopicAttributes.attrval_id FROM tblTopicAttributes WHERE (((tblTopicAttributes.top_id)=strTopMax));"
  17.   CurrentDb.Execute strSQL1, dbFailOnError
  18.   CurrentDb.Execute strSQL2, dbFailOnError
  19.   If Me.Dirty Then Me.Dirty = False 'save first.
  20.   If Not IsNull(Me.[actual club]) Then
  21.   strWhere = "topID = strTopMax"
  22.   DoCmd.OpenForm "frmTopics", WhereCondition:=strWhere
  23. End If
  24. Else
  25.   'do absolutely nothing
  26. End If
  27.  
  28. DoCmd.SetWarnings (True)
  29.  
  30. End Sub
Sep 25 '08 #12

100+
P: 167
Your code seems to skip steps.
Line 13 must be placed before line 11.

You need to execute SQL1
Save that new record
Find TopMax value (that will give the ID of that new record you just added)
Construct SQL2
Execute SQL 2
.........
Sep 25 '08 #13

WyvsEyeView
P: 46
Okay, I am taking another approach altogether. It's not as elegant as what I was trying for here but I understand it and can make it work. I have one last obstacle to overcome and then I think I will be on my way.

After creating the new topic, I then want to run this SQL to copy all attributes associated with the old topic and create new identical records, except, of course, with the top_id now being the id of the newly-created topic. I'm trying to use DMax to specify that new top_id, without success. Please set me straight!

Expand|Select|Wrap|Line Numbers
  1. INSERT INTO tblTopicAttributes ( top_id, attr_id, attrval_id )
  2. SELECT tblTopicAttributes.top_id, tblTopicAttributes.attr_id, tblTopicAttributes.attrval_id
  3. FROM tblTopicAttributes
  4. WHERE (((tblTopicAttributes.top_id)=DMax("topID","tblTopics")) And ((tblTopicAttributes.attr_id)=Forms!frmTopicAttributes!attr_id) And ((tblTopicAttributes.attrval_id)=Forms!frmTopicAttributes!attrval_id));
(Just noticed that the site is putting some spaces into the SQL that aren't there in my actual SQL. I can't correct, so if you see them as well, please don't conclude that is the problem :)
Sep 25 '08 #14

WyvsEyeView
P: 46
I just wanted to announce that I finally cobbled together a solution for this. Rather than try to put my SQL directly in the code, which was proving infuriatingly problematic, I just decided to save the queries and call them by name from the code.

So my approach was:
1. copy the topic and create a new record in tblTopics
2. copy the topic's attributes and create new records in tblTopicAttributes with no topicID
3. optionally copy the topic's comments and create new records in tblTopicComments with no topicID
4. use DMax to find the ID of the new topic and assign it as the topicID to the new attributes
5. use DMax to find the ID of the new topic and assign it as the topicID to the new comments
6. optionally go to the new topic and initially display title in red italics as visual cue it's a copy

I used five separate queries for this operation. I probably could have combined some of them, but I prefer the modular approach :)

If anyone is interested, here is the code:

Expand|Select|Wrap|Line Numbers
  1. DoCmd.SetWarnings (False)
  2.  
  3. Dim stDocName As String
  4. Dim stLinkCriteria As String
  5. Dim UserChoice As VbMsgBoxResult
  6.  
  7. strMsg1 = "You are about to copy this topic and its attributes." & vbCrLf & "Do you want to copy its comments too?"
  8.  
  9. UserChoice = MsgBox(strMsg1, vbYesNoCancel)
  10.  
  11. If UserChoice = vbYes Then
  12.   'run code to copy topic and attributes and comments
  13.  
  14.   stDocName = "qryCopyTopic"
  15.   DoCmd.OpenQuery stDocName, acNormal, acEdit
  16.  
  17.   stDocName = "qryCopyTopicAttr"
  18.   DoCmd.OpenQuery stDocName, acNormal, acEdit
  19.  
  20.   stDocName = "qryUpdateTopAttrID"
  21.   DoCmd.OpenQuery stDocName, acNormal, acEdit
  22.  
  23.   stDocName = "qryCopyTopicCmts"
  24.   DoCmd.OpenQuery stDocName, acNormal, acEdit
  25.  
  26.   stDocName = "qryUpdateTopCmtID"
  27.   DoCmd.OpenQuery stDocName, acNormal, acEdit
  28.  
  29.   stDocName = "frmTopics"
  30.  
  31.   strMsg2 = "The topic, attributes and comments have been successfully copied." & vbCrLf & "Click OK to view them or Cancel to remain on the current topic."
  32.  
  33.   If MsgBox(strMsg2, vbOKCancel) = vbOK Then
  34.     stLinkCriteria = "[topID]=" & DMax("TopID", "tblTopics")
  35.     DoCmd.OpenForm stDocName, , , stLinkCriteria
  36.     Forms!frmTopics!txtDesc.ForeColor = 255
  37.     Forms!frmTopics!txtDesc.FontItalic = True
  38.   Else
  39.   'do absolutely nothing
  40.   End If
  41.  
  42. ElseIf UserChoice = vbNo Then
  43.   'run code to copy topic and attributes only
  44.  
  45.   stDocName = "qryCopyTopic"
  46.   DoCmd.OpenQuery stDocName, acNormal, acEdit
  47.  
  48.   stDocName = "qryCopyTopicAttr"
  49.   DoCmd.OpenQuery stDocName, acNormal, acEdit
  50.  
  51.   stDocName = "qryUpdateTopAttrID"
  52.   DoCmd.OpenQuery stDocName, acNormal, acEdit
  53.  
  54.   stDocName = "frmTopics"
  55.  
  56.   strMsg2 = "The topic and attributes have been successfully copied." & vbCrLf & "Click OK to view them or Cancel to remain on the current topic."
  57.  
  58.   If MsgBox(strMsg2, vbOKCancel) = vbOK Then
  59.     stLinkCriteria = "[topID]=" & DMax("TopID", "tblTopics")
  60.     DoCmd.OpenForm stDocName, , , stLinkCriteria
  61.     Forms!frmTopics!txtDesc.ForeColor = 255
  62.     Forms!frmTopics!txtDesc.FontItalic = True
  63.   Else
  64.   'do absolutely nothing
  65.   End If
  66.  
  67. End If
  68.  
  69. DoCmd.SetWarnings (True)
Thanks to everyone who helped me with this...it takes a village! I'm sure there is a better, more elegant way, but this is what I was able to come up with on my own.
Sep 29 '08 #15

Post your reply

Sign in to post your reply or Sign up for a free account.