Variable Form Reference in VBA

[BlastWaveTech]

I have a VBA module that contains a bunch of functions, and some of those functions are responsible for setting field values on a form. Right now, in the test environment, the form in question is just a standalone, but for production, the form will be moved to a different form (thereby becoming a subform to a new parent), and so all the form references in my code will need to be updated with the new path. Is there a way to store that form reference in a variable or table which I can reference in the code? That way, when the form/subform structure changes, all I will need to do is update the variable/table value instead of combing through potentially dozens of functions to make the update?

For example: I have a form called "MyForm", and a field called "My Field". This MyForm form will be renamed to NewSubform, and will eventually be embedded onto a form called NewForm. I want to write a function that sets the value of Forms!MyForm!MyField to "Done", but I want to 'store' the "Forms!MyForm" part in a variable called MyVariable so that in my VBA I can set the field value like this: MyVariable!MyField.value = "Done". That way, when it's time to move it to production, I only have to change the value of MyVariable to read "Forms!NewForm.Form!NewSubform" once. Does that make sense?

I keep trying different ways of doing it, but they all error out. It seems Access doesn't like using any sort of secondary reference as a Form reference.

 

[Isaac]

and so all the form references in my code will need to be updated with the new path

Not if your functions use the method: Form_FormName.....

 

[theDBguy]

Basically, you can't store code in a variable and expect it to execute. What you can do though is store objects in a variable. Maybe you could try approaching the problem that way.

 

[MajP]

You can use a public variable or a tempvar. Not sure why it is not working for you. Sounds straight forward.

 

[MajP]

Now i see what you are saying. Likely no. You can only save a reference to that form when open. So that pointer goes out of scope if you close the form. You are likely going to have to store the name, then when used create a form reference.

 

[BlastWaveTech]

Right now I'm trying to do the variable declaration in a function, and then Call that function that's setting the variable from within the other function, and it's giving me an Object Required error.

Function SetPath()
    Dim TheForm as Form
    Set TheForm = Forms!MyForm
End Function    '<--I think this is where the variable TheForm is lost, as this closing delcaration pushes it out of scope... I think...

Function SetFieldVal()
    Call SetPath
    TheForm!MyField.Value = "Done"  '<-- But I need to be able to reference it outside that SetPath function
End Function

The reason I need to do this is that I have to be able to call that TheForm variable from many different functions, so it has to be static and remembered from anywhere in the application I run a SetFieldVal function. I might call that function from any other form, or any other function from anywhere else, and it needs to be able to fetch the specific form's path from the variable. Then when the app is updated to a production environment and embedded into another form, I can just go in and update that SetPath function and every other thing gets updated.

I need to be able to store that SetPath value somewhere that isn't lost when I execute a different function (or execute the SetPath within a different function)

 

[MajP]

That way, when the form/subform structure changes, all I will need to do is update the variable/table value instead of combing through potentially dozens of functions to make the update?

Most functions should be written generically where you pass it a form instance

'Agnostic of form passed
Public Function DoSomething(frm as access.form) as variant
DoSomething = frm.something
end function

 

[MajP]

Also simple example showing that you can save a reference to a form and use it in code is attached. Most likely this is not needed. Your function should accept a form reference or a form name and then use that in the function

Function SetFieldVal()
    Call SetPath
    TheForm!MyField.Value = "Done"
End Function

To

Public Function SetFieldVal(formName as string, ControlName as string, TheValue as variant)
   forms("formName").controls("ControlName").value = TheValue
end Function

if you had a global form

Public Function SetFieldVal(ControlName as string, TheValue as variant)
    glblForm.controls("ControlName").value = TheValue
end function

 

[BlastWaveTech]

Okay, I see what you did, and it makes sense, but it doesn't quite solve my problem. Can you take a look at the database I've attached? It has more specific instructions on what I need to do.

 

[MajP]

Not exactly how I would do it, but uses a global variable. Understand this goes out of scope once the main form closes.