Functions and modules.......

[tjewers]

Hi,

I have a large if statment which is used more than once in the code for
a form. I was wondering if it is possible to place the if statment in
a function or module and then to call it. I have tried to place the if
statement into a module, but have gotten a compile error "Invalid
outside procedure," when I do this. I have never really used functions
or modules before and therefore am lost. I have looked on Microsoft's
site, but there was nothing for the error, and I did not know what else
to look up.

Thanks for the help,

Tracy

 

[chrismcbride]

It may be in how you are refering to values within the Function. Why don't you post your code for us to look at...
Chris

 

[tjewers]

Hi, here is an example of my code. I couldn't put all of it in here as it is huge.

Private Sub cmdSearch_Click()
'Query Type Info
Dim strQuery As String
Dim StrQueryInfo As String
Dim strQueryName As String

StrQueryInfo = cmbQuery.Value
Select Case (StrQueryInfo)

'Number of Accidents Reported
Case "Accidents Reported"

strQueryName = "Number_Of_Accidents_Reported"
strQuery = "SELECT Count([CF663:CF663s_Table].[REPORT TYPE]) AS [Number Of Accidents]" & _
"FROM [CF663:CF663s_Table];"

Call AccidentsReported

'Number of Days Off
Case "Days Off"
strQueryName = "Number of Days Off"
strQuery = "SELECT Sum([CF663:CF663s_Table].[DAYS OFF DUTY]) AS [DaysOffDuty]" & _
"FROM [CF663:CF663s_Table];"

Call AccidentsReported

End Select

Set qrySearch = CurrentDb.CreateQueryDef(strQueryName, strQuery)
DoCmd.OpenQuery qrySearch.Name, acViewNormal, acReadOnly

db.QueryDefs.Delete qrySearch.Name

End Sub

Function AccidentsReported(strQuery As String, strQueryName As String, Optional strYearInfo As String, Optional strUnitInfo As String, _
Optional strFormation As String, Optional strPersonnelStatusInfo As String, Optional strSexofVictim As String, Optional strAccidentType As String, _
Optional strAgeOfVictim As String, Optional strHour As String, Optional strBodyPart As String, Optional strInjuryClass As String, _
Optional strDutyStatus As String, Optional strInjurySource As String, Optional strInjuryNature As String, Optional strDaysLightDuty As String, _
Optional strDaysOffDuty As String, Optional strSubunit As String, Optional strUIC As String, Optional db As DATABASE, Optional qrySearch As QueryDef, _
Optional Where As Variant) As String

AccidentsReported

Set db = CurrentDb
strYearInfo = DatePart("yyyy", [CF663:CF663s_Table].Date)
strUnitInfo = [CF663:CF663s_Table].[Unit Ser No]
strSubunit = [CF663:CF663s_Table].[Sub Unit]
strUIC = [CF663:CF663s_Table].UIC
strFormation = [CF663:CF663s_Table].Command
strPersonnelStatusInfo = [CF663:CF663s_Table].[Personal Status]
strSexofVictim = [CF663:CF663s_Table].SEX
strAccidentType = [CF663:CF663s_Table].[Accident Type]
strAgeOfVictim = [CF663:CF663s_Table].AGE
strHour = [CF663:CF663s_Table].HOUR
strBodyPart = [CF663:CF663s_Table].[Body Part]
strInjuryClass = [CF663:CF663s_Table].[Injury Classification]
strDutyStatus = [CF663:CF663s_Table].[Duty Status]
strInjurySource = [CF663:CF663s_Table].[Injury Source]
strInjuryNature = [CF663:CF663s_Table].[Injury Nature]
strDaysLightDuty = [CF663:CF663s_Table].[Days Light Duty]
strDaysOffDuty = [CF663:CF663s_Table].[Days Off Duty]

'Set Where equal to null before starting a new where clause
Where = Null

' Everything all combined
If optAllYearsCombined.Value And optAllUnitsCOmbined.Value And optAllInjuries.Value And _
optAllPersonnel.Value And optAllAges.Value And optBothLightAndOffDuty.Value And optAllDutyStatuses.Value And _
optBothSexes.Value And optAllHours.Value And optAllInjuryClasses.Value And optAllBodyParts.Value And _
optAllInjuryNatures.Value Then

strQuery = strQuery

' All years divided, all units combined, and everything else combined.
ElseIf optAllYearsDivided.Value And optAllUnitsCOmbined.Value And optAllInjuries.Value And _
optAllPersonnel.Value And optAllAges.Value And optBothLightAndOffDuty.Value And optAllDutyStatuses.Value And _
optBothSexes.Value And optAllHours.Value And optAllInjuryClasses.Value And optAllBodyParts.Value And _
optAllInjuryNatures.Value Then

strQuery = strQuery & "[Year([DATE])] AS Year" & _
"GROUP BY Year([DATE]);"

End If
End Function
I have just shown two of the statistics, and two of the if statements. There are lots more really. I hope this gives you an idea of what I am doing and that you can help me.

Tracy

 

[chrismcbride]

Tracey
A few more questions and maybe we can get somewhere...

1. Usually a Function is used to calculate a value and return it to the calling procedure. This is done by assigning the function to a variable (or control, perhaps) within the calling procedure. You do not appear to do this ...

Call AccidentsReported


Should be more like ..

strSQL_Sting_that_I_want_returned = AccedentsReported.

2. Usually a Module level procedure is used without any direct reference to Form level values. By this I mean that you shouldn't say ...

Forms!frmCallingForm!Controlvalue

If you want to supply a value to a Module level procedure or function, you pass it as a parameter. I notice that your function has many parameters any you know to label some as required and others as Optional. However you do not pass values to the parameters from you calling procedure, but try to assign values to them within the function...


strYearInfo = DatePart("yyyy",
[CF663:CF663s_Table].Date)

To go back to the initial call...


strSQL_Sting_that_I_want_returned = AccidentsReported (Forms!frmCallingForm!Date_Value)

3. To revisit this line...


strYearInfo = DatePart("yyyy",
[CF663:CF663s_Table].Date)

It seems that you are trying to directly reference a Table value. If I am correct about this, then this needs to be changes. Firstly you can't refer to a field name within a table when you are looking for one value. You need to find a way to reference only one value from within that table. This is best done by passing the Form value or creating a query to limit the return.

It is possible to have the function evaluate values passed to it by the calling procedure, then build a sqlString and Pass it back. But I am still a little unsure what you want the sqlString for. I, myself, never use QueryDefs, so mayber I am missing something. Try to rebuild a simpler version of this code. Get the values passing back and forth and try to satisfy some basic requirements within the function. Use the Help files as well. They have good, if well hidden, iformation. If I miss any future postings, drop me a line and I will try to help.
Good Luck
Chris