Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2003

    Question Unanswered: how to pass data from one form to another... please help.

    Hi there. I'm in a real bind and I need some help.

    I need to pass data (same piece of data) from one form to other embedded subforms. On the form "testExam", I have a page control with 3 separate tabs. For each tab, I've embedded three separate subforms. All forms involved, including the container form (testExam) are bound to separate tables. All tables have one common field - PID
    I'd like the user to only have to input the PID on one form, and have it trickle down to all subforms.

    In the Form_Current sub of the testExam form, I have the following code:

    Private Sub Form_Current()
    [PID] = Forms!frmForm1.PID
    End Sub

    Using this code, I am able to pass the data from Form1 to testExam, have the PID on testExam filled in no problems, and have the PID on tab 2 filled in. But tab 1 will not populate automatically. I have no idea why. I don't know where to start troubleshooting this.
    Please help.

  2. #2
    Join Date
    Dec 2004
    Madison, WI
    If setting the default value to the current form's PID field, try setting the value of the field in tab 1 in the Form_Current() event
    Private Sub Form_Current()
    me!PID = Forms!frmForm1!PID
    me!MyTabName!PID = Forms!frmForm1!PID
    or me!MyTabeName!Page1!PID = Forms!frmForm1!PID
    end Sub
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Feb 2003

    Red face validation sub forms.

    Thanks for the response. I noticed that for the properties on the subform for tab1, the link properties were missing!

    But a problem still exists in that when i close the main container form, shouldn't it do a save on all subforms too? When i check my tables, the only record that's created is the on the main table, even though the PID field is now being propagated throughout all forms. The tables bound to the sub forms don't get a record created at all.

    Also, when i test each sub form separately, it gives the right error messages about missing values for required fields etc.
    And when i save the form with some questions unanswered (this is a questionnaire application) it correctly runs through my CHeckforMissingValues routine and puts in special default values.
    However, when i run the same form as a subform by launching the "container" form, the only validation that happens is for the container.

  4. #4
    Join Date
    Sep 2006
    Am I missing something here. If the Link Child Fields and Link Master Fields are used, I have found that the value in the Subform (Child Field) gets populated upon any user input.


  5. #5
    Join Date
    Feb 2003

    Exclamation The link is now working... but

    You are right, the user input is now being passed along to all the forms. I was missing a property setting and I didn't notice it before.
    But I am having other problems with these subforms.
    a) the system isn't preventing me from loading the main form, and clicking on tab 2 before filling in any of the details on tab1. when you test the form embedded on tab1 on it's own it works fine.

    b) also, someone on this forum was kind enough to give me a logging script that audits all changes. I'm calling it from the before_update methods of all forms. Again, everything works fine as independant forms but now that i'm trying it as a sub form, it's failing. The error is saying that the form name I'm passing to the method doesn't exist. Please note the code below: (the line with the asterisks is where the error is happening)

    Private Sub Form_BeforeUpdate(Cancel As Integer)

    Call CheckForMissingValues
    'log it
    Call basLogTrans(Me, "PID", PID)
    End Sub

    Audit functions
    Public Function basLogTrans(frm As Form, MyKeyName As Variant, mykey As Variant) As Boolean
    Dim MyDb As DAO.Database
    Dim MyCtrl As Control
    Dim MyMsg As String
    Dim Hist As String

    For Each MyCtrl In frm.Controls
    If (basActiveCtrl(MyCtrl)) Then 'Consider replacing w/ test for "ControlSource"
    If ((MyCtrl.Value <> MyCtrl.OldValue) _
    Or (IsNull(MyCtrl) And Not IsNull(MyCtrl.OldValue)) _
    Or (Not IsNull(MyCtrl) And IsNull(MyCtrl.OldValue))) Then
    If (MyCtrl.ControlType = dbMemo) Then
    Hist = "tblHistMemo"
    Hist = "tblHist"
    End If
    Call basAddHist(Hist, frm.Name, mykey.Name, MyCtrl)
    End If
    End If
    Next MyCtrl

    basLogTrans = True 'Let User know sucess

    End Function
    Public Function basActiveCtrl(Ctl As Control) As Boolean
    Select Case Ctl.ControlType
    Case Is = acTextBox
    basActiveCtrl = True
    Case Is = acLabel
    Case Is = acRectangle
    Case Is = acLine
    Case Is = acImage
    Case Is = acCommandButton
    Case Is = acOptionButton
    Case Is = acCheckBox
    basActiveCtrl = True
    Case Is = acOptionGroup
    Case Is = acBoundObjectFrame
    Case Is = acListBox
    basActiveCtrl = True
    Case Is = acComboBox
    basActiveCtrl = True
    Case Is = acSubform
    Case Is = acObjectFrame
    Case Is = acPageBreak
    Case Is = acPage
    Case Is = acCustomControl
    Case Is = acToggleButton
    Case Is = acTabCtl
    End Select
    End Function

    Public Function basAddHist(Hist As String, frm As String, MyKeyName As String, MyCtrl As Control)

    'FrmName Text 80 Name of the form where change Occured
    'FldName Text 80 Field Name of the changed value
    'dtChg Date/Time 8 Date/Time of Change (MACHINE value!!)
    'OldVal Text 255 Field Value BEFORE change
    'NewVal Text 255 Field Value after change
    'UserId Text 50 User who Made Change
    'MyKey Variant ?? KeyField as Indentified by Caller
    'MyKeyName Text 80 'Key Field Contents

    'tblHistMemo is the same structure except the "type" for the fields
    'OldContents and NewContents are Memo (and therfore the length is "??")

    Dim dbs As DAO.Database
    Dim tblHistTable As DAO.Recordset

    Set dbs = CurrentDb
    Set tblHistTable = dbs.OpenRecordset(Hist, dbOpenDynaset)

    With tblHistTable
    !mykey = Forms(frm).Controls(MyKeyName) *********
    !MyKeyName = MyKeyName
    !frmName = frm
    !FldName = MyCtrl.ControlSource
    !dtChg = Now()
    !UserId = Environ("Username") 'Orlando's Way
    '!UserId = CurrentUser() 'Mike's way, example of current user would be Admin
    !OldVal = MyCtrl.OldValue
    !NewVal = MyCtrl
    End With

    End Function

    As a test, I tried trapping when the user clicks on tab2, to try and validate tab1 some how. But in debug mode, the system doesn't even stop at my breakpoint for this event.

    Private Sub Section_2_Click() ** put a break point here... but it didn't work
    'test for values in section 1???
    If Section_1.Controls.Item(5) Then
    'do something bogus
    End If
    End Sub
    Last edited by dotolee; 01-25-08 at 18:48.

  6. #6
    Join Date
    Sep 2006
    I would suggest that you may have make the Tab2 Invisible or Disable until a certain input has been made on Tab1.


  7. #7
    Join Date
    Feb 2003

    Can't make it invisible

    because... they should be allowed to skip the questions. The only problem is that I need a validation routine to be triggered... this routine will fill in some special values for these skipped fields depending on which question it is etc.

    The real key is that I need to trigger the "before_update" method... when the user navigates away from the tab to another tab.
    Unless I'm using the wrong event to do this validation.
    do you have any suggestions?

  8. #8
    Join Date
    Feb 2003

    Exclamation I've even tried....

    On the container form, I found this sub:
    (frmIESection2 is the name of the form on the second tab)

    Private Sub frmIESection2_Enter()

    Call frmIESection1.Form.BeforeUpdate

    End Sub

    But this gives an error saying that it's an invalid use of the sub.

  9. #9
    Join Date
    Feb 2003

    Thumbs up figured it out.

    used the same sub mentioned before... but calling the .setfocus method on the subform.
    Which seems logical. but it just took forever finding the right pplace to put the logic.
    sheesh. hate being an access forms newbie.

Posting Permissions

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