Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2011
    Posts
    2

    Question Unanswered: Passing parameters between forms

    Hello.
    I've been trying to find this online, but have been unsuccessfull with the wording on my google searches, so please forgive me if my question is obvious and easily found. Any links or wording suggestions are welcome.

    Take for example a scenario with three tables:

    Event
    Event-People
    People

    I can have many people associated to one event, and my navigation is event-based. For that, I have set up an "Event" form with a "People" subform in datasheet mode, very simple stuff.

    I would like to have an "Add new person" button, which would open a subform in datasheet mode displaying the "People" table, and have a button there that would create the entry on the Event-People table linking the Event I am currently selecting on the original form, and the Person I have selected on the popup sub-form.

    How can I do to pass these parameters between forms and popup forms in an elegant, maintainable way?

    on the On-click of the subform's button, I'd like to have something like what I show below, even though I realize my syntax there is most likely completely inadequate:


    INSERT Event, Person into EVENT-PEOPLE

    Selecting frmEvent.Selected(Event_ID), frmPeople.Selected(Person_ID)

    Any help is appreciated.
    (using MS Access 2003. Yeah, I know it's 2011... not my choice, believe me)
    Last edited by Tylo; 09-19-11 at 11:50. Reason: clarification

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    The general syntax for referencing a control in a subform from its parent form is:
    Code:
    Me.ControlSubFormName.Form.ControlName
    Where ControlSubFormName is the name of the control of type SubForm/SubReport in the parent form (the name of this control can be different from the name of the subform itself), and ControlName is the name of the control in the subform you want to reference.

    For referencing the name of a control in the parent form from a subform you use:
    Code:
    Me.Parent.ControlName
    For the INSERT query, from then Parent Form:
    Code:
    Private Sub Command_Add_Click()
    
    ' This procedure is in the class module of the Parent Form.
    '
        Dim strSQL As String
        
        strSQL = "INSERT INTO [EVENT-PEOPLE] ( Event, Person ) VALUES ( " & _
                  Me.Text_Event_ID.Value & ", " & Me.Child_Person.Form.Text_Person_ID.Value & " );"
        CurrentDb.Execute strSQL, dbFailOnError
       
    End Sub
    From the subform:
    Code:
    Public Sub Command_Add_Click()
    
    ' This procedure is in the class module of the Child Form.
    '
    
        Dim strSQL As String
        
        strSQL = "INSERT INTO [EVENT-PEOPLE] ( Event, Person ) VALUES ( " & _
                  Me.Parent.Text_Event_ID.Value & ", " & Me.Text_Person_ID.Value & " );"
        CurrentDb.Execute strSQL, dbFailOnError
        
    End Sub
    Notice however that you won't be able to display a button on a subform in Datasheet view.
    Have a nice day!

  3. #3
    Join Date
    Sep 2011
    Posts
    2
    Thank you, Sinndho!
    Clear, concise explanation! I will try it out, I think that with the button restriction on datasheet forms I'll have to change the approach for this linking, but your answer helped me understand how Access juggles this information back and forth.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Tags for this Thread

Posting Permissions

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