Help with Option Explicit

  • Thread starter Thread starter Richard
  • Start date Start date
R

Richard

I am using Access 2000 I complied the below code and recived the following
error: Compile error: Variable not defined. If I compile with just Option
Compare Database I get no error, But if use Option Compare Database Option
Explicit I recive
Compile error: Variable not defined.
**********************************************************
Private Sub cmdSearch_Click()
On Error GoTo Err_cmdSearch_Click

strSQL = "UPDATE tblWarehouse " & _
"SET Signature = '" & Me.txtSignature & _
"' WHERE Signature is Null AND [Date]=#" & _
Me.txtDate & "# AND [School]='" & Me.txtSchool & _
"' AND Driver='" & Me.txtdriver & "'"
DoCmd.RunSQL strSQL

Exit_cmdSearch_Click:
Exit Sub

Err_cmdSearch_Click:
MsgBox " Please Try Again! "
Resume Exit_cmdSearch_Click


End Sub
************************************************************
It highlight in yellow on this.. strSQL =

any ideas? the rest of the code compiles with no problems.
 
Richard,

Option Explicit means you have to explicitly dimension variables in each
routine before use.

As such add the line

Dim strSql as string

after your on error line.

Using option explicit is the best option, as it prevents mistyping names
etc... and ending up with wierd coding faults.


swas
 
Thank you,

it compiled. 1 question not having this "Dim strSql as string" effect the
operation of the update query? I would think it would.

swas said:
Richard,

Option Explicit means you have to explicitly dimension variables in each
routine before use.

As such add the line

Dim strSql as string

after your on error line.

Using option explicit is the best option, as it prevents mistyping names
etc... and ending up with wierd coding faults.


swas


Richard said:
I am using Access 2000 I complied the below code and recived the following
error: Compile error: Variable not defined. If I compile with just Option
Compare Database I get no error, But if use Option Compare Database Option
Explicit I recive
Compile error: Variable not defined.
**********************************************************
Private Sub cmdSearch_Click()
On Error GoTo Err_cmdSearch_Click

strSQL = "UPDATE tblWarehouse " & _
"SET Signature = '" & Me.txtSignature & _
"' WHERE Signature is Null AND [Date]=#" & _
Me.txtDate & "# AND [School]='" & Me.txtSchool & _
"' AND Driver='" & Me.txtdriver & "'"
DoCmd.RunSQL strSQL

Exit_cmdSearch_Click:
Exit Sub

Err_cmdSearch_Click:
MsgBox " Please Try Again! "
Resume Exit_cmdSearch_Click


End Sub
************************************************************
It highlight in yellow on this.. strSQL =

any ideas? the rest of the code compiles with no problems.
Click to expand...
Click to expand...
 
Richard,

I wouldn't think so. It just means without Option Explicit you could have

strSql = "Select..."

and the next line

Docmd.runsql strCql

and Access would try and do it, but strCql is nothing.

Stop your code in debug mode and copy strSql into a query, and have a closer
look at it there if the sql isn't working correctly.


swas


Richard said:
Thank you,

it compiled. 1 question not having this "Dim strSql as string" effect the
operation of the update query? I would think it would.

swas said:
Richard,

Option Explicit means you have to explicitly dimension variables in each
routine before use.

As such add the line

Dim strSql as string

after your on error line.

Using option explicit is the best option, as it prevents mistyping names
etc... and ending up with wierd coding faults.


swas


Richard said:
I am using Access 2000 I complied the below code and recived the following
error: Compile error: Variable not defined. If I compile with just Option
Compare Database I get no error, But if use Option Compare Database Option
Explicit I recive
Compile error: Variable not defined.
**********************************************************
Private Sub cmdSearch_Click()
On Error GoTo Err_cmdSearch_Click

strSQL = "UPDATE tblWarehouse " & _
"SET Signature = '" & Me.txtSignature & _
"' WHERE Signature is Null AND [Date]=#" & _
Me.txtDate & "# AND [School]='" & Me.txtSchool & _
"' AND Driver='" & Me.txtdriver & "'"
DoCmd.RunSQL strSQL

Exit_cmdSearch_Click:
Exit Sub

Err_cmdSearch_Click:
MsgBox " Please Try Again! "
Resume Exit_cmdSearch_Click


End Sub
************************************************************
It highlight in yellow on this.. strSQL =

any ideas? the rest of the code compiles with no problems.
Click to expand...
Click to expand...
Click to expand...
 
thank you I will.

swas said:
Richard,

I wouldn't think so. It just means without Option Explicit you could have

strSql = "Select..."

and the next line

Docmd.runsql strCql

and Access would try and do it, but strCql is nothing.

Stop your code in debug mode and copy strSql into a query, and have a closer
look at it there if the sql isn't working correctly.


swas


Richard said:
Thank you,

it compiled. 1 question not having this "Dim strSql as string" effect the
operation of the update query? I would think it would.

swas said:
Richard,

Option Explicit means you have to explicitly dimension variables in each
routine before use.

As such add the line

Dim strSql as string

after your on error line.

Using option explicit is the best option, as it prevents mistyping names
etc... and ending up with wierd coding faults.


swas


:

I am using Access 2000 I complied the below code and recived the following
error: Compile error: Variable not defined. If I compile with just Option
Compare Database I get no error, But if use Option Compare Database Option
Explicit I recive
Compile error: Variable not defined.
**********************************************************
Private Sub cmdSearch_Click()
On Error GoTo Err_cmdSearch_Click

strSQL = "UPDATE tblWarehouse " & _
"SET Signature = '" & Me.txtSignature & _
"' WHERE Signature is Null AND [Date]=#" & _
Me.txtDate & "# AND [School]='" & Me.txtSchool & _
"' AND Driver='" & Me.txtdriver & "'"
DoCmd.RunSQL strSQL

Exit_cmdSearch_Click:
Exit Sub

Err_cmdSearch_Click:
MsgBox " Please Try Again! "
Resume Exit_cmdSearch_Click


End Sub
************************************************************
It highlight in yellow on this.. strSQL =

any ideas? the rest of the code compiles with no problems.
Click to expand...
Click to expand...
Click to expand...
 
I am using Access 2000 I complied the below code and recived the following
error: Compile error: Variable not defined. If I compile with just Option
Compare Database I get no error, But if use Option Compare Database Option
Explicit I recive
Compile error: Variable not defined.
**********************************************************
Private Sub cmdSearch_Click()
On Error GoTo Err_cmdSearch_Click

strSQL = "UPDATE tblWarehouse " & _
"SET Signature = '" & Me.txtSignature & _
"' WHERE Signature is Null AND [Date]=#" & _
Me.txtDate & "# AND [School]='" & Me.txtSchool & _
"' AND Driver='" & Me.txtdriver & "'"
DoCmd.RunSQL strSQL

Exit_cmdSearch_Click:
Exit Sub

Err_cmdSearch_Click:
MsgBox " Please Try Again! "
Resume Exit_cmdSearch_Click


End Sub
************************************************************
It highlight in yellow on this.. strSQL =

any ideas? the rest of the code compiles with no problems.
Click to expand...

Just to add my 2 cents to the other responses, Date is a reserved
Access/VBA/Jet word and should not be used as a field name.
For additional reserved words, see the Microsoft KnowledgeBase article
for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'

For an even more complete list of reserved words, see:
http://www.allenbrowne.com/AppIssueBadWord.html
 
Fredg thanks for the links I ran issue checker on allen browne's site found
a few other thing I need to change..I am puzzled at the date field...How do I
get around this since I would like to connect the date with the records?

fredg said:
I am using Access 2000 I complied the below code and recived the following
error: Compile error: Variable not defined. If I compile with just Option
Compare Database I get no error, But if use Option Compare Database Option
Explicit I recive
Compile error: Variable not defined.
**********************************************************
Private Sub cmdSearch_Click()
On Error GoTo Err_cmdSearch_Click

strSQL = "UPDATE tblWarehouse " & _
"SET Signature = '" & Me.txtSignature & _
"' WHERE Signature is Null AND [Date]=#" & _
Me.txtDate & "# AND [School]='" & Me.txtSchool & _
"' AND Driver='" & Me.txtdriver & "'"
DoCmd.RunSQL strSQL

Exit_cmdSearch_Click:
Exit Sub

Err_cmdSearch_Click:
MsgBox " Please Try Again! "
Resume Exit_cmdSearch_Click


End Sub
************************************************************
It highlight in yellow on this.. strSQL =

any ideas? the rest of the code compiles with no problems.
Click to expand...

