1

I currently have 2 forms that contains a List Box of records with some Text Box to allow user input. I have a Command Button that inserts the data input into a table, then I requery the List Box which is bounded to the table get the updated data.

The functionality code in both forms are the same so I like to reduce as much repetitive code as possible and am basically trying to do something like this issue:

How to get instance of actual form from MS Access in a Module (VBA)

I have created a class module to try and take the data input in the Text Box from the currently active form and store the value of each Text Box in each property of the class, then reference that in my form's On Click save command.

Option Explicit

Public number As String
Public name As String

Public Function getData()

    Dim frmCurrentForm As Form
    Dim frmName As String

    Set frmCurrentForm = Screen.ActiveForm
    frmName = frmCurrentForm.Name

End Function

I am able to get the current active form's name, but am unsure where to go from here to get the form's Text Box property data input, which as an example, are named textBox_Number and textBox_Name?

The answer for the above issue states to do the following:

Public Sub mFormLoad(p_form As Form)
    'do stuff with p_form
end sub

But using the p_form object to access its property, I can only find the generic properties of a Form, and not specific properties tied to my active form, i.e. textBox_Number and textBox_Name.

Being new to VBA programming, can someone guide me through?

Thanks!

Pangu
  • 3,721
  • 11
  • 53
  • 120

1 Answers1

1

Use the Controls collection:

p_form.Controls("textbox_Number").Value should refer to the value of your textbox.

Alternatively, you could only take a specific form as a parameter:

Public Sub mFormLoad(p_form As Form_MyFormName)
    'do stuff with p_form
End Sub
Erik A
  • 31,639
  • 12
  • 42
  • 67
  • thanks your first suggestion works!...can you elaborate more on the alternative?....you are saying if my form is named `frmMyForm`, then it would be `Public Sub mFormLoad(p_form As frmMyForm)`? – Pangu Feb 20 '18 at 17:50
  • No, if your form is named _frmMyForm_, then it would be `Public Sub mFormLoad(p_form As Form_frmMyForm)`. That approach specifies that you're not taking a form with the generic _Form_ interface (which any form possesses) as a parameter, but that the parameter must possess the `Form_frmMyForm` interface. That way you can only pass one specific form to the sub. – Erik A Feb 20 '18 at 17:55
  • sorry to ask this, still learning vba, but when I declared the `sub` as you suggested with `Form_frmMyForm`, I cannot access any of its properties, i.e. `p_form.` gives me nothing...is there something I'm doing wrong? – Pangu Feb 20 '18 at 18:06
  • The form needs to have a module. You can try adding [Option Explicit](https://stackoverflow.com/a/2454583/7296893) to the top of your module to debug easily. – Erik A Feb 20 '18 at 18:09