Results 1 to 13 of 13
  1. #1
    Join Date
    Nov 2005
    Posts
    18

    Unanswered: Form Copy and Pasting Via Visual Basic

    This is for Access 2000:

    I am using the following VB code to open a form then copy everything into a new record but it isn't working. It seems to stop after going to the last record. What am I doing wrong?

    Code:
    Private Sub update_ssr_from_Switchboard_Click()
    
        DoCmd.OpenForm "frm_ssr", acNormal
        DoCmd.GoToRecord , , acLast
        DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
        DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
        DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste Append
    
    End Sub

  2. #2
    Join Date
    Mar 2007
    Posts
    277
    Try:
    Code:
    Private Sub update_ssr_from_Switchboard_Click()
    
        DoCmd.OpenForm "frm_ssr", acNormal
        DoCmd.GoToRecord , , acLast
        DoCmd****nCommand acCmdSelectRecord
        DoCmd****nCommand acCmdCopy
        DoCmd.GoToRecord , , acNewRec
        DoCmd****nCommand acCmdPaste
    
    End Sub
    RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP, ac07, ac10 - WinXP Pro, Win7 Pro
    Please reply to this forum so all may learn.

  3. #3
    Join Date
    Nov 2005
    Posts
    18
    Do I actually use the asterisk? I've never seen a command that calls for them.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    nah the asterisk's are a symptom to measures taken to stop spammers from Roumania

    its docmd.R*U*ncommand (without the asterisks)
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Mar 2007
    Posts
    277
    Healdem got the answer. Spam filter issue.
    RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP, ac07, ac10 - WinXP Pro, Win7 Pro
    Please reply to this forum so all may learn.

  6. #6
    Join Date
    Nov 2005
    Posts
    18
    Oh, ok. Thanks. I'll give it a shot.

  7. #7
    Join Date
    Nov 2005
    Posts
    18
    Quote Originally Posted by RuralGuy View Post
    Try:
    Code:
    Private Sub update_ssr_from_Switchboard_Click()
    
        DoCmd.OpenForm "frm_ssr", acNormal
        DoCmd.GoToRecord , , acLast
        DoCmd****nCommand acCmdSelectRecord
        DoCmd****nCommand acCmdCopy
        DoCmd.GoToRecord , , acNewRec
        DoCmd****nCommand acCmdPaste
    
    End Sub
    Works wonderfully, thank you!

    Jacque

  8. #8
    Join Date
    Mar 2007
    Posts
    277
    That's great! Here's a web site that will help convert those old DoMenuItem commands. Converting DoMenuItem to RunCommand
    RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP, ac07, ac10 - WinXP Pro, Win7 Pro
    Please reply to this forum so all may learn.

  9. #9
    Join Date
    Nov 2005
    Posts
    18
    Here's a conumdrum.... I can I get field that's set as a time/date to automatically update when I get the new record?
    Love the link by the way. Immediatly moved to my bookmarks.

  10. #10
    Join Date
    Mar 2007
    Posts
    277
    Use the BeforeUpdate event of the form to set the TimeStamp field. If this is a DateCreated field then just test for Me.NewRecord first.
    RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP, ac07, ac10 - WinXP Pro, Win7 Pro
    Please reply to this forum so all may learn.

  11. #11
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    That's a great little reference source, Allan!

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  12. #12
    Join Date
    Mar 2007
    Posts
    277
    Glad you liked it Linq.
    RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP, ac07, ac10 - WinXP Pro, Win7 Pro
    Please reply to this forum so all may learn.

  13. #13
    Join Date
    Nov 2005
    Posts
    18
    This is what I ended up going with, it works wonderfully.

    Thanks for all the assistance!

    Code:
    sSql = "Insert Into tbl_ssr ( "
    sSql = sSql & " ssr_highpriority, ssr_weather_spokane_rw, ssr_weather_spokane_fw,"
    sSql = sSql & " ssr_weather_tc_rw, ssr_weather_tc_fw, ssr_weather_central_rw,"
    sSql = sSql & " ssr_weather_central_fw, ssr_weather_lifeflight_rw, ssr_weather_lifeflight_fw,"
    sSql = sSql & " ssr_medical_rotation_spokane, ssr_staffing_issues, ssr_administrator_notes,"
    sSql = sSql & " ssr_rn_notes, ssr_rrt_notes, ssr_comm_center_notes, ssr_mechanic_notes,"
    sSql = sSql & " ssr_pilot_notes, ssr_ambulance_notes, ssr_apparatus_status, ssr_turndowns,"
    sSql = sSql & " ssr_completed_medstar, ssr_completed_lifeflight, ssr_standby, ssr_cancelled,"
    sSql = sSql & " ssr_active, ssr_pending_transports, ssr_pending_pr, ssr_comm_eq_notes,"
    sSql = sSql & " ssr_helipad_cameras, ssr_comm_concerns, ssr_lifeflight_notes, ssr_unusual_stuff ) "
    sSql = sSql & "Select ssr_highpriority, ssr_weather_spokane_rw, ssr_weather_spokane_fw,"
    sSql = sSql & " ssr_weather_tc_rw, ssr_weather_tc_fw, ssr_weather_central_rw,"
    sSql = sSql & " ssr_weather_central_fw, ssr_weather_lifeflight_rw, ssr_weather_lifeflight_fw,"
    sSql = sSql & " ssr_medical_rotation_spokane, ssr_staffing_issues, ssr_administrator_notes,"
    sSql = sSql & " ssr_rn_notes, ssr_rrt_notes, ssr_comm_center_notes, ssr_mechanic_notes,"
    sSql = sSql & " ssr_pilot_notes, ssr_ambulance_notes, ssr_apparatus_status, ssr_turndowns,"
    sSql = sSql & " ssr_completed_medstar, ssr_completed_lifeflight, ssr_standby, ssr_cancelled,"
    sSql = sSql & " ssr_active, ssr_pending_transports, ssr_pending_pr, ssr_comm_eq_notes,"
    sSql = sSql & " ssr_helipad_cameras, ssr_comm_concerns, ssr_lifeflight_notes, ssr_unusual_stuff"
    sSql = sSql & " From tbl_ssr Where ssrnumber = " & vKey
    Jacque

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •