Results 1 to 12 of 12
  1. #1
    TazoTazmaniac is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Nov 2014
    Posts
    41

    DoCmd.RunCommand acCmdNewRecord on a subform not working and instead overwriting previous records

    Hi everyone,

    I've tried to google this problem and not found anything that has helped so far so thought I'd bring the problem here. I'll try and explain it as clearly as I can!

    I'm currently updating an old fundraising database for a small charity and trying to automate some of the functions. I've got a form that contains all of the data of all the contacts, and then buttons that run various code to write different letter types. I've now added in a new button that opens a pop up form to select a type of letter that then needs to populate a new record on the subform (containing details of all the letters written to a particular contact) of the original form (that contains all the contact details). The code that I have used for it is copied from a similar button that does the same thing, but without the pop up form. So, easy peasy I thought, I just need to change the form/subform references...except that seems to have made it go wrong! The syntax SEEMS to be right, as it does focus on the subform, and it does populate something, BUT it populates the top record in the subform and doesn't create a new record first. What have I done wrong? Have I made a typo? Messed up the syntax? Done something strange with a string? I'll leave the code below and see if any of you can come up with anything!

    This is the code that works:
    Code:
    Dim strApType As String
            
        strApType = InputBox("What is the appeal type?", "Please Enter Appeal Type", "Operating")
        
            If strApType = vbNullString Then
            
                MsgBox "Letter cancelled.", , "Cancelled"
                Exit Sub
                
            End If
        
        With Me![LettersSentSub]
        .SetFocus
        .Form![Appeal Type].SetFocus
        DoCmd.GoToRecord , , acNewRec
        .Form![Appeal Type] = strApType
        .Form![Date Letter Sent] = Date
        End With
        
        'Run Mail Merge Function: (This part all works fine)
        MailMergeLetters
    
    End Sub
    This is the code that doesn't:


    Code:
    Dim strApType As String
    Dim strCurrentForm As String
    Dim strControlName As String
            
        strApType = InputBox("What is the appeal type?", "Please Enter Appeal Type", "Operating")
        
            If strApType = vbNullString Then
            
                MsgBox "Letter cancelled.", , "Cancelled"
                Exit Sub
                
            End If
        
    If CurrentProject.AllForms("frmTrustCompanyDetailsWithSearch").IsLoaded Then
    
    
            strCurrentForm = "frmTrustCompanyDetailsWithSearch"
    
    
        ElseIf CurrentProject.AllForms("frmPrevGiversTrustCompanyDetailsWithSearch").IsLoaded Then
    
    
            strCurrentForm = "frmPrevGiversTrustCompanyDetailsWithSearch"
    
    
    End If
    
    
    strControlName = "LettersSentSub"
    
    
        With Forms(strCurrentForm).Controls(strControlName)
        .SetFocus
        .Form![Appeal Type].SetFocus
        DoCmd.GoToRecord , , acNewRec                   '''DoCmd.RunCommand acCmdNewRecord
                                                                           '''(I tried this ^^ as an alternative and it came up 
                                                                           '''with an error that said it was cancelled 
                                                                           '''- maybe this is a clue?)
        .Form![Appeal Type] = strApType
        .Form![Date Letter Sent] = Date
        End With
    
    
        'Run Mail Merge Function:  (This part all works fine)
        MailMergeLettersSpecialistAppeal

  2. #2
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    For both frmTrustCompanyDetailsWithSearch and frmPrevGiversTrustCompanyDetailsWithSearch,
    is the name of the CONTROL HOLDING THE SUBFORM named LettersSentSub ?

  3. #3
    TazoTazmaniac is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Nov 2014
    Posts
    41
    Yes, I think so, but to be honest, I just want it to work on one first before I add the same button on the other form! It's the right name for the form that I'm testing it on.

  4. #4
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    Have a look at this form/subform. The CTRL-sfrmJunction here is equivalent to your LettersSentSub control. It is possible that the control name and the subform name are the same. In the example it is not.
    Click image for larger version. 

Name:	ctrl.png 
Views:	11 
Size:	61.5 KB 
ID:	37312

    Click image for larger version. 

Name:	ctrl-2.png 
Views:	11 
Size:	67.6 KB 
ID:	37313

  5. #5
    TazoTazmaniac is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Nov 2014
    Posts
    41
    I hoped they'd be different so I could find the problem, but I've just double checked and unfortunately they are the same and correct, so not the answer yet!

  6. #6
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Do you see the new record line in the subform? Have you tried to hard-code the name of the forms? In the code with problems try something like this:

    Forms!frmTrustCompanyDetailsWithSearch!LettersSent Sub.Form![Appeal Type].SetFocus

    Cheers,
    Vlad

  7. #7
    TazoTazmaniac is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Nov 2014
    Posts
    41
    Just hard-coded it in and exactly the same has happened. Still no luck.

    Code:
     Forms!frmTrustCompanyDetailsWithSearch!LettersSentSub.Form![Appeal Type].SetFocus
    DoCmd.GoToRecord , , acNewRec
    Forms!frmTrustCompanyDetailsWithSearch!LettersSentSub.Form![Appeal Type] = strApType
    Forms!frmTrustCompanyDetailsWithSearch!LettersSentSub.Form![Date Letter Sent] = Date

  8. #8
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Can you go to a new record manually?

  9. #9
    TazoTazmaniac is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Nov 2014
    Posts
    41
    Yup. So this is what I'm seeing before the pop up and you can see there's a place in the subform to add in a new record (excuse the red parts, that's just blocking out the data):
    Attached Thumbnails Attached Thumbnails snipFDB.png  

  10. #10
    TazoTazmaniac is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Nov 2014
    Posts
    41
    I've found something that works!! Yay So, I still have no idea why my original code didn't work, but I changed this line:

    Code:
         DoCmd.GoToRecord , , acNewRec
    to this:


    Code:
        .Form.Recordset.AddNew
    and it's magically worked! I'd be interested if anyone knew why, but I'm very glad I've got it working
    Thanks for all your replies, much appreciated.

  11. #11
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    I think the code that sets up the subform needs to be in the main form, like the first example using ME.
    So the problem becomes how to get the string from the popup, then pass that string to the proper main form so that it can use the Me. syntax.
    Try this. In the popup, get the strApType as usual.
    Then determine which form is loaded. Close that form and reopen it with openargs = to strApType.
    In the on_open event of the form have it check for openargs, and if it finds a value, have it use that value like the first example.

  12. #12
    TazoTazmaniac is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Nov 2014
    Posts
    41
    Thank you davegri - I'll definitely bear that in mind if the fix stops working again. I really appreciate your help.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. DoCmd.RunCommand acCmdSaveRecord Problem
    By musicopr in forum Programming
    Replies: 3
    Last Post: 06-23-2017, 06:45 PM
  2. Replies: 2
    Last Post: 09-27-2016, 09:10 PM
  3. docmd.runcommand accmdpaste - data is NULL
    By dickn in forum Programming
    Replies: 7
    Last Post: 07-16-2013, 04:27 PM
  4. DoCmd.RunCommand acCmdSaveRecord
    By Rick West in forum Programming
    Replies: 3
    Last Post: 04-22-2010, 02:52 PM
  5. Replies: 5
    Last Post: 03-30-2010, 12:53 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums