Variable not Defined

  • Thread starter Thread starter Lynne
  • Start date Start date
L

Lynne

Hi All

I have a problem which I'm hopeing someone can help me with.

We have a database which has been working for years. The main form when
opened, gives an autonumber for a new record - also been working for years.
I have been tasked to add 3 separate addtional fields to the table and
subsequently to the form.

I set up just one to check - field called xyz. but when I add xyz which is
just a memo field to the form, then click on form view, I have an error to
say that the variable is not defined for the on open expression.

On investigation for the on open expressions, I have the following in the
code section.....

Private Sub Form_Open(Cancel As Integer)

DoCmd.GoToRecord acDataForm, "NonConformance details", acNewRec, 1
'When the database is opened this runs automatically and opens the data
entry form at a new record (snazzy eh!)
End Sub

I have not touched this section, it was written several years ago and been
working fine.

Could someone please point me in the right direction.

Thanks ever so much

Lynne
 
First of all, when asking about an error be as specific as you can. Include
the exact text of the error message, and an error number if available. Does
the database open after the error message, or what exactly?
As I recall that message can show up for reasons other than problems with
the On Open event. Open the code window (the VBA editor) and add this line
just below Option Compare Database at the top, if it is not there:
Option Explicit
If it was not there, in the VBA editor click Tools > Options. On the Editor
tab check Require Variable Declaration.
Now compile the code (Debug >> Compile). If that goes OK, try setting a
break point at the Private Sub Form_Open line. To do this, click the
vertical bar just to the left of the code window at that line of code. This
will place a dot in the bar, and highlight the line of code. Run the code
by attempting to open the form. Code execution will pause at the break
point, and you will see the VBA editor. Press the F8 key to go to the next
line of code. This will let you identify the line that causes the error.
 
Thanks for this Bruce, I'll try to be more specific now.

I have created a new field called xyz in my table. From design view on the
form, have put field xyz. go to form view and the error I get is

"the expression On Open you entered as the event property setting produced
the following error: Variable not defined."

by clicking on the OK message to remove the error, you are taken directly
back to the design view of the form.

I have checked the code as suggested and yes there is the entry for Option
Explicit.

Changing the required variable declaration, The debug/compile option stops
with the Compile Error - "Variable not defined", by clicking on OK to remove
the error, it highlights "acFormatRFT" which is in a line to send an email
when a button is clicked, the code for it is as follows:

Private Sub Command292_Click()
On Error GoTo Err_Command292_Click

'Emails all corrective action reports
'but is limited to the current record by the record source property in the
report


Dim stDocName As String

stDocName = "Corrective and Preventive Action Report"
DoCmd.SendObject acReport, stDocName, acFormatRFT, Me.email, "lloydca;
perkinsj; ellish", , "Corrective and Preventative Action Report logged as NCN
" & Me.Non_Conformance_number & ", " & Me.Item_number & ", " &
Me.Item_description, "Test.", , []


Exit_Command292_Click:
Exit Sub

so I'm very confused now, as there were no errors until I've put the xyz
field onto the form.

Hope this makes sense
 
I think it's acFormatRTF (rich text format), not acFormatRFT. Also, I note
that you have used the words "preventive" and "preventative". Be sure the
report's spelling is correct.
If you still have problems, reduce the line of code to DoCmd.SendObject to
see if it will generate a blank e-mail. If that works, build the rest of
the line one item at a time.

Lynne said:
Thanks for this Bruce, I'll try to be more specific now.

I have created a new field called xyz in my table. From design view on
the
form, have put field xyz. go to form view and the error I get is

"the expression On Open you entered as the event property setting produced
the following error: Variable not defined."

by clicking on the OK message to remove the error, you are taken directly
back to the design view of the form.

I have checked the code as suggested and yes there is the entry for Option
Explicit.

Changing the required variable declaration, The debug/compile option stops
with the Compile Error - "Variable not defined", by clicking on OK to
remove
the error, it highlights "acFormatRFT" which is in a line to send an email
when a button is clicked, the code for it is as follows:

Private Sub Command292_Click()
On Error GoTo Err_Command292_Click

'Emails all corrective action reports
'but is limited to the current record by the record source property in the
report


Dim stDocName As String

stDocName = "Corrective and Preventive Action Report"
DoCmd.SendObject acReport, stDocName, acFormatRFT, Me.email, "lloydca;
perkinsj; ellish", , "Corrective and Preventative Action Report logged as
NCN
" & Me.Non_Conformance_number & ", " & Me.Item_number & ", " &
Me.Item_description, "Test.", , []


Exit_Command292_Click:
Exit Sub

so I'm very confused now, as there were no errors until I've put the xyz
field onto the form.

Hope this makes sense

BruceM said:
First of all, when asking about an error be as specific as you can.
Include
the exact text of the error message, and an error number if available.
Does
the database open after the error message, or what exactly?
As I recall that message can show up for reasons other than problems with
the On Open event. Open the code window (the VBA editor) and add this
line
just below Option Compare Database at the top, if it is not there:
Option Explicit
If it was not there, in the VBA editor click Tools > Options. On the
Editor
tab check Require Variable Declaration.
Now compile the code (Debug >> Compile). If that goes OK, try setting a
break point at the Private Sub Form_Open line. To do this, click the
vertical bar just to the left of the code window at that line of code.
This
will place a dot in the bar, and highlight the line of code. Run the
code
by attempting to open the form. Code execution will pause at the break
point, and you will see the VBA editor. Press the F8 key to go to the
next
line of code. This will let you identify the line that causes the error.
Click to expand...
Click to expand...
 
Bruce - you're a Star!!!!!!

Thank you ever so much for your help - it was the RFT/RTF which was causing
it.

BruceM said:
I think it's acFormatRTF (rich text format), not acFormatRFT. Also, I note
that you have used the words "preventive" and "preventative". Be sure the
report's spelling is correct.
If you still have problems, reduce the line of code to DoCmd.SendObject to
see if it will generate a blank e-mail. If that works, build the rest of
the line one item at a time.

Lynne said:
Thanks for this Bruce, I'll try to be more specific now.

I have created a new field called xyz in my table. From design view on
the
form, have put field xyz. go to form view and the error I get is

"the expression On Open you entered as the event property setting produced
the following error: Variable not defined."

by clicking on the OK message to remove the error, you are taken directly
back to the design view of the form.

I have checked the code as suggested and yes there is the entry for Option
Explicit.

Changing the required variable declaration, The debug/compile option stops
with the Compile Error - "Variable not defined", by clicking on OK to
remove
the error, it highlights "acFormatRFT" which is in a line to send an email
when a button is clicked, the code for it is as follows:

Private Sub Command292_Click()
On Error GoTo Err_Command292_Click

'Emails all corrective action reports
'but is limited to the current record by the record source property in the
report


Dim stDocName As String

stDocName = "Corrective and Preventive Action Report"
DoCmd.SendObject acReport, stDocName, acFormatRFT, Me.email, "lloydca;
perkinsj; ellish", , "Corrective and Preventative Action Report logged as
NCN
" & Me.Non_Conformance_number & ", " & Me.Item_number & ", " &
Me.Item_description, "Test.", , []


Exit_Command292_Click:
Exit Sub

so I'm very confused now, as there were no errors until I've put the xyz
field onto the form.

Hope this makes sense

BruceM said:
First of all, when asking about an error be as specific as you can.
Include
the exact text of the error message, and an error number if available.
Does
the database open after the error message, or what exactly?
As I recall that message can show up for reasons other than problems with
the On Open event. Open the code window (the VBA editor) and add this
line
just below Option Compare Database at the top, if it is not there:
Option Explicit
If it was not there, in the VBA editor click Tools > Options. On the
Editor
tab check Require Variable Declaration.
Now compile the code (Debug >> Compile). If that goes OK, try setting a
break point at the Private Sub Form_Open line. To do this, click the
vertical bar just to the left of the code window at that line of code.
This
will place a dot in the bar, and highlight the line of code. Run the
code
by attempting to open the form. Code execution will pause at the break
point, and you will see the VBA editor. Press the F8 key to go to the
next
line of code. This will let you identify the line that causes the error.

Hi All

I have a problem which I'm hopeing someone can help me with.

We have a database which has been working for years. The main form
when
opened, gives an autonumber for a new record - also been working for
years.
I have been tasked to add 3 separate addtional fields to the table and
subsequently to the form.

