none
opening one form from different forms RRS feed

  • Question

  • Hello.  I have a form (Note) that will be opened from multiple forms.  Not all of the forms will be opened at the same time.  The form load of the Note form would then load the info from the form that opens it.  Each of the forms that opens the Note screen is assigned a RecordTypeID.  An example is the Unit form is 1, the Transaction form is 3, the Vendor form is 2, and so on.  The assigned record type id is in an invisible label on appropriate form.

    What my concern is about what is the best way to open the Note form from all the others?  I have a button on each that opens but the Note form's form load event will error out.  I say that now because if I use an IF statement for loading the form I will be using each form's name (ie; frmUnit, frmTransaction, frmVendor, and so on) and if it isn't open, error.  So, that is why I'm asking for other thoughts, ideas, suggestions that are better.

    Thanks.

    ...John

     
    Monday, September 14, 2015 6:14 PM

Answers

  • Pass the values to the Note form via the OpenArgs mechanism.  You'll find an example of a means of passing multiple arguments as Args.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    Ken Sheridan, Stafford, England

    • Marked as answer by johnboy0276 Monday, September 14, 2015 9:20 PM
    Monday, September 14, 2015 6:22 PM
  • You can use the OpenArgs argument of the OpenForm method.

    When your Note form is opened, the Form_Open() event would read the form's OpenArgs property and use what you've sent along to set up your form.

    • Marked as answer by johnboy0276 Monday, September 14, 2015 9:20 PM
    Monday, September 14, 2015 6:23 PM

All replies

  • Pass the values to the Note form via the OpenArgs mechanism.  You'll find an example of a means of passing multiple arguments as Args.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    Ken Sheridan, Stafford, England

    • Marked as answer by johnboy0276 Monday, September 14, 2015 9:20 PM
    Monday, September 14, 2015 6:22 PM
  • You can use the OpenArgs argument of the OpenForm method.

    When your Note form is opened, the Form_Open() event would read the form's OpenArgs property and use what you've sent along to set up your form.

    • Marked as answer by johnboy0276 Monday, September 14, 2015 9:20 PM
    Monday, September 14, 2015 6:23 PM
  • Thanks to you both.  I had forgotten about the OpenArgs.  I wanted to provide to you what is currently used for the Note button on the Unit form for opening up the Note form.  The Unit form has the record type id of 1.  I also need to pass along the PK of the record that is showing when the Note screen opens.  As you can see there is a DoCmd line followed by what is being passed to the Note form to fill the listbox.  I also included the IF statement in the Form_Load event and the info behind the function that is used in the IF statement in the Note form load.  Would it be better to use the Form_Open rather than the Form_Load of the Note form?  I know RMHD mentioned form open but wanting to clarify.  Not afraid to admit it but I am not an Access programmer.  Mainly filling while the real person is out on leave.  So kind on the comments.  I will see what I can do and report back on the outcome.

    Again, thanks for the reply.

    John

        If IsNull(Me.txtUnitID) Then
            MsgBox "There is no record displayed for the note.", vbOKOnly, "Need Unit:"
            
        Else
            DoCmd.OpenForm "frmNote"
            
            Forms!frmNote!txtRecordID = Forms!frmUnit!txtUnitID
            Forms!frmNote!txtRecordTypeID = Forms!frmUnit!lblRecordTypeID.Caption
        
        End If
    
        If Forms!frmUnit!lblRecordTypeID.Caption = 1 Then
            Me.lstNoteList.RowSource = ""
            Me.lstNoteList.RowSource = FormLoadSelectStatement(Forms!frmUnit!txtUnitID, Forms!frmUnit!lblRecordTypeID.Caption)
    
    Function FormLoadSelectStatement(recordid As Integer, recordtypeid As Integer)
        
        Dim sql As String
        
        sql = "SELECT * " & _
                "FROM dbo_tblNote " & _
                " WHERE Remove = 0 " & _
                    " AND RecordID = " & recordid & _
                    " AND RecordTypeID = " & recordtypeid & _
                " ORDER BY DateNoteAdded DESC"
        
        FormLoadSelectStatement = sql
    
    End Function
    

    Monday, September 14, 2015 8:09 PM
  • Hi John. Pardon me for jumping in but what is the purpose of the Note form? Is it just so the user can have more space to enter notes or comments? If so, you have two more options:

    1. You can try using the Zoombox, or

    2. Use an popup unbound Note form and open it in Modal mode. If you do that, all you need to do is display the note from the record to the popup form, and then reassign it to the field when the user hits the "Done" or "Close" button.

    Just a thought...

    Monday, September 14, 2015 8:40 PM
  • Ken and RMHD, here is what I did so far and it appears to be working.  In the first code box which is the button on the Unit form that opens the Note form I did a small change by combining the 2 lines into the DoCmd line.  I used the ';' to separate the 2 parameters that need to be passed.

        If IsNull(Me.txtUnitID) Then
            MsgBox "There is no record displayed for the note.", vbOKOnly, "Need Unit:"
            
        Else
            DoCmd.OpenForm "frmNote", , , , , , Me.txtUnitID & ";" & Me.lblRecordTypeID.Caption
        
        End If
    

    Next, in the Note forms form load I still used an IF statement but one that accepts the DoCmd args.  The form load also needed to fill the listbox on the Note form with all of the notes associated with the passed args.  So, the 2 fields on the Note form accept the passed args then the sql SELECT statement uses that info to fill the listbox.  The recordid and the recordtypeid are 2 fields in the Note table as well for when SSRS reports are done.  I did not need to alter the function seen in the first posting.

        Dim recordid As Integer
        Dim recordtypeid As Integer
        
        If Not IsNull(Me.OpenArgs) Then
            recordid = Left(OpenArgs, InStr(OpenArgs, ";") - 1)
            recordtypeid = Mid(OpenArgs, InStr(OpenArgs, ";") + 1)
        
            Me.txtRecordID = recordid
            Me.txtRecordTypeID = recordtypeid
            
            Me.lstNoteList.RowSource = ""
            Me.lstNoteList.RowSource = FormLoadSelectStatement(Me.txtRecordID, Me.txtRecordTypeID)
            
            Me.lstNoteList.ColumnCount = 9
            Me.lstNoteList.ColumnWidths = "1000;0;2500;2000;4000;3000;0;0;0"
            Me.txtNoteCount = Me.lstNoteList.ListCount
        
        End If
    

    Now, passing the args like you suggested should work for the remaining forms that will use the Note form.  The Note form form load should accept them as well and the function fill the Note listbox with the appropriate list of notes.

    To DBGuy, how this is being set up does have the Note form open in modal.  The user adds a note associated with the record and then closes the Note form before being allowed to move on.  Instead of using multiple Note forms/tables it was decided to use one Note form/table for all the other forms that would use a Note form.  There will be multiple notes for each different record, especially on several other planned forms.  That is the purpose behind the recordid and recordtypeid.  Using many other forms the recordid will have duplicates but the recordtypeid will not, since each form is assigned its own recordtypeid.  The Note table has no FK in it.  The table field RecordID fills in with the PK of the form that is used for the Note.  But the Note table does have its own PK.  Makes also for easier searching if needed.

    I want to thank you all for responding with the OpenArgs thing.  Have a good rest of your day.

    ...John

    Monday, September 14, 2015 9:20 PM