Setting Form Name in VBA

[CJ Mora]

I am writing a public function in my database, and I need to pass a variable
from the form function Call statement that sends a "target" form name (not
the calling form name) as a variable. My goal is to allow an HR rep to look
through all of the positions in the DB and select one to use as a template
for several different types of transactions (Hire, Re-Hire, Transfer,
Assign), saving them time from entering it manually.

(It is important to note that combining everything into one form is not an
option. While several fields are common across all of these transaction
types (e.g. each has a position type of Full-time, Part-time, etc.), many
more are unique to each type of transaction (e.g. Assignments have bonuses,
while transfers may trigger changes in benefit eligibility); when I tried to
do this all on one form, I had so many controls on it that I literally had
layers of them and couldn't manage the form.)

To use it, the HR rep goes to one of the above-mentioned Transaction forms
(Hie, Transfer, etc.) and clicks on a "find template" button, which opens the
Template form displaying all the positions within the database and each
position's details. Once the HR rep finds the right position on the Template
Form, they click the "Use Template" button.

The function in question then loads all the template values into variables,
closes the Template Form, and fills-in the variable values to the fields on
the relevant Transaction Form.

I would like to use a single function to do this, instead of writing one for
each Transaction Form; but I can't figure out how to set the form name as a
variable. I've tried declaring the variable as a Form, Object, and as a
String, but I keep getting errors.

For Example:

Instead of having a set code in the form function:

Call TransferTemplate

Public Sub TransferTemplate
...declare variables
...Load variables
Forms![frmTransfer]![txtPositionType].SetFocus
Forms![frmTransfer]![txtPositionType].Value = strType

....and I have to write a different function for each of the forms that the
Template has to send values back to, I want to use a variable like this:

Call TransferTemplate(Forms![frmTransfer])

Public Sub TransferTemplate(varFormName as ?)
...declare variables
...Load variables
varFormName![txtPositionType].SetFocus
varFormName![txtPositionType.Value = strType

Can anyone suggest how I can use a variable as a form name?

Thanks in advance.

---cj

 

[Douglas J. Steele]

Public Sub TransferTemplate(strFormName as String)
...declare variables
...Load variables
Forms(strFormName)![txtPositionType].SetFocus
Forms(strFormName)![txtPositionType.Value = strType

 

[CJ Mora]

Yup! Did the trick!

Looking back at my attempts, I think my mistake was writing it like so:

Forms!(strFormName)![txtPositionType].SetFocus
...and so on, using the exclamation point.

Thanks!

Public Sub TransferTemplate(strFormName as String)
...declare variables
...Load variables
Forms(strFormName)![txtPositionType].SetFocus
Forms(strFormName)![txtPositionType.Value = strType



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I am writing a public function in my database, and I need to pass a
variable
from the form function Call statement that sends a "target" form name (not
the calling form name) as a variable. My goal is to allow an HR rep to
look
through all of the positions in the DB and select one to use as a template
for several different types of transactions (Hire, Re-Hire, Transfer,
Assign), saving them time from entering it manually.

(It is important to note that combining everything into one form is not an
option. While several fields are common across all of these transaction
types (e.g. each has a position type of Full-time, Part-time, etc.), many
more are unique to each type of transaction (e.g. Assignments have
bonuses,
while transfers may trigger changes in benefit eligibility); when I tried
to
do this all on one form, I had so many controls on it that I literally had
layers of them and couldn't manage the form.)

To use it, the HR rep goes to one of the above-mentioned Transaction forms
(Hie, Transfer, etc.) and clicks on a "find template" button, which opens
the
Template form displaying all the positions within the database and each
position's details. Once the HR rep finds the right position on the
Template
Form, they click the "Use Template" button.

The function in question then loads all the template values into
variables,
closes the Template Form, and fills-in the variable values to the fields
on
the relevant Transaction Form.

I would like to use a single function to do this, instead of writing one
for
each Transaction Form; but I can't figure out how to set the form name as
a
variable. I've tried declaring the variable as a Form, Object, and as a
String, but I keep getting errors.

For Example:

Instead of having a set code in the form function:

Call TransferTemplate

Public Sub TransferTemplate
...declare variables
...Load variables
Forms![frmTransfer]![txtPositionType].SetFocus
Forms![frmTransfer]![txtPositionType].Value = strType

...and I have to write a different function for each of the forms that the
Template has to send values back to, I want to use a variable like this:

Call TransferTemplate(Forms![frmTransfer])

Public Sub TransferTemplate(varFormName as ?)
...declare variables
...Load variables
varFormName![txtPositionType].SetFocus
varFormName![txtPositionType.Value = strType

Can anyone suggest how I can use a variable as a form name?

Thanks in advance.

---cj