Making function avaliable to all forms

[Joelyue]

Hi,

i have written a function using vba in one of my form but i want it to be available for use in other forms i created too. How do i do that?

The function is shown below.

Public Function GetShift(StaffID As Long, InputDate As Date) As String
On Error GoTo Err_GetShift
' Declare variables
Dim StartDate As Date
Dim ShiftPatternID As Long
Dim ShiftPattern As String
Dim CycleDays As Long
Dim ChangeCnt1 As Long
Dim ChangeCnt2 As Long
Dim ChangeCnt3 As Long
Dim ChangeCnt4 As Long
Dim IDate As Date
Dim ToDate As Date

IDate = Format(InputDate, "mm/dd/yyyy")

'Declare recordset - speed optimisation
Dim rs As Recordset
Dim strSQL As String

strSQL = "SELECT * FROM [tbl_staffShiftPattern],[tbl_shiftPattern] " _
& "WHERE tbl_staffShiftPattern.ShiftPatternID = tbl_ShiftPattern.Index " _
& "AND tbl_staffShiftPattern.StaffID = " & StaffID & ""
Set rs = CurrentDb.OpenRecordset(strSQL)
StartDate = rs!StartDate
ShiftPatternID = rs!ShiftPatternID
ShiftPattern = rs!Pattern
CycleDays = rs!CycleDays

ChangeCnt1 = DCount("[Index]", "tbl_shiftChangeMain", "[RequestorName]= " & StaffID & " AND [FromDate]= #" & IDate & "#")
ChangeCnt2 = DCount("[Index]", "tbl_shiftChangeMain", "[RequestorName]= " & StaffID & " AND [ToDate]= #" & IDate & "#")

ChangeCnt3 = DCount("[Index]", "tbl_shiftChangeSub", "[Name]= " & StaffID & " AND [FromDate]= #" & IDate & "#")
ChangeCnt4 = DCount("[Index]", "tbl_shiftChangeSub", "[Name]= " & StaffID & " AND [ToDate]= #" & IDate & "#")

If InputDate < StartDate Then
GetShift = "Err"
ElseIf ChangeCnt1 = 1 And ChangeCnt2 = 0 Then
ToDate = DLookup("[ToDate]", "tbl_shiftChangeMain", "[RequestorName]= " & StaffID & " AND [FromDate]= #" & IDate & "#")
ToDate = Format(ToDate, "mm/dd/yyyy")
GetShift = Mid(ShiftPattern, (DateDiff("d", StartDate, Format(ToDate, "dd/mm/yyyy")) Mod CycleDays) + 1, 1) + "*"
ElseIf ChangeCnt2 = 1 Then
GetShift = CStr(DLookup("[ToShift]", "tbl_shiftChangeMain", "[RequestorName] = " & StaffID & " AND [ToDate]= #" & IDate & "#")) + "*"
ElseIf ChangeCnt3 = 1 And ChangeCnt4 = 0 Then
ToDate = DLookup("[ToDate]", "tbl_shiftChangeSub", "[Name]= " & StaffID & " AND [FromDate]= #" & IDate & "#")
ToDate = Format(ToDate, "mm/dd/yyyy")
GetShift = Mid(ShiftPattern, (DateDiff("d", StartDate, Format(ToDate, "dd/mm/yyyy")) Mod CycleDays) + 1, 1) + "*"
ElseIf ChangeCnt4 = 1 Then
GetShift = CStr(DLookup("[ToShift]", "tbl_shiftChangeSub", "[Name] = " & StaffID & " AND [ToDate]= #" & IDate & "#")) + "*"
Else
GetShift = Mid(ShiftPattern, (DateDiff("d", StartDate, Format(InputDate, "dd/mm/yyyy")) Mod CycleDays) + 1, 1)
End If

Exit_GetShift:
Set rs = Nothing 'Deassign all objects.
Exit Function
Err_GetShift:
'Error handler here.
Resume Exit_GetShift
End Function

 

[pbaldy]

Generally, move it to a standard module instead of a form module.

 

[DCrake]

Midway through your post you mention speed optimisation. Then you proceed to perform various DCounts and DLookups on the same table
Why not get the data using recordsets for example

ChangeCnt1 = DCount("[Index]", "tbl_shiftChangeMain", "[RequestorName]= " & StaffID & " AND [FromDate]= #" & IDate & "#")
ChangeCnt2 = DCount("[Index]", "tbl_shiftChangeMain", "[RequestorName]= " & StaffID & " AND [ToDate]= #" & IDate & "#")

Dim rs2 As DAO.Recordset

Set Rs2 = CurrentDb.OpenRecordset("Select * from tbl_shiftChangeMain Where RequestorName = " & StaffID )

If Not Rs2.EOF Then

  Do Until Rs2.EOF
   If FromDate = IDate Then
      ChangeCnt1 = ChangeCnt1 + 1
   End If
   If ToDate = IDate Then
      ChangeCnt2 = ChangeCnt2 + 1
   End If
   Rs2.MoveNext
  Loop
End If

As you now have the Recordset(s) for the Requester name you can use this in your subsequent If statements to get the answer(s). A little bit more code but the read/writes and processing time/network traffic is reduced considerably ultimately improving performance.

David

 

[pbaldy]

I'm going to disagree with that logic David (though not with the general theory of avoiding domain aggregates). With a small recordset that would probably perform okay, but with a large one, looping through the entire recordset counting one by one would be very inefficient. I did brief test on a table with 400k records. The DCounts returned their result virtually instantaneously. The recordset loop took over a minute.

The way to use a recordset would be to open it on an SQL statement that returned the count (SELECT Count(*)...WHERE [FromDate]= #11/10/08#)

 

[Joelyue]

Thanks. I will try it out to see if the form loads faster using this method

I'm going to disagree with that logic David (though not with the general theory of avoiding domain aggregates). With a small recordset that would probably perform okay, but with a large one, looping through the entire recordset counting one by one would be very inefficient. I did brief test on a table with 400k records. The DCounts returned their result virtually instantaneously. The recordset loop took over a minute.

The way to use a recordset would be to open it on an SQL statement that returned the count (SELECT Count(*)...WHERE [FromDate]= #11/10/08#)

 

[Joelyue]

What is the diff between a recordset and a DAO.recordset?

 

[pbaldy]

DAO is a type of recordset, ADO being the other. Yours is DAO, and should be disambiguated as such (declared as David declared his).