CODEWITHSUNDEEP
vbavariablesms-accessreference
Dennis vd Eijnde
Dennis vd Eijnde

Reputation: 303

Using a variable to reference to a form in Access

I'm trying to make code that can be used by multiple different forms. My problem is that I can't get the form reference right.

Here is my code:

Dim FormNaam As String

FormNaam = Screen.ActiveForm.Name

'Realisatie_Euro 6_Transferstraat1
If Forms!Productie_Blok1!txtReal_1_1.Enabled = True Then
    rst.AddNew
    rst!Productielijn = Productielijn
    rst!Lijngedeelte = DLookup("LijngedeelteNr", "TblLijngedeelte", "
[productielijn] = productielijn and [omschrijving] = '" & Forms!
(FormNaam)!lbl_1.Caption & "'")

The code that is giving the error is:

DLookup("LijngedeelteNr", "TblLijngedeelte", "[productielijn] = 
productielijn and [omschrijving] = '" & Forms!(FormNaam)!lbl_1.Caption & "'")

When running this I get a compile error saying the type-declaration character does not match declared data type. I have tried all sort of combinations but cant't get it to work.

So my question is: How can I reference to a specific field on a form, using a variable as the form name?

Thanks in advance.

Upvotes: 0

Views: 66

Answers (1)

Andre
Andre

Reputation: 27644

It's either

Forms!constantFormName!Property

or

Forms(strVarFormName)!Property

So: 

Forms(FormNaam)!lbl_1.Caption

or a little more straightforward:

Dim myForm As Access.Form
Set myForm = Screen.ActiveForm

"...[omschrijving] = '" & myForm!lbl_1.Caption & "'"

Upvotes: 1

Related Questions