Just to add my 2 cents to the other responses, Date is a reserved
Access/VBA/Jet word and should not be used as a field name.
For additional reserved words, see the Microsoft KnowledgeBase article
for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'

For an even more complete list of reserved words, see:
http://www.allenbrowne.com/AppIssueBadWord.html
Click to expand...
 
I went back and renamed my date fields to dDate but I have a combo box set to
field list I use for searching. Now in the drop down I have dDate ? how do I
change it to Date without changing the table again?

Richard said:
Fredg thanks for the links I ran issue checker on allen browne's site found
a few other thing I need to change..I am puzzled at the date field...How do I
get around this since I would like to connect the date with the records?

fredg said:
I am using Access 2000 I complied the below code and recived the following
error: Compile error: Variable not defined. If I compile with just Option
Compare Database I get no error, But if use Option Compare Database Option
Explicit I recive
Compile error: Variable not defined.
**********************************************************
Private Sub cmdSearch_Click()
On Error GoTo Err_cmdSearch_Click

strSQL = "UPDATE tblWarehouse " & _
"SET Signature = '" & Me.txtSignature & _
"' WHERE Signature is Null AND [Date]=#" & _
Me.txtDate & "# AND [School]='" & Me.txtSchool & _
"' AND Driver='" & Me.txtdriver & "'"
DoCmd.RunSQL strSQL

Exit_cmdSearch_Click:
Exit Sub

Err_cmdSearch_Click:
MsgBox " Please Try Again! "
Resume Exit_cmdSearch_Click


End Sub
************************************************************
It highlight in yellow on this.. strSQL =

any ideas? the rest of the code compiles with no problems.
Click to expand...

Just to add my 2 cents to the other responses, Date is a reserved
Access/VBA/Jet word and should not be used as a field name.
For additional reserved words, see the Microsoft KnowledgeBase article
for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'

For an even more complete list of reserved words, see:
http://www.allenbrowne.com/AppIssueBadWord.html
Click to expand...
Click to expand...
 
I went back and renamed my date fields to dDate but I have a combo box set to
field list I use for searching. Now in the drop down I have dDate ? how do I
change it to Date without changing the table again?

Richard said:
Fredg thanks for the links I ran issue checker on allen browne's site found
a few other thing I need to change..I am puzzled at the date field...How do I
get around this since I would like to connect the date with the records?

fredg said:
On Fri, 30 Nov 2007 17:07:01 -0800, Richard wrote:

I am using Access 2000 I complied the below code and recived the following
error: Compile error: Variable not defined. If I compile with just Option
Compare Database I get no error, But if use Option Compare Database Option
Explicit I recive
Compile error: Variable not defined.
**********************************************************
Private Sub cmdSearch_Click()
On Error GoTo Err_cmdSearch_Click

strSQL = "UPDATE tblWarehouse " & _
"SET Signature = '" & Me.txtSignature & _
"' WHERE Signature is Null AND [Date]=#" & _
Me.txtDate & "# AND [School]='" & Me.txtSchool & _
"' AND Driver='" & Me.txtdriver & "'"
DoCmd.RunSQL strSQL

Exit_cmdSearch_Click:
Exit Sub

Err_cmdSearch_Click:
MsgBox " Please Try Again! "
Resume Exit_cmdSearch_Click


End Sub
************************************************************
It highlight in yellow on this.. strSQL =

any ideas? the rest of the code compiles with no problems.

Just to add my 2 cents to the other responses, Date is a reserved
Access/VBA/Jet word and should not be used as a field name.
For additional reserved words, see the Microsoft KnowledgeBase article
for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'

For an even more complete list of reserved words, see:
http://www.allenbrowne.com/AppIssueBadWord.html
Click to expand...
Click to expand...
Click to expand...

I'm not sure I understand your question.
Wherever you have a reference to the field [Date] in your database you
need to change that reference to [dDate], both in VBA and in Access
(Control Source,s Record Sources, SQL, etc.)
In Access, if you do indeed need to refer to the Date() function, not
[dDate], then manually reset that code back to Date().

If your code is in VBA, you could use Edit + Find to search for all
instances of dDate and then change just the ones which should be
Date() back to Date.
 
Fredg,

