Solved OpenArgs to pass value but not create record. (1 Viewer)

oxicottin

Learning by pecking away....
Local time
Today, 01:52
Joined
Jun 26, 2007
Messages
666
Hello, I have a button cmdAddNewSubFormPage on my subform that that creates a record adds the next sequential number in txtSequentialNumber and then opens a popup frm_ManageSubMenuPage. I wanted to somehow use OpenArgs to open the popup BUT not create the record yet incase you decide not to go threw with creating one. The way it is now is it creates a record then opens the popup form. How else can I do this?

Code:
Private Sub cmdAddNewSubFormPage_Click()
'--------------------------------------------------------------------------------------------------
'Helped: thedbguy@gmail.com 9/25/20 _
by adding Nz(Me.Parent.cboMenuPage, 0)
'
'Purpose: Was getting error because there want a MenuID created yet so in order to supress the error _
it grabs the MenuID from the parent forms combo box cboMenuPage for the string.
'--------------------------------------------------------------------------------------------------
Dim strNxtNum As String
    Dim intMaxNumRecs As Integer

    strNxtNum = DMax("MenuSequentialNumber", "tbl_MenuItems", "[MenuID] = " & Nz(Me.Parent.cboMenuPage, 0)) + 1
    intMaxNumRecs = 9    'Max Number of Records to Allow

    If Me.Recordset.RecordCount >= intMaxNumRecs Then
MsgBox "You cant have more than 9 SubMenu Entries for this Menu Page.", vbInformation, "Over Max"
Exit Sub
    End If

    Me.AllowAdditions = True
DoCmd.GoToRecord , , acNewRec
txtSequentialNumber = strNxtNum
DoCmd.RunCommand acCmdSaveRecord
DoCmd.GoToRecord , , acLast
    Me.AllowAdditions = False

    'Open the frm_ManageSubMenuPage
    cmdEditSubMenuPage_Click
End Sub


Private Sub cmdEditSubMenuPage_Click()
If IsNull(Me.txtRowMenuID) Or Me.txtRowMenuID = "" Then
MsgBox "You cant edit a SubMenu until you add one first", vbInformation, "Missing Data"
Me.cmdAddNewSubFormPage.SetFocus
Exit Sub
    End If

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frm_ManageSubMenuPage"
stLinkCriteria = "[MenuID]=" & Me.txtMenuID & "And [MenuSequentialNumber]=" & Me.txtSequentialNumber
DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormEdit
End Sub
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 22:52
Joined
Oct 29, 2018
Messages
14,544
Hi. I would try changing this line:
Code:
txtSequentialNumber = strNxtNum
into this:
Code:
txtSequentialNumber.DefaultValue = """" & strNxtNum & """"
I actually noticed it the last time I looked at your code but decided not to say anything, since it wasn't the issue we were working on at the time.

Hope this helps...
 

oxicottin

Learning by pecking away....
Local time
Today, 01:52
Joined
Jun 26, 2007
Messages
666
Na its just an issue that was hopefully my last :)

@theDBguy I'm wanting to NOT create the record until the popup closes, just incase you decided not to enter the record. I figured OpenArgs and in the popups BeforeUpdate do something, but what?
 

oxicottin

Learning by pecking away....
Local time
Today, 01:52
Joined
Jun 26, 2007
Messages
666
Attached is what I currently have, the (sfrm_MenuEditor) has an add record button (cmdAddNewSubFormPage). Currently I have it create the new record first in order to get the MenuSequentialNumber and it not then it places a 1 as the MenuSequentialNumber then opens the (frm_ManageSubMenuPage) to add data to that new record. I wanted to have it utilize OpenArgs instead of creating the new record first incase you decide not to and cancel the popup (frm_ManageSubMenuPage). If you want to save the data on the popup click a save button and then it will allow additions ect and create the new record.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 01:52
Joined
Feb 19, 2002
Messages
31,476
This is the line of code that is causing the record to save.
Code:
DoCmd.RunCommand acCmdSaveRecord

You shouldn't even be generating the sequence number in this form if you are not going to add the record here.

I'm guessing that you've over complicated this process. Here's a custom switchboard sample that had no limits for any page but, you could easily add a limit by intercepting the BeforeInsert event and counting the existing records for the current page. If there are aleady 9, cancel the insert and give the user an error message.

