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

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> How To Copy A Subform Record Using A Main Form Button, Access 2016    
 
   
rmcgaffic
post Aug 16 2019, 04:06 PM
Post#1



Posts: 179
Joined: 18-June 06



Why won't Wizard code work on a main form, but does work on a subform?

Microsoft Access provides a Command Button Wizard to easily duplicate or copy a record using the path Record operations>Duplicate record.

The wizard generates a macro which I have converted to the VBA code below using the MS Access tool.

My main form contains a subform with detail records.

If I create and place the command button [Copy] with this code in the header of the subform, it works like a dream.

However, I would like to place the command button [Copy] with this code on the main form, not the subofrm.

When I do, nothing happens.

Any suggestions of how to copy a subform record by clicking on a command button on the main form?

How should the wizard generated code be modified to achieve this?

Thanks,
Bob


'--------------------------------------------------------------
' Command17_Click
'
'------------------------------------------------------------
Private Sub Command17_Click()
On Error GoTo Command17_Click_Err

On Error Resume Next
DoCmd.RunCommand acCmdSelectRecord
If (MacroError = 0) Then
DoCmd.RunCommand acCmdCopy
End If
If (MacroError = 0) Then
DoCmd.RunCommand acCmdRecordsGoToNew
End If
If (MacroError = 0) Then
DoCmd.RunCommand acCmdSelectRecord
End If
If (MacroError = 0) Then
DoCmd.RunCommand acCmdPaste
End If
If (MacroError <> 0) Then
Beep
MsgBox MacroError.Description, vbOKOnly, ""
End If


Command17_Click_Exit:
Exit Sub

Command17_Click_Err:
MsgBox Error$
Resume Command17_Click_Exit

End Sub


Go to the top of the page
 
theDBguy
post Aug 16 2019, 04:09 PM
Post#2


Access Wiki and Forums Moderator
Posts: 76,300
Joined: 19-June 07
From: SunnySandyEggo


Hi. The code you're using is tied to the current record. Since you want to apply it to the subform record, you'll have to make sure it is referring to the subform record instead of the current record.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
rmcgaffic
post Aug 16 2019, 04:17 PM
Post#3



Posts: 179
Joined: 18-June 06



Thanks DBGuy, but can you give us a hint about how to do so?


Go to the top of the page
 
rmcgaffic
post Aug 16 2019, 04:27 PM
Post#4



Posts: 179
Joined: 18-June 06



I know I could but a mainform command button and have code behind it to execute the code behind the subform command button, but this doesn't seem like a very elegant approach.

Go to the top of the page
 
tina t
post Aug 16 2019, 06:51 PM
Post#5



Posts: 6,066
Joined: 11-November 10
From: SoCal, USA


you could try wrapping the code in a reference to the subform control, as

CODE
With Me!MySubformContainerControlName.Form
    DoCmd.RunCommand acCmdSelectRecord
    If (MacroError = 0) Then
        DoCmd.RunCommand acCmdCopy
    End If
    If (MacroError = 0) Then
        DoCmd.RunCommand acCmdRecordsGoToNew
    End If
    If (MacroError = 0) Then
        DoCmd.RunCommand acCmdSelectRecord
    End If
    If (MacroError = 0) Then
        DoCmd.RunCommand acCmdPaste
    End If
    If (MacroError <> 0) Then
        Beep
        MsgBox MacroError.Description, vbOKOnly, ""
    End If
End With

MySubformContainerControlName refers to the name of the specialized control that "holds" a subform inside of a mainform. the two names may be the same or they may be different. if they are different, make sure you use the name of that control, not the name of the subform itself.

i've had success using the above reference in .mdb versions of Access. i can't say whether it will work in an .accdb database, in general, or in A2016 in particular. all you can do is try it and see.

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
rmcgaffic
post Aug 17 2019, 05:00 AM
Post#6



Posts: 179
Joined: 18-June 06



tina,

thanks for the suggestion. i'll give it a try!

bob
Go to the top of the page
 
rmcgaffic
post Aug 17 2019, 06:41 AM
Post#7



Posts: 179
Joined: 18-June 06



Just wanted to report my MS Access 2016 experience.

My mainform is frmTask and my subform is frmTaskSub.

When I reference the subform as you suggested, a run time error is returned:
Method 'Form' of object '_Subform' failed

I even tried removing the .Form from the With statement, and the code executed
cmdSelectRecord and
cmdCopy

but aborted on
cmdRecordsGoToNew

with another runtime error:
The command or action 'RecordsGoToNew' isn't available now.

Both my subform container and the form used as a subform are named the same: frmTaskSub.

Any suggestions of how to resolve the first error?

CODE
Private Sub Command17_Click()
  With Me!frmTaskSub.Form      
    DoCmd.RunCommand acCmdSelectRecord
    If (MacroError = 0) Then
      DoCmd.RunCommand acCmdCopy
    End If
    If (MacroError = 0) Then
      DoCmd.RunCommand acCmdRecordsGoToNew
    End If
    If (MacroError = 0) Then
      DoCmd.RunCommand acCmdSelectRecord
    End If
    If (MacroError = 0) Then
      DoCmd.RunCommand acCmdPaste
    End If
    If (MacroError <> 0) Then
      Beep
      MsgBox MacroError.Description, vbOKOnly, ""
    End If
  End With
End Sub
Go to the top of the page
 
theDBguy
post Aug 17 2019, 11:31 AM
Post#8


Access Wiki and Forums Moderator
Posts: 76,300
Joined: 19-June 07
From: SunnySandyEggo


QUOTE (rmcgaffic)
Thanks DBGuy, but can you give us a hint about how to do so?

Using your original set of code, try adding the following as the very first line in it:
CODE
Me.frmTaskSub.SetFocus
Hope it helps...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
rmcgaffic
post Aug 17 2019, 01:17 PM
Post#9



Posts: 179
Joined: 18-June 06



dbGuy,

That one line of code you suggested works perfectly.

Thanks,
Bob
Go to the top of the page
 
theDBguy
post Aug 17 2019, 02:11 PM
Post#10


Access Wiki and Forums Moderator
Posts: 76,300
Joined: 19-June 07
From: SunnySandyEggo


Hi Bob. Congratulations! Glad to hear you got it to work. Tina and I were happy to assist. Good luck with your project.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    16th September 2019 - 05:22 PM