Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2004
    Posts
    3

    Question Unanswered: Passing Form Values

    I have an MS Access database where users add a new record to a main form. The key field on this form is PROJECT NO. Associated with this form are other forms that are opened by command buttons on the bottom of the main form. These additional forms have fields that are filled in to add additional data to the record being added from the main form. I would like to pass the value of the PROJECT NO field on the main form to any of the forms being opened so the record of the main form is linked by that key field to the opened form. I know that this could be accomplished by using tabs on the main form instead of separate forms, but that is not how the users wish to see it. Any suggestions or sample code would be appreciated.

  2. #2
    Join Date
    Nov 2003
    Posts
    1,487
    To begin with....you will need this Function (if you don't already have it) which we will use to make sure your main form is open for retrieving data from. Place this Function into a code Module located within the Modules section of your database:
    Code:
    Public Function IsLoaded(ByVal strFormName As String) As Boolean
     ' Returns True if the specified form is open in Form view or Datasheet view.
        
        Const conObjStateClosed = 0
        Const conDesignView = 0
        
        If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <> conObjStateClosed Then
            If Forms(strFormName).CurrentView <> conDesignView Then
                IsLoaded = True
            End If
        End If
    End Function
    Now in the On Open event of each Form you are going to open, enter this code (or a close facsimile ):
    Code:
    If IsLoaded("myMainFormName") Then
       Me.myOtherFormProjNumTxtBoxName = Forms![myMainFormName] _
       ![myMainFormProjNumTxtBoxName]
    End If
    There you go. That shooooould work.


  3. #3
    Join Date
    Mar 2004
    Posts
    3
    I have done as you have advised. Here is the code for the form being opened from the main form:

    Private Sub Form_Open(Cancel As Integer)
    If IsLoaded(strFormName) Then
    Me.PROJECT_NO = Forms![PROJ INFO AR]![PROJECT NO]
    End If

    End Sub

    Not sure why the form being opened (Me.) has the field name as
    PROJECT_NO when it should be PROJECT NO.

    Anyway, when the main form is opened, I key the value into the PROJECT NO field, advance to the next field, then click on the command button to open the first form.

    No value appears in the PROJECT NO field for the newly opened form.

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    There's a few ways you can do this. You can pass the value as one of the OpenForm arguments, or you can pass it as an input parameter to the query.

    Eg:

    DoCmd.OpenForm "yourChildForm", acNormal, , "[PROJECT NO] = " & me![PROJECT NO]

    And for the input parameter:

    You could set something like this for the record sources for your child forms:

    SELECT *
    FROM t1
    WHERE t1.[PROJECT NO] = ?

    Then in the input parameters property of the form:

    ? = forms!yourMainForm![PROJECT NO]

  5. #5
    Join Date
    Mar 2004
    Posts
    3

    Passing Values

    Both solutions work.

    Thanks to both.

    bsmiller92

Posting Permissions

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