I use a small search form (popup) that has a combo box and a text box. The
combo is set to field list, the user can select fields to search such as
"date" in the text box they supply a string i.e. (12/1/2007) then press
search.. Well my field name in the combo has changed to dDate. I don't want
the user to see dDate in the drop down. Would it be an alias like ddate=date?
Sorry for my lack of knowledge.

fredg said:
I went back and renamed my date fields to dDate but I have a combo box set to
field list I use for searching. Now in the drop down I have dDate ? how do I
change it to Date without changing the table again?

Richard said:
Fredg thanks for the links I ran issue checker on allen browne's site found
a few other thing I need to change..I am puzzled at the date field...How do I
get around this since I would like to connect the date with the records?

:

On Fri, 30 Nov 2007 17:07:01 -0800, Richard wrote:

I am using Access 2000 I complied the below code and recived the following
error: Compile error: Variable not defined. If I compile with just Option
Compare Database I get no error, But if use Option Compare Database Option
Explicit I recive
Compile error: Variable not defined.
**********************************************************
Private Sub cmdSearch_Click()
On Error GoTo Err_cmdSearch_Click

strSQL = "UPDATE tblWarehouse " & _
"SET Signature = '" & Me.txtSignature & _
"' WHERE Signature is Null AND [Date]=#" & _
Me.txtDate & "# AND [School]='" & Me.txtSchool & _
"' AND Driver='" & Me.txtdriver & "'"
DoCmd.RunSQL strSQL

Exit_cmdSearch_Click:
Exit Sub

Err_cmdSearch_Click:
MsgBox " Please Try Again! "
Resume Exit_cmdSearch_Click


End Sub
************************************************************
It highlight in yellow on this.. strSQL =

any ideas? the rest of the code compiles with no problems.

Just to add my 2 cents to the other responses, Date is a reserved
Access/VBA/Jet word and should not be used as a field name.
For additional reserved words, see the Microsoft KnowledgeBase article
for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'

For an even more complete list of reserved words, see:
http://www.allenbrowne.com/AppIssueBadWord.html
Click to expand...
Click to expand...

I'm not sure I understand your question.
Wherever you have a reference to the field [Date] in your database you
need to change that reference to [dDate], both in VBA and in Access
(Control Source,s Record Sources, SQL, etc.)
In Access, if you do indeed need to refer to the Date() function, not
[dDate], then manually reset that code back to Date().

If your code is in VBA, you could use Edit + Find to search for all
instances of dDate and then change just the ones which should be
Date() back to Date.
Click to expand...
 
Fredg,

I use a small search form (popup) that has a combo box and a text box. The
combo is set to field list, the user can select fields to search such as
"date" in the text box they supply a string i.e. (12/1/2007) then press
search.. Well my field name in the combo has changed to dDate. I don't want
the user to see dDate in the drop down. Would it be an alias like ddate=date?
Sorry for my lack of knowledge.

fredg said:
I went back and renamed my date fields to dDate but I have a combo box set to
field list I use for searching. Now in the drop down I have dDate ? how do I
change it to Date without changing the table again?

:

Fredg thanks for the links I ran issue checker on allen browne's site found
a few other thing I need to change..I am puzzled at the date field...How do I
get around this since I would like to connect the date with the records?

:

On Fri, 30 Nov 2007 17:07:01 -0800, Richard wrote:

I am using Access 2000 I complied the below code and recived the following
error: Compile error: Variable not defined. If I compile with just Option
Compare Database I get no error, But if use Option Compare Database Option
Explicit I recive
Compile error: Variable not defined.
**********************************************************
Private Sub cmdSearch_Click()
On Error GoTo Err_cmdSearch_Click

strSQL = "UPDATE tblWarehouse " & _
"SET Signature = '" & Me.txtSignature & _
"' WHERE Signature is Null AND [Date]=#" & _
Me.txtDate & "# AND [School]='" & Me.txtSchool & _
"' AND Driver='" & Me.txtdriver & "'"
DoCmd.RunSQL strSQL

Exit_cmdSearch_Click:
Exit Sub

Err_cmdSearch_Click:
MsgBox " Please Try Again! "
Resume Exit_cmdSearch_Click


End Sub
************************************************************
It highlight in yellow on this.. strSQL =

any ideas? the rest of the code compiles with no problems.

Just to add my 2 cents to the other responses, Date is a reserved
Access/VBA/Jet word and should not be used as a field name.
For additional reserved words, see the Microsoft KnowledgeBase article
for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'

