Form name as a variable in a module

  • Thread starter Thread starter E
  • Start date Start date
E

E

I have a Access 2000 application that originally had 2-3 pages in which the
end user could enter data. The number of forms has grown to double digits
and promises to grow more. On each form the BeforeUpdate event has
essentially the same code. Since we are adding new fields to all the forms I
thought it would be wise to create a modul to call from each form so I would
only have to update the code in 1 module instead of the code for 10+ forms.
My problem is this:

the code makes several references to the name of the form

Partial code Ex:
If Me.Dirty Then
If MsgBox("You are about to exit the form. Do you want to save this
record?", vbYesNo + vbExclamation, "Save") = vbNo Then
DoCmd.RunCommand acCmdUndo
Else
Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb()
Set rst = db.OpenRecordset("Select * from tblEvent")

With rst
.AddNew
!PatID = Forms!frmICD!txtPatID
!ProcID = Forms!frmICD!txtICDID
!ProcType = "ICD Implant"

etc...

In the module, it will not allow me to use Me nor can i specifically
reference the form name since it will be different for each form. How do I
pass the form name as a variable to the module (instead of using me.dirty for
example)?

Sorry for the long post I'm trying to be thorough.

Regards,
 
In your standard module, set up your function so it receives the form as a
variable, e.g.:
Public Function DoSomething(frm As Form)
Debug.Print frm.Name
End Function

Now pass in the form itself from your form. You could set the form's
BeforeUpdate property to:
=DoSomething([Form])
(Note: don't replace Form with your form name!)
Alternatively, you could call the function from the Form_BeforeUpdate event
procedure like this:
Call DoSomething(Me)

Now you can do anything in DoSomething() that you could have done with Me,
just by using frm instead, e.g.:
If frm.Dirty Then ....
 
Thanks for your response. I am getting an "Invalid use of property" error
when I call the function from the BeforeUpdate procedure on the form.

I set-up the function like so in the module:

Public Function FormName(frm As Form)
Debug.Print frm.Name
End Function

In the form I called the function in the BeforeUpdate
Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_BeforeUpdate
Call FormName(Me)
Call TableUpdate

etc...




Allen Browne said:
In your standard module, set up your function so it receives the form as a
variable, e.g.:
Public Function DoSomething(frm As Form)
Debug.Print frm.Name
End Function

Now pass in the form itself from your form. You could set the form's
BeforeUpdate property to:
=DoSomething([Form])
(Note: don't replace Form with your form name!)
Alternatively, you could call the function from the Form_BeforeUpdate event
procedure like this:
Call DoSomething(Me)

Now you can do anything in DoSomething() that you could have done with Me,
just by using frm instead, e.g.:
If frm.Dirty Then ....

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

E said:
I have a Access 2000 application that originally had 2-3 pages in which the
end user could enter data. The number of forms has grown to double digits
and promises to grow more. On each form the BeforeUpdate event has
essentially the same code. Since we are adding new fields to all the
forms I
thought it would be wise to create a modul to call from each form so I
would
only have to update the code in 1 module instead of the code for 10+
forms.
My problem is this:

the code makes several references to the name of the form

Partial code Ex:
If Me.Dirty Then
If MsgBox("You are about to exit the form. Do you want to save this
record?", vbYesNo + vbExclamation, "Save") = vbNo Then
DoCmd.RunCommand acCmdUndo
Else
Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb()
Set rst = db.OpenRecordset("Select * from tblEvent")

With rst
.AddNew
!PatID = Forms!frmICD!txtPatID
!ProcID = Forms!frmICD!txtICDID
!ProcType = "ICD Implant"

etc...

In the module, it will not allow me to use Me nor can i specifically
reference the form name since it will be different for each form. How do
I
pass the form name as a variable to the module (instead of using me.dirty
for
example)?

Sorry for the long post I'm trying to be thorough.

Regards,
Click to expand...
Click to expand...
 
Rename your function.

FormName is the name of a built-in property, which is why it's use is
invalid.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

E said:
Thanks for your response. I am getting an "Invalid use of property" error
when I call the function from the BeforeUpdate procedure on the form.

I set-up the function like so in the module:

Public Function FormName(frm As Form)
Debug.Print frm.Name
End Function

In the form I called the function in the BeforeUpdate
Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_BeforeUpdate
Call FormName(Me)
Call TableUpdate

etc...




Allen Browne said:
In your standard module, set up your function so it receives the form as
a
variable, e.g.:
Public Function DoSomething(frm As Form)
Debug.Print frm.Name
End Function

Now pass in the form itself from your form. You could set the form's
BeforeUpdate property to:
=DoSomething([Form])
(Note: don't replace Form with your form name!)
Alternatively, you could call the function from the Form_BeforeUpdate
event
procedure like this:
Call DoSomething(Me)

Now you can do anything in DoSomething() that you could have done with
Me,
just by using frm instead, e.g.:
If frm.Dirty Then ....

E said:
I have a Access 2000 application that originally had 2-3 pages in which
the
end user could enter data. The number of forms has grown to double
digits
and promises to grow more. On each form the BeforeUpdate event has
essentially the same code. Since we are adding new fields to all the
forms I
thought it would be wise to create a modul to call from each form so I
would
only have to update the code in 1 module instead of the code for 10+
forms.
My problem is this:

the code makes several references to the name of the form

Partial code Ex:
If Me.Dirty Then
If MsgBox("You are about to exit the form. Do you want to save this
record?", vbYesNo + vbExclamation, "Save") = vbNo Then
DoCmd.RunCommand acCmdUndo
Else
Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb()
Set rst = db.OpenRecordset("Select * from tblEvent")

With rst
.AddNew
!PatID = Forms!frmICD!txtPatID
!ProcID = Forms!frmICD!txtICDID
!ProcType = "ICD Implant"

etc...

In the module, it will not allow me to use Me nor can i specifically
reference the form name since it will be different for each form. How
do
I
pass the form name as a variable to the module (instead of using
me.dirty
for
example)?
Click to expand...
Click to expand...
Click to expand...
 
Doh! on my part.

That did the trick. Thank you for your help.

E

Allen Browne said:
Rename your function.

FormName is the name of a built-in property, which is why it's use is
invalid.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

E said:
Thanks for your response. I am getting an "Invalid use of property" error
when I call the function from the BeforeUpdate procedure on the form.

I set-up the function like so in the module:

Public Function FormName(frm As Form)
Debug.Print frm.Name
End Function

In the form I called the function in the BeforeUpdate
Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_BeforeUpdate
Call FormName(Me)
Call TableUpdate

etc...




Allen Browne said:
In your standard module, set up your function so it receives the form as
a
variable, e.g.:
Public Function DoSomething(frm As Form)
Debug.Print frm.Name
End Function

Now pass in the form itself from your form. You could set the form's
BeforeUpdate property to:
=DoSomething([Form])
(Note: don't replace Form with your form name!)
Alternatively, you could call the function from the Form_BeforeUpdate
event
procedure like this:
Call DoSomething(Me)

Now you can do anything in DoSomething() that you could have done with
Me,
just by using frm instead, e.g.:
If frm.Dirty Then ....

I have a Access 2000 application that originally had 2-3 pages in which
the
end user could enter data. The number of forms has grown to double
digits
and promises to grow more. On each form the BeforeUpdate event has
essentially the same code. Since we are adding new fields to all the
forms I
thought it would be wise to create a modul to call from each form so I
would
only have to update the code in 1 module instead of the code for 10+
forms.
My problem is this:

the code makes several references to the name of the form

Partial code Ex:
If Me.Dirty Then
If MsgBox("You are about to exit the form. Do you want to save this
record?", vbYesNo + vbExclamation, "Save") = vbNo Then
DoCmd.RunCommand acCmdUndo
Else
Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb()
Set rst = db.OpenRecordset("Select * from tblEvent")

With rst
.AddNew
!PatID = Forms!frmICD!txtPatID
!ProcID = Forms!frmICD!txtICDID
!ProcType = "ICD Implant"

etc...

In the module, it will not allow me to use Me nor can i specifically
reference the form name since it will be different for each form. How
do
I
pass the form name as a variable to the module (instead of using
me.dirty
for
example)?
Click to expand...
Click to expand...
Click to expand...
 
Great. BTW, if you want a list of the problem names to avoid in Access when
naming your fields, objects, and procedures, here's one to refer to:
Problem names and reserved words
at:
http://allenbrowne.com/AppIssueBadWord.html

There's over 2000 of them, so I don't memorize them: I just refer there when
I need to.
 
Allen Browne said:
Rename your function.

FormName is the name of a built-in property, which is why it's use is
invalid.
Click to expand...
<snip>

Hi Allen

Interesting. I had no idea FormName was a reserved word. I've been using
that word as a (local) constant for years. I notice (via Object Browser)
that it's a hidden property of the Form class, but also for a Report.
Intriguing...
 
Yes, there's a heap of those that cause potential problems.

It took a bit of work putting the list together, but IME it's a resource
worth having.
 
Back
Top