I set up just one to check - field called xyz. but when I add xyz
which
is
just a memo field to the form, then click on form view, I have an error
to
say that the variable is not defined for the on open expression.

On investigation for the on open expressions, I have the following in
the
code section.....

Private Sub Form_Open(Cancel As Integer)

DoCmd.GoToRecord acDataForm, "NonConformance details", acNewRec, 1
'When the database is opened this runs automatically and opens the data
entry form at a new record (snazzy eh!)
End Sub

I have not touched this section, it was written several years ago and
been
working fine.

Could someone please point me in the right direction.

Thanks ever so much

Lynne
Click to expand...
Click to expand...
Click to expand...
 
Glad to help. Sometimes after staring at code for a while it is hard
actually to see it any more.

Lynne said:
Bruce - you're a Star!!!!!!

Thank you ever so much for your help - it was the RFT/RTF which was
causing
it.

BruceM said:
I think it's acFormatRTF (rich text format), not acFormatRFT. Also, I
note
that you have used the words "preventive" and "preventative". Be sure
the
report's spelling is correct.
If you still have problems, reduce the line of code to DoCmd.SendObject
to
see if it will generate a blank e-mail. If that works, build the rest of
the line one item at a time.

Lynne said:
Thanks for this Bruce, I'll try to be more specific now.

I have created a new field called xyz in my table. From design view on
the
form, have put field xyz. go to form view and the error I get is

"the expression On Open you entered as the event property setting
produced
the following error: Variable not defined."

by clicking on the OK message to remove the error, you are taken
directly
back to the design view of the form.

I have checked the code as suggested and yes there is the entry for
Option
Explicit.

Changing the required variable declaration, The debug/compile option
stops
with the Compile Error - "Variable not defined", by clicking on OK to
remove
the error, it highlights "acFormatRFT" which is in a line to send an
email
when a button is clicked, the code for it is as follows:

Private Sub Command292_Click()
On Error GoTo Err_Command292_Click

'Emails all corrective action reports
'but is limited to the current record by the record source property in
the
report


Dim stDocName As String

stDocName = "Corrective and Preventive Action Report"
DoCmd.SendObject acReport, stDocName, acFormatRFT, Me.email,
"lloydca;
perkinsj; ellish", , "Corrective and Preventative Action Report logged
as
NCN
" & Me.Non_Conformance_number & ", " & Me.Item_number & ", " &
Me.Item_description, "Test.", , []


Exit_Command292_Click:
Exit Sub

so I'm very confused now, as there were no errors until I've put the
xyz
field onto the form.

Hope this makes sense

:

First of all, when asking about an error be as specific as you can.
Include
the exact text of the error message, and an error number if available.
Does
the database open after the error message, or what exactly?
As I recall that message can show up for reasons other than problems
with
the On Open event. Open the code window (the VBA editor) and add this
line
just below Option Compare Database at the top, if it is not there:
Option Explicit
If it was not there, in the VBA editor click Tools > Options. On the
Editor
tab check Require Variable Declaration.
Now compile the code (Debug >> Compile). If that goes OK, try setting
a
break point at the Private Sub Form_Open line. To do this, click the
vertical bar just to the left of the code window at that line of code.
This
will place a dot in the bar, and highlight the line of code. Run the
code
by attempting to open the form. Code execution will pause at the
break
point, and you will see the VBA editor. Press the F8 key to go to the
next
line of code. This will let you identify the line that causes the
error.

Hi All

I have a problem which I'm hopeing someone can help me with.

We have a database which has been working for years. The main form
when
opened, gives an autonumber for a new record - also been working for
years.
I have been tasked to add 3 separate addtional fields to the table
and
subsequently to the form.

I set up just one to check - field called xyz. but when I add xyz
which
is
just a memo field to the form, then click on form view, I have an
error
to
say that the variable is not defined for the on open expression.

On investigation for the on open expressions, I have the following
in
the
code section.....

Private Sub Form_Open(Cancel As Integer)

DoCmd.GoToRecord acDataForm, "NonConformance details", acNewRec, 1
'When the database is opened this runs automatically and opens the
data
entry form at a new record (snazzy eh!)
End Sub

I have not touched this section, it was written several years ago
and
been
working fine.

Could someone please point me in the right direction.

Thanks ever so much

Lynne
Click to expand...
Click to expand...
Click to expand...
 
Back
Top