For an even more complete list of reserved words, see:
http://www.allenbrowne.com/AppIssueBadWord.html
Click to expand...

I'm not sure I understand your question.
Wherever you have a reference to the field [Date] in your database you
need to change that reference to [dDate], both in VBA and in Access
(Control Source,s Record Sources, SQL, etc.)
In Access, if you do indeed need to refer to the Date() function, not
[dDate], then manually reset that code back to Date().

If your code is in VBA, you could use Edit + Find to search for all
instances of dDate and then change just the ones which should be
Date() back to Date.
Click to expand...
Click to expand...

If you use an Alias, then when that alias is selected, Access is going
to look for a field named whatever the Alias is and it won't find it.

There is nothing wrong with having the field named dDate and having
the user select that.
Note: If this was a concern for you, before you changed the name of
the field from Date you could have changed it to "SaleDate" or
"TheDate" or "ServiceDate" or some other more meaningful name so the
user knows what that Date field refers to.
Then your combo box would simply refer to "SaleDate", as it should.

It's still not too late to change the field name again to something
more meaningful.
 
Thank you for your help, I am taking your advice, and your right its not to
late to make changes.

fredg said:
Fredg,

I use a small search form (popup) that has a combo box and a text box. The
combo is set to field list, the user can select fields to search such as
"date" in the text box they supply a string i.e. (12/1/2007) then press
search.. Well my field name in the combo has changed to dDate. I don't want
the user to see dDate in the drop down. Would it be an alias like ddate=date?
Sorry for my lack of knowledge.

fredg said:
On Sat, 1 Dec 2007 00:35:00 -0800, Richard wrote:

I went back and renamed my date fields to dDate but I have a combo box set to
field list I use for searching. Now in the drop down I have dDate ? how do I
change it to Date without changing the table again?

:

Fredg thanks for the links I ran issue checker on allen browne's site found
a few other thing I need to change..I am puzzled at the date field...How do I
get around this since I would like to connect the date with the records?

:

On Fri, 30 Nov 2007 17:07:01 -0800, Richard wrote:

I am using Access 2000 I complied the below code and recived the following
error: Compile error: Variable not defined. If I compile with just Option
Compare Database I get no error, But if use Option Compare Database Option
Explicit I recive
Compile error: Variable not defined.
**********************************************************
Private Sub cmdSearch_Click()
On Error GoTo Err_cmdSearch_Click

strSQL = "UPDATE tblWarehouse " & _
"SET Signature = '" & Me.txtSignature & _
"' WHERE Signature is Null AND [Date]=#" & _
Me.txtDate & "# AND [School]='" & Me.txtSchool & _
"' AND Driver='" & Me.txtdriver & "'"
DoCmd.RunSQL strSQL

Exit_cmdSearch_Click:
Exit Sub

Err_cmdSearch_Click:
MsgBox " Please Try Again! "
Resume Exit_cmdSearch_Click


End Sub
************************************************************
It highlight in yellow on this.. strSQL =

any ideas? the rest of the code compiles with no problems.

Just to add my 2 cents to the other responses, Date is a reserved
Access/VBA/Jet word and should not be used as a field name.
For additional reserved words, see the Microsoft KnowledgeBase article
for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'

For an even more complete list of reserved words, see:
http://www.allenbrowne.com/AppIssueBadWord.html
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail


I'm not sure I understand your question.
Wherever you have a reference to the field [Date] in your database you
need to change that reference to [dDate], both in VBA and in Access
(Control Source,s Record Sources, SQL, etc.)
In Access, if you do indeed need to refer to the Date() function, not
[dDate], then manually reset that code back to Date().

If your code is in VBA, you could use Edit + Find to search for all
instances of dDate and then change just the ones which should be
Date() back to Date.
Click to expand...
Click to expand...

If you use an Alias, then when that alias is selected, Access is going
to look for a field named whatever the Alias is and it won't find it.

There is nothing wrong with having the field named dDate and having
the user select that.
Note: If this was a concern for you, before you changed the name of
the field from Date you could have changed it to "SaleDate" or
"TheDate" or "ServiceDate" or some other more meaningful name so the
user knows what that Date field refers to.
Then your combo box would simply refer to "SaleDate", as it should.

It's still not too late to change the field name again to something
more meaningful.
Click to expand...
 
Back
Top