If you haven't seen my custom menu sample and you want to, just post back or look for my name and switchboard
 

oxicottin

Learning by pecking away....
Local time
Today, 01:52
Joined
Jun 26, 2007
Messages
666
Pat Ill take a look.... I almost got it figured out though!

In the command button I changed it to:
Code:
'--------------------------------------------------------------------------------------------------
'Helped: thedbguy@gmail.com 9/25/20 _
  by adding Nz(Me.Parent.cboMenuPage, 0)
'
'Purpose: Was getting error because there want a MenuID created yet so in order to supress the error _
  it grabs the MenuID from the parent forms combo box cboMenuPage for the string.
'--------------------------------------------------------------------------------------------------
    Dim intNxtSeqNum, intMaxNumRecs, intMenuID As Integer
  
    intNxtSeqNum = DMax("MenuSequentialNumber", "tbl_MenuItems", "[MenuID] = " & Nz(Me.Parent.cboMenuPage, 0)) + 1
    intMaxNumRecs = 9    'Max Number of Records to Allow
    intMenuID = Nz(Me.Parent.cboMenuPage, 0) 'Gets menu number from MenuPage

    If Me.Recordset.RecordCount >= intMaxNumRecs Then
        MsgBox "You cant have more than 9 SubMenu Entries for this Menu Page.", vbInformation, "Over Max"
        Exit Sub
        Else
      
        'Opens form in OpenArgs to new record acFormAdd
        DoCmd.OpenForm "frm_ManageSubMenuPage", , , , acFormAdd, acDialog, intMenuID & ";" & intNxtSeqNum

    End If
End Sub

In the popup form I added in the on load:
Code:
Private Sub Form_Load()
Dim MenuSequentialNumber As Long
Dim MenuID As Long

'Get list of procedures to run code
    Me.cboProceduresList.RowSource = GetProcedures("mod_RunCode")
  
If Me.OpenArgs & "" <> "" Then
    MenuID = Split(Me.OpenArgs, ";")(0)
    MenuSequentialNumber = Split(Me.OpenArgs, ";")(1)
  
    Me.txtMenuID = MenuID
    Me.txtSequentialNumber = MenuSequentialNumber
  
End If
End Sub
 
Last edited:

oxicottin

Learning by pecking away....
Local time
Today, 01:52
Joined
Jun 26, 2007
Messages
666
Ok I figured it out, I am now able to either save the new record OR cancel and don't save anything.

Added a Cancel button:

Code:
Private Sub cmdCancel_Click()
If Me.Dirty Then 'Undo any changes.
    Me.Undo
End If
'No need to requery sfrm_MenuEditor you are not saving anything just close form
DoCmd.Close acForm, Me.Name
End Sub

Changed the Save and Close button by adding:
Code:
'You entered a menu name so save and close
    If Me.Dirty Then 'Save any changes
        Me.Dirty = False
    End If
    DoCmd.Close acForm, Me.Name
 

Attachments

  • Forum_TempVars Switchboard v5 Final.zip
    158.1 KB · Views: 52

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 22:52
Joined
Oct 29, 2018
Messages
14,544
Hi. Glad to hear you got it sorted out. Good luck with your project.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 01:52
Joined
May 21, 2018
Messages
4,763
@oxicottin,
Here are some dbs that demonstrate saving color values to a table and using for individual controls. One is from @isladogs
 

Attachments

  • Dynamic Settings V3.accdb
    804 KB · Views: 46

Pat Hartman

Super Moderator
Staff member
Local time
Today, 01:52
Joined
Feb 19, 2002
Messages
31,476
It is poor practice to dirty a form before the user does. That is how you end up with this problem. Use the form's BeforeInsert event to populate the foreign key or any other field that needs populating from the data on the form that opened this one. The BeforeInsert event runs ONCE and it runs only for NEW records and it runs only AFTER the user has typed something into a control. So, you may still want a cancel button but at this point in time, the user knows that he typed something into the form so he won't be confused if you give him a message about bad or missing data as he would be if he never typed anything.
 

Users who are viewing this thread

Top Bottom