CODEWITHSUNDEEP
excelvbams-access
zkhan
zkhan

Reputation: 157

Referring to subform record in access

I have the following form, with three subforms:

enter image description here

The button on the top right opens a userform in excel. I would like that userform to already have some of the selected values from these subforms. However, I am unable to refer to the records in the subforms in my VBA code. I've been successful in transferring the values from the main form, such as name etc. but not the ones from the subform. Code in access:

Option Compare Database

Private Sub Toggle159_Click()
    Dim abc As String
    abc = Me.CompanyName

    Dim xlApp As Object 'Excel.Application, xlWB As Excel.Workbook
    Dim xlWB As Object
    Set xlApp = CreateObject("Excel.Application")
    Set xlWB = xlApp.Workbooks.Open("C:\NA\eb\quotegenv2.xlsm") ' specify file
    xlApp.Visible = True
    xlApp.Run "Module3.showFormWithValues", abc


End Sub

In the above code, CompanyName is a control on the mainform. This code runs fine but I've tried to refer to items in the subform using 'me.subformname.form.controlname' but the control name does not appear in the suggestions.

Code in excel for the module:

' placed in code Module3
    Sub showFormWithValues(txt1 As String)
        With UserForm1

            .ClientName.Text = txt1

        End With
    UserForm1.Show
    End Sub

Any help is appreciated.

Upvotes: 0

Views: 222

Answers (1)

Gary Evans
Gary Evans

Reputation: 1890

You have tried to set ClientName before the form is open.

Use a global variable at the top of module 3 to share the data between showFormWithValues and the form.

At the top of Module3 before the first procedure as a public variable as the example below shows: -

Option Explicit

Public StrCN    As String

Sub showFormWithValues(txt1 As String)
    ...
End Eub

In UserForm1 add (or update if it already exists) the following: -

Private Sub UserForm_Activate()
Me.ClientName.Text = Module3.StrCN
End Sub

Upvotes: 0

Related Questions