Can I use a variable as the name of a control in Microsoft Access VBA

Question

I have a Microsoft Access Popup Form which I use to lookup addresses. Once the user has found the postcode, the address is then put into various text boxes on the form it was launched from. The problem is, this popup form is launched from various forms throughout the database and so the text boxes it puts the result into are in different locations.

I tried to work around this in the following way. I have a switchboard which is open at all times so I have a hidden Textbox on there which I programmatically put the name of the form I am launching the popup form from. I then declare a string variable which is set to the current value of this hidden textbox like so:

Dim currentForm As String
currentForm = [Forms]![foo]![bar]

I then tried to put my address details into the relevant textboxes like so:

Forms!currentForm![txtCurrentAdd1] = rst![Line1]

However this isn't working as planned, what am I doing wrong?

Thanks

Answer 1

Either:

Dim currentForm As String
''Not sure where the two parts are coming from
''but you cannot have them like that
currentForm = "foobar"

Forms(currentForm).[txtCurrentAdd1] = rst![Line1]

Or

Dim currentForm As Form
Set currentForm = Forms![foobar]

currentForm![txtCurrentAdd1] = rst![Line1]

You might like to read up on bang vs dot.

Mind you, the whole thing looks a little like you are swimming upstream.

Answer 2

You can access controls on other forms like this:

Dim FormName As String
Dim ControlName As String

FormName = "YourForm"
ControlName = "YourTextbox"

Forms(FormName).Controls(ControlName) = "New Value"