proper declaration of variable 2

[THWatson]

I am trying to do some work on a database that was designed by someone else.

The following code lies behind a command button on a form...

Private Sub CmdUpdatePerformTable_Click()
  DoCmd.Echo False, "Visual Basic code is executing."
  DoCmd.OpenForm "UpDatePERFORM"
  DoCmd.Echo True, "Visual Basic code is executing."
  DoCmd.Echo False, "Visual Basic code is executing."
  [COLOR=blue]rez = UpDatePerform()[/color]
  DoCmd.Echo True, "Visual Basic code is executing."
  DoCmd.Close acForm, "UpDatePERFORM", acSaveYes
  [NumRecordInPerform] = NumRecInPerform()
  If [NumRecordInPerform] > 0 Then
     CmdSession.Visible = True
     DoCmd.GoToControl "CmdSession"
     CmdUpdatePerformTable.Visible = False
  End If
End Sub

I note that the programmer did not include the line "Option Explicit" at the top of any code throughout the database.

When I insert "Option Explicit" and attempt to compile the code the line in blue above results in a "variable not defined" error.

As will be noted the line rez = UpDatePerform() calls a function from a module.

How do I properly declare "rez" so that I can include the "Option Explicit" line at the top of this form's code?

Tom

 

[THWatson]

For further information, this is the code that is called by the "rez = UpdatePerform()" line.

Function UpDatePerform()
On Error GoTo Err_UpDatePerform1
    DoCmd.DeleteObject acTable, "PERFORM"
Err_UpDatePerform1:
On Error GoTo Err_UpDatePerform2
    DoCmd.DeleteObject acTable, "tmpADJUDICinSESSION"
Err_UpDatePerform2:
    Dim db As Database
    Dim myQPerformQQ As QueryDef
    Dim myQ As QueryDef
    Dim intState As Integer
    Dim myForm As Form
    Set db = CurrentDb()
    Set myQ = db.QueryDefs("listADJUDICinSESSIONQ")
    myQ.Execute
    myQ.Close
    Set myQPerformQQ = db.QueryDefs("PerformQQ")
    myQPerformQQ.Execute
    myQPerformQQ.Close
    rezReCalc = RecalAll_SESSIONS_START_END_DATE()
    If Not rezReCalc Then
       re = EmptyT("PERFORM")
    End If
    UpDatePerform = rezReCalc
End Function

Tom

 

[PHV]

Dim rez

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[THWatson]

PHV
Ah, I see. This declares the variable as a variant.

I would appreciate your further advice:

Nowhere throughout the database does the designer include the line "Option Explicit" in code behind forms, and there are lots of forms.

Would you recommend...
1. That I go through all the forms, include the line "Option Explicit" and declare the various undeclared variables as Variant, and thus avoid the undeclared variable error
or
2. Leave the "Option Explicit" line out and move on

Tom

 

[PHV]

I personally insist on Option Explicit.

 

[THWatson]

Thanks. That was the reply I expected.

I always use Option Explicit. It's an interesting chore to go through a sizable database designed by someone else. Unfortunately, the designer is no longer available.

Your guidance is much appreciated.

Tom

 

[SkipVought]

ahhhh, gone but not forgotten.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[MajP]

Tom,
Here are a couple suggestions to improve your general coding. This will limit problems in the future and help with debugging.

1)Try not to use error checking for programming flow. It looks like you are trying let the program throw an error if the table does not exist. (Not sure what else would cause the error in the delete). So roll yourself a reuseable function.

Public Function tableExists(tblName As String) As Boolean
  Dim tblDef As TableDef
  For Each tblDef In CurrentDb.TableDefs
    If tblDef.Name = tblName Then tableExists = True
  Next tblDef
End Function

now you can check if any table exist prior to deleting

2) Declare all variables at top of procedure. It just makes debugging a lot easier.
3) Functions should always have return types, if not it is a variant. If you want a variant then still declare it. There are several advantages to specifying the return type.
4) Avoid defining objects and variables that you do not need to reuse.
dim db as dao.database
dim tbl as dao.tabledef
dim fld as dao.field
set tbl = currentdb.tables(1)
set fld = tbl.fields(1)
'If I only need the fld and do not need to reuse the others then simply:
dim fld as dao.field
set fld = currentdb.table(1).fields(1)
or

5)Do not need a tempvariable rezcalc. See 4.
The code becomes more simple and less prone to errors.

Function UpDatePerform() As Boolean
     'Error statement up top
   on error goto errLbl
     '  Then declare your variables
    'dim variable as type
    'Avoid errors from occurring the best you can
    If tableExists("PERFORM") Then
        DoCmd.DeleteObject acTable, "PERFORM"
    End If
    If tableExists("tmpADJUDICinSESSION") Then
        DoCmd.DeleteObject acTable, "tmpADJUDICinSESSION"
    Else
    'I assume these are action queries so simply
    CurrentDb.Execute "listADJUDICinSESSIONQ"
    CurrentDb.Execute "PerformQQ"
    'Do not need the temp variable rezcalc
    UpDatePerform = RecalAll_SESSIONS_START_END_DATE()
    If Not UpDatePerform Then
       UpDatePerform = EmptyT("PERFORM")
    End If
    exit function
errLbl:
    'some error checking here
End Function

 

[THWatson]

MajP
Thanks for the recommendations.

I wasn't really trying to let the program throw an error if the table doesn't exist. The error occurred when I included the "Option Explicit" line.

A lot of the stuff this programmer has done is not familiar to me.

One of the things the Kiwanis Club does is support the annual Music Festival where, this year, 3500 young people performed either as individuals or in groups. It's an amazing event. This particular database was prepared for the Festival Board and I was asked to have a look at a few problems in it. In general, the database works well but with a few issues.

Tom