Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2014
    Posts
    42
    Provided Answers: 1

    Question Unanswered: Access VBA referencing controls between forms

    Hey everyone,

    So I am still learning VBA in Access and I can not seem to find the syntax to reference form controls other than with "me"

    For example, I have a dialog form open (Form 1). I press a button on Form 1to open a second form (Form 2). There is a button on Form 2, once I press that, I want Form 2 to close another form (Form 3) to open. So many forms!

    My problem is, that when form 2 closes and opens form 3, I want it to reference the ID from form 1 (Form 2 is essentially a warning message form that the user needs to acknowledge), but I don't know the syntax to do this...

    Normally, I would do something like this:
    Code:
    DoCmd.OpenForm "frmEquipmentEntry", , , _
                       "[tblEquipment.EquipmentID]=" & _
                        Me.lbxSelectEquipment.Column(0), acFormReadOnly, acDialog

    But the me doesn't work because that "me" control is actually on Form 1, not Form 2. I have tried some of the below with no luck. If anyone can shed some light on the proper way to do this, I would greatly appreciate it.

    Also, if another explanation is needed, I would be happy to explain another way and/or in more detail.

    Thanks

    Code:
    DoCmd.OpenForm "frmEquipmentEntry", , , _
                       "[tblEquipment.EquipmentID]=" & _
                        [Forms]![frmEquipmentEntry]![lbxSelectEquipment].Column(0), acFormEdit, acDialog
    Code:
    DoCmd.OpenForm "frmEquipmentEntry", , , _
                       "[tblEquipment.EquipmentID]=" & _
                        Forms!frmEquipmentEntry!lbxSelectEquipment.Column(0), acFormEdit, acDialog
    Code:
    DoCmd.OpenForm "frmEquipmentEntry", , , _
                       "[tblEquipment.EquipmentID]=" & _
                        Forms("frmEQuipmentEntry").Controls.Item("lbxSelectEquipment").Column(0), acFormEdit, acDialog

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Use the Forms collection (only works with a form open as a main form, not with a form open as a subform):
    Code:
    Forms("FormName").Controls("ControlName").Value
    Note: You can also create a Public property in a form module:
    Code:
    Public Property Get SomeProperty() As Variant
        SomeProperty = Me.ControlName.Value
    End Property
    
    Public Property Let SomeProperty(Value As Variant)
        Me.ControlName.Value = Value
    End Properrty
    Then:
    Code:
    Forms("FormName").SomeProperty = SomeValue
    Or:
    Code:
    SomeValue = Forms("FormName").SomeProperty
    Have a nice day!

  3. #3
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    495
    Provided Answers: 24
    Yeah, ME is the 1 thing you DONT need to use.
    just the box name..lbxSelectEquipment

    You dont need the column text either...the lbxSelectEquipment data source should already be set to that column.
    [Forms]![frmEquipmentEntry]![lbxSelectEquipment]

  4. #4
    Join Date
    Jun 2014
    Posts
    42
    Provided Answers: 1
    Worked like a charm Sinndho.

    I actually just ran into the subfrm problem you mentioned...

    I added the following to the form module:

    Code:
    Public Property Get propTxtID() As Variant
        propTxtID = Me.txtID.DefaultValue
    End Property
    and tried to reference it on the main parent page and got an error that MS couldn't find the referenced form:

    Code:
    Forms("frmSearchEquipment").propTxtID = compUserKey
    What's going on?

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    This is because a form is not added to the Forms collection when it is open as a subform. You can only reference it through its parent form (i.e. the main form).

    Either you create the property on the main for, but like this:
    Code:
    Public Property Get SomeProperty() As Variant
        SomeProperty = Me.SubFormName.Form.ControlName.Value
    End Property
    
    Public Property Let SomeProperty(Value As Variant)
        Me.SubFormName.Form.ControlName.Value = Value
    End Properrty
    Or the property remains in the subform module but you reference it like this:
    Code:
    Forms("MainFormName").SubFormName.Form.SomeProperty ...
    See: Forms: Refer to Form and Subform properties and controls
    Have a nice day!

  6. #6
    Join Date
    Jun 2014
    Posts
    42
    Provided Answers: 1
    Great. Thanks!

  7. #7
    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
  •