create a new record and related records simultaneously

[WyvsEyeView]

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.

[nico5038]

You can create the query like:

INSERT INTO tblX (field1, field2) SELECT field1, field2 from tblY where ID_tlX = 123
 

Now use the currentdb.execute statement in your code like:

currendb.execute ("INSERT INTO tblX (field1, field2) SELECT field1, field2 from tblY where ID_tlX = 123")
 

Getting the idea ?

Nic;o)

[WyvsEyeView]

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:

  DoCmd.RunCommand acCmdSelectRecord
  DoCmd.RunCommand acCmdCopy
  DoCmd.RunCommand acCmdRecordsGoToNew
  DoCmd.RunCommand acCmdSelectRecord
  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?

[nico5038]

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:

  DoCmd.RunCommand acCmdSelectRecord
  DoCmd.RunCommand acCmdCopy
  DoCmd.RunCommand acCmdRecordsGoToNew
  DoCmd.RunCommand acCmdSelectRecord
  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)

[WyvsEyeView]

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!

[nico5038]

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)

[WyvsEyeView]

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:

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)=[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")?

[WyvsEyeView]

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.

[nico5038]

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)

[WyvsEyeView]

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?

[Stewart Ross]

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:

"...
WHERE (((tblTopicAttributes.top_id)=" & [Forms]![frmTopics]![nbrTopID] & "));"

"...
WHERE (((tblTopicAttributes.top_id)='" & [Forms]![frmTopics]![nbrTopID] & "'));"

-Stewart

[WyvsEyeView]

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.

Private Sub cmdCopy_Click()
 
DoCmd.SetWarnings (False)
 
Dim strMsg As String
Dim strSQL1 As String
Dim strSQL2 As String
Dim strTopMax As String
Dim strWhere As String
 
strMsg = "You are about to copy this topic and its attributes."
 
If MsgBox(strMsg, vbOKCancel) = vbOK Then
  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] & "));"
strTopMax = DMax("TopID", "tblTopics")
  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));"
  CurrentDb.Execute strSQL1, dbFailOnError
  CurrentDb.Execute strSQL2, dbFailOnError
  If Me.Dirty Then Me.Dirty = False 'save first.
  If Not IsNull(Me.[actual club]) Then
  strWhere = "topID = strTopMax"
  DoCmd.OpenForm "frmTopics", WhereCondition:=strWhere
End If
Else
  'do absolutely nothing
End If
 
DoCmd.SetWarnings (True)
 
End Sub

[hjozinovic]

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
.........

[WyvsEyeView]

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!

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)=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 :)

[WyvsEyeView]

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:

DoCmd.SetWarnings (False)
 
Dim stDocName As String
Dim stLinkCriteria As String
Dim UserChoice As VbMsgBoxResult
 
strMsg1 = "You are about to copy this topic and its attributes." & vbCrLf & "Do you want to copy its comments too?"
 
UserChoice = MsgBox(strMsg1, vbYesNoCancel)
 
If UserChoice = vbYes Then
  'run code to copy topic and attributes and comments
 
  stDocName = "qryCopyTopic"
  DoCmd.OpenQuery stDocName, acNormal, acEdit
 
  stDocName = "qryCopyTopicAttr"
  DoCmd.OpenQuery stDocName, acNormal, acEdit
 
  stDocName = "qryUpdateTopAttrID"
  DoCmd.OpenQuery stDocName, acNormal, acEdit
 
  stDocName = "qryCopyTopicCmts"
  DoCmd.OpenQuery stDocName, acNormal, acEdit
 
  stDocName = "qryUpdateTopCmtID"
  DoCmd.OpenQuery stDocName, acNormal, acEdit
 
  stDocName = "frmTopics"
 
  strMsg2 = "The topic, attributes and comments have been successfully copied." & vbCrLf & "Click OK to view them or Cancel to remain on the current topic."
 
  If MsgBox(strMsg2, vbOKCancel) = vbOK Then
    stLinkCriteria = "[topID]=" & DMax("TopID", "tblTopics")
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    Forms!frmTopics!txtDesc.ForeColor = 255
    Forms!frmTopics!txtDesc.FontItalic = True
  Else
  'do absolutely nothing
  End If
 
ElseIf UserChoice = vbNo Then
  'run code to copy topic and attributes only
 
  stDocName = "qryCopyTopic"
  DoCmd.OpenQuery stDocName, acNormal, acEdit
 
  stDocName = "qryCopyTopicAttr"
  DoCmd.OpenQuery stDocName, acNormal, acEdit
 
  stDocName = "qryUpdateTopAttrID"
  DoCmd.OpenQuery stDocName, acNormal, acEdit
 
  stDocName = "frmTopics"
 
  strMsg2 = "The topic and attributes have been successfully copied." & vbCrLf & "Click OK to view them or Cancel to remain on the current topic."
 
  If MsgBox(strMsg2, vbOKCancel) = vbOK Then
    stLinkCriteria = "[topID]=" & DMax("TopID", "tblTopics")
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    Forms!frmTopics!txtDesc.ForeColor = 255
    Forms!frmTopics!txtDesc.FontItalic = True
  Else
  'do absolutely nothing
  End If
 
End If
 
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.