Results 1 to 7 of 7
  1. #1
    virgilio is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2019
    Posts
    81

    The Command or Action "RecordsGoToNew" isn't available right now

    I'm trying to add a record from a subform using the following code:

    Code:
    Private Sub Command45_Click()   
       Me.AllowAdditions = True
       DoCmd.RunCommand acCmdRecordsGoToNew
       Me!LOT_NUMBER = ""
       Me.AllowAdditions = False
       Call Update_Lot_Button_Click
    End Sub
    and it faults on the second line, 'DoCmd.RunCommand acCmdRecordsGoToNew'
    This works when I invoke the code from a form that isn't in subform.

    I've done some research and found that common errors are that Data Entry should be set to "Yes", I verified that that is true for the data source.


    I also found that the SetFocus method is probably the solution, but I can't figure out how it works, or the idea behind it. Would someone mind explaining this?

    Thanks in advance!

    Paul

    EDIT: The Sub "Update_Lot_Button_Click" that is called at the end does this:

    Code:
    Private Sub Update_Lot_Button_Click()   
       Dim lotNumber As String
       lotNumber = Me!LOT_NUMBER
       DoCmd.Close acForm, "Landing_Page"
       DoCmd.OpenForm "RM_ADD_Form", OpenArgs:=lotNumber
    End Sub

  2. #2
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    If Data Entry is set to YES, then the form is always set for a new record. The Me.AllowAdditions and Docmd.runcommand accmdrecordgotonew are unnecessary.

  3. #3
    virgilio is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2019
    Posts
    81
    Ah, that wasn't very clear of me. I want that query based form NOT to have data entry available, so it should be set to no. I want the "RM_ADD_Form" that opens to allow new records, but that form is based on a table.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    Shouldn't matter if RecordSource is table or query.

    Advise not to allow empty string in fields. Your code is setting field/control to empty string. Why would you want an empty string for lotNumber when opening RM_ADD_Form?

    Command45 button is located within subform?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    virgilio is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2019
    Posts
    81
    Ah, I have no idea how much of this is unorthodox, but I built this part from a Udemy class but the teacher didn't really explain it very well. The second "Update_Lot" sub is the normal path to edit and update a record, the second sub makes a new record with no lot number assigned yet, (no editing allowed on this form) and then calls the second sub to edit and update a blank record.

    I guess struggling to understand this line.

    "DoCmd.RunCommand acCmdRecordsGoToNew"

    Edit:

    Yes, Command45 is in a subform but I set the link Parent/Child setting to share the variable, (I forgot the wording, I'm tremporarily on a different computer).

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    I would not create a record with empty string in field even if I do intend to immediately open and edit. I would open form with acFormAdd parameter.

    I suppose instructor intent was to make use of one form for new and edits and allow for form to open filtered to an existing lot number or a "new" record without having to check if there is a valid lot number provided to filter on.

    If Command45 (could use more descriptive name) purpose is to "Add New RM", I would use:
    Code:
    Private Sub Command45_Click()   
       DoCmd.OpenForm "RM_ADD_Form", , , acFormAdd
       DoCmd.Close acForm, "Landing_Page"
    End Sub
    
    Private Sub Update_Lot_Button_Click()   
       DoCmd.OpenForm "RM_ADD_Form", , , "Lot_Number='" & Me.LOT_Number & "'"
       DoCmd.Close acForm, "Landing_Page"
    End Sub
    Now I assume there is code behind RM_ADD_Form that deals with OpenArgs that would have to be changed or eliminated.

    Or if you want to use OpenArgs (I have done this as well).
    Code:
    Private Sub Command45_Click()   
       Call Update_Lot_Button_Click
    End Sub
    
    Private Sub Update_Lot_Button_Click()   
       DoCmd.OpenForm "RM_ADD_Form", OpenArgs:=Me.Lot_Number
       DoCmd.Close acForm, "Landing_Page"
    End Sub
    And code in RM_ADD_Form Load or Open event
    Code:
    If IsNull(Me.OpenArgs) Then
        DoCmd.RunCommand acCmdRecordsGoToNew
    Else
        Me.Filter = "Lot_Number='" & Me.OpenArgs & "'"
        Me.FilterOn = True
    End If
    Either allows user to abandon new record input without creating a new record with empty string.
    In my db, Landing_Page (MainMenu in my db) never closes. It is sized so other forms cover it. I use Overlapping Windows instead of Tabbed Documents setting.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    virgilio is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2019
    Posts
    81
    Thank you, your second example worked well. That's what I get for using copy pasta code from Udemy, I didn't fully understand what I was doing and I couldn't fix it. Now that I see your solution the answer is very clear! Thanks again.

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

Similar Threads

  1. Replies: 4
    Last Post: 10-02-2019, 04:58 AM
  2. Replies: 2
    Last Post: 03-24-2017, 06:43 PM
  3. Replies: 3
    Last Post: 01-02-2015, 02:06 PM
  4. Replies: 3
    Last Post: 06-09-2012, 08:14 AM
  5. command or action " isn't available
    By laqsb in forum Access
    Replies: 3
    Last Post: 07-23-2010, 10:29 AM

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