By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,660 Members | 1,557 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,660 IT Pros & Developers. It's quick & easy.

Recordset or Array to memory problem

P: n/a
Kev
Hello,

I have an Access 2003 database running on an XP network.
I have a datasheet subform containing a 28 day roster - shift1 to
shift28.
Each record has 1 RosterEmpID, 1 EmployeeNumber, 28 shift fields, and
1 shiftTotal field and 1 HoursTotal field.

Datasheet may look like:

Employee Shift1 Shift2 Shift3 Shift4 etc...
shiftTotal HoursTotal

Emp1 D D E
E 4 32
Emp2 D
E 2 16
Emp3 N N
N 3
30 (10hr Night shift)

The user enters shift codes into each shift field or leaves them blank
for days off. The shift codes are selected from the Shift table.
The Shift table consists of:

Table field name Example data

ShiftID D
ShiftDescription Day Shift
ShiftStartTime 07:00
ShiftEndTime 15:30
ShiftType D
ShiftPaidTime 480 (in Minutes)
I need to total the shifts and hours for each employee and am unsure
of how to
do this.
For the hours I was thinking of somehow getting the shift information
into memory without many DLookups, possibly as below. Would this place
the table information into memory where I can query it?

If so
Option Compare Database
Option Explicit
Dim db As Database
Dim RosSftrst As Recordset
Dim RosSftSQL As String 'put in form open
Dim sftID As String, sftDesc As String, sftStTime As Date, sftPdTime
As String, hrs As String
Private Sub Form_Open(Cancel As Integer)

RosSftSQL = "Select [ShiftID], [ShiftDescription], [ShiftStartTime],
[ShiftPaidTime] From RosterShifts "

Set db = CurrentDb
Set RosSftrst = db.OpenRecordset(RosSftSQL, dbOpenSnapshot)
If Not RosSftrst.EOF Then
sftID = RosSftrst![ShiftID]
sftDesc = RosSftrst![ShiftDescription]
sftStTime = RosSftrst![ShiftStartTime]
sftPdTime = RosSftrst![ShiftPaidTime]
End If
RosSftrst.Close
Set RosSftrst = Nothing
Set db = Nothing
MsgBox sftID
End Sub

Public Function HoursTot()
Dim shftHrs as string
Do until ShiftTotal
shftHrs = sftPdTime where sftID = me![Shift1] - not sure how
to do this line - IT DOESN'T WORK
hrs = hrs + shftHrs
Goto next control
Loop
Me![HoursTotal] = hrs
End Function

I would also need to convert the minutes to hours somehow. I think
I've seen this before.

Any help greatly appreciated.
Regards
Kevin

Mar 21 '07 #1
Share this Question
Share on Google+
4 Replies


P: n/a
On Mar 21, 2:14 am, "Kev" <kevin.vaug...@nhw.hume.org.auwrote:
Hello,

I have an Access 2003 database running on an XP network.
I have a datasheet subform containing a 28 day roster - shift1 to
shift28.
Each record has 1 RosterEmpID, 1 EmployeeNumber, 28 shift fields, and
1 shiftTotal field and 1 HoursTotal field.

Datasheet may look like:

Employee Shift1 Shift2 Shift3 Shift4 etc...
shiftTotal HoursTotal

Emp1 D D E
E 4 32
Emp2 D
E 2 16
Emp3 N N
N 3
30 (10hr Night shift)

The user enters shift codes into each shift field or leaves them blank
for days off. The shift codes are selected from the Shift table.
The Shift table consists of:

Table field name Example data

ShiftID D
ShiftDescription Day Shift
ShiftStartTime 07:00
ShiftEndTime 15:30
ShiftType D
ShiftPaidTime 480 (in Minutes)

I need to total the shifts and hours for each employee and am unsure
of how to
do this.
For the hours I was thinking of somehow getting the shift information
into memory without many DLookups, possibly as below. Would this place
the table information into memory where I can query it?

If so

Option Compare Database
Option Explicit
Dim db As Database
Dim RosSftrst As Recordset
Dim RosSftSQL As String 'put in form open
Dim sftID As String, sftDesc As String, sftStTime As Date, sftPdTime
As String, hrs As String

Private Sub Form_Open(Cancel As Integer)

RosSftSQL = "Select [ShiftID], [ShiftDescription], [ShiftStartTime],
[ShiftPaidTime] From RosterShifts "

Set db = CurrentDb
Set RosSftrst = db.OpenRecordset(RosSftSQL, dbOpenSnapshot)
If Not RosSftrst.EOF Then
sftID = RosSftrst![ShiftID]
sftDesc = RosSftrst![ShiftDescription]
sftStTime = RosSftrst![ShiftStartTime]
sftPdTime = RosSftrst![ShiftPaidTime]
End If
RosSftrst.Close
Set RosSftrst = Nothing
Set db = Nothing
MsgBox sftID
End Sub

Public Function HoursTot()
Dim shftHrs as string
Do until ShiftTotal
shftHrs = sftPdTime where sftID = me![Shift1] - not sure how
to do this line - IT DOESN'T WORK
hrs = hrs + shftHrs
Goto next control
Loop
Me![HoursTotal] = hrs
End Function

I would also need to convert the minutes to hours somehow. I think
I've seen this before.

Any help greatly appreciated.
Regards
Kevin
One way to keep lookup values in memory would be to use Dictionary
objects. Try this as an example:

Create a new unbound form. Add a combo box that displays the [ShiftID]
values from your [RosterShifts] table. Then create "On Load" and "On
Unload" events for the form, create an "After Update" event for the
combo box, and paste the following bits of code into each. When a new
[ShiftID] value is selected in the combo box a MsgBox will pop up
telling you what the corresponding [ShiftPaidTime] is.

'-----------------------------
Option Compare Database
Option Explicit

' this requires a reference to "Microsoft Scripting Runtime"
Dim ShiftData As Dictionary

Private Sub Combo0_AfterUpdate()
' demonstrate referencing a Dictionary of Dictionary objects
With Me.Combo0
MsgBox "ShiftPaidTime for ShiftID=""" & .Value _
& """ is " & ShiftData(.Value)("ShiftPaidTime")
End With
End Sub

Private Sub Form_Load()
Dim cdb As DAO.Database, rst As DAO.Recordset
Dim ShiftRecord As Dictionary

Set ShiftData = New Dictionary

Set cdb = CurrentDb
Set rst = cdb.OpenRecordset( _
"SELECT ShiftID, ShiftDescription, ShiftPaidTime " & _
"FROM RosterShifts", _
dbOpenSnapshot)
Do While Not rst.EOF
Set ShiftRecord = New Dictionary
With ShiftRecord
.Add "ShiftDescription", rst!ShiftDescription.Value
.Add "ShiftPaidTime", rst!ShiftPaidTime.Value
End With
ShiftData.Add rst!ShiftID.Value, ShiftRecord
Set ShiftRecord = Nothing
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
Set cdb = Nothing
End Sub

Private Sub Form_Unload(Cancel As Integer)
Set ShiftData = Nothing
End Sub

Mar 21 '07 #2

P: n/a
Kev
On Mar 21, 11:01 pm, "Gord" <g...@kingston.netwrote:
On Mar 21, 2:14 am, "Kev" <kevin.vaug...@nhw.hume.org.auwrote:
Hello,
I have an Access 2003 database running on an XP network.
I have a datasheet subform containing a 28 day roster - shift1 to
shift28.
Each record has 1 RosterEmpID, 1 EmployeeNumber, 28 shift fields, and
1 shiftTotal field and 1 HoursTotal field.
Datasheet may look like:
Employee Shift1 Shift2 Shift3 Shift4 etc...
shiftTotal HoursTotal
Emp1 D D E
E 4 32
Emp2 D
E 2 16
Emp3 N N
N 3
30 (10hr Night shift)
The user enters shift codes into each shift field or leaves them blank
for days off. The shift codes are selected from the Shift table.
The Shift table consists of:
Table field name Example data
ShiftID D
ShiftDescription Day Shift
ShiftStartTime 07:00
ShiftEndTime 15:30
ShiftType D
ShiftPaidTime 480 (in Minutes)
I need to total the shifts and hours for each employee and am unsure
of how to
do this.
For the hours I was thinking of somehow getting the shift information
into memory without many DLookups, possibly as below. Would this place
the table information into memory where I can query it?
If so
Option Compare Database
Option Explicit
Dim db As Database
Dim RosSftrst As Recordset
Dim RosSftSQL As String 'put in form open
Dim sftID As String, sftDesc As String, sftStTime As Date, sftPdTime
As String, hrs As String
Private Sub Form_Open(Cancel As Integer)
RosSftSQL = "Select [ShiftID], [ShiftDescription], [ShiftStartTime],
[ShiftPaidTime] From RosterShifts "
Set db = CurrentDb
Set RosSftrst = db.OpenRecordset(RosSftSQL, dbOpenSnapshot)
If Not RosSftrst.EOF Then
sftID = RosSftrst![ShiftID]
sftDesc = RosSftrst![ShiftDescription]
sftStTime = RosSftrst![ShiftStartTime]
sftPdTime = RosSftrst![ShiftPaidTime]
End If
RosSftrst.Close
Set RosSftrst = Nothing
Set db = Nothing
MsgBox sftID
End Sub
Public Function HoursTot()
Dim shftHrs as string
Do until ShiftTotal
shftHrs = sftPdTime where sftID = me![Shift1] - not sure how
to do this line - IT DOESN'T WORK
hrs = hrs + shftHrs
Goto next control
Loop
Me![HoursTotal] = hrs
End Function
I would also need to convert the minutes to hours somehow. I think
I've seen this before.
Any help greatly appreciated.
Regards
Kevin

One way to keep lookup values in memory would be to use Dictionary
objects. Try this as an example:

Create a new unbound form. Add a combo box that displays the [ShiftID]
values from your [RosterShifts] table. Then create "On Load" and "On
Unload" events for the form, create an "After Update" event for the
combo box, and paste the following bits of code into each. When a new
[ShiftID] value is selected in the combo box a MsgBox will pop up
telling you what the corresponding [ShiftPaidTime] is.

'-----------------------------
Option Compare Database
Option Explicit

' this requires a reference to "Microsoft Scripting Runtime"
Dim ShiftData As Dictionary

Private Sub Combo0_AfterUpdate()
' demonstrate referencing a Dictionary of Dictionary objects
With Me.Combo0
MsgBox "ShiftPaidTime for ShiftID=""" & .Value _
& """ is " & ShiftData(.Value)("ShiftPaidTime")
End With
End Sub

Private Sub Form_Load()
Dim cdb As DAO.Database, rst As DAO.Recordset
Dim ShiftRecord As Dictionary

Set ShiftData = New Dictionary

Set cdb = CurrentDb
Set rst = cdb.OpenRecordset( _
"SELECT ShiftID, ShiftDescription, ShiftPaidTime " & _
"FROM RosterShifts", _
dbOpenSnapshot)
Do While Not rst.EOF
Set ShiftRecord = New Dictionary
With ShiftRecord
.Add "ShiftDescription", rst!ShiftDescription.Value
.Add "ShiftPaidTime", rst!ShiftPaidTime.Value
End With
ShiftData.Add rst!ShiftID.Value, ShiftRecord
Set ShiftRecord = Nothing
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
Set cdb = Nothing
End Sub

Private Sub Form_Unload(Cancel As Integer)
Set ShiftData = Nothing
End Sub


Thanks Gord,
This looks great.
I think I'm getting closer.
Another of my weak points is syntax and "s and &s.
Using your code I have the shift data in memory.

Please correct me where you see me err.

I'm thinking of making a public function to place in each shift field
- (ie Shift 1 to 28) afterUpdate event
This function will identify the value of shift1 (which will be a
shiftID.value) and then query the shift data in memory to get the
ShiftPaidTime - this is the bit that I'm having difficulty with.

Once the ShiftPaidTime is known, I will add it to a variable Totalhrs.
and repeat the process for all 28 shifts.

Do you think this is an efficient way of calculating the total hrs for
an employee?
If so, do you know the syntax I need to get the ShiftPaidTime for each
shift entered by the user.
I have tried an assortment of variations of the below code but just
can't get it right: (as you can see, I'm desperate)

Dim PTime As String, RShift As String
RShift = [Shift1].Value
'PTime = Shift1.Value ("ShiftPaidTime")
PTime = RShift ("ShiftPaidTime")

Thanks again for your help.
Regards
Kevin

Mar 22 '07 #3

P: n/a
On Mar 21, 11:22 pm, "Kev" <kevin.vaug...@nhw.hume.org.auwrote:
On Mar 21, 11:01 pm, "Gord" <g...@kingston.netwrote:
On Mar 21, 2:14 am, "Kev" <kevin.vaug...@nhw.hume.org.auwrote:
Hello,
I have an Access 2003 database running on an XP network.
I have a datasheet subform containing a 28 day roster - shift1 to
shift28.
Each record has 1 RosterEmpID, 1 EmployeeNumber, 28 shift fields, and
1 shiftTotal field and 1 HoursTotal field.
Datasheet may look like:
Employee Shift1 Shift2 Shift3 Shift4 etc...
shiftTotal HoursTotal
Emp1 D D E
E 4 32
Emp2 D
E 2 16
Emp3 N N
N 3
30 (10hr Night shift)
The user enters shift codes into each shift field or leaves them blank
for days off. The shift codes are selected from the Shift table.
The Shift table consists of:
Table field name Example data
ShiftID D
ShiftDescription Day Shift
ShiftStartTime 07:00
ShiftEndTime 15:30
ShiftType D
ShiftPaidTime 480 (in Minutes)
I need to total the shifts and hours for each employee and am unsure
of how to
do this.
For the hours I was thinking of somehow getting the shift information
into memory without many DLookups, possibly as below. Would this place
the table information into memory where I can query it?
If so
Option Compare Database
Option Explicit
Dim db As Database
Dim RosSftrst As Recordset
Dim RosSftSQL As String 'put in form open
Dim sftID As String, sftDesc As String, sftStTime As Date, sftPdTime
As String, hrs As String
Private Sub Form_Open(Cancel As Integer)
RosSftSQL = "Select [ShiftID], [ShiftDescription], [ShiftStartTime],
[ShiftPaidTime] From RosterShifts "
Set db = CurrentDb
Set RosSftrst = db.OpenRecordset(RosSftSQL, dbOpenSnapshot)
If Not RosSftrst.EOF Then
sftID = RosSftrst![ShiftID]
sftDesc = RosSftrst![ShiftDescription]
sftStTime = RosSftrst![ShiftStartTime]
sftPdTime = RosSftrst![ShiftPaidTime]
End If
RosSftrst.Close
Set RosSftrst = Nothing
Set db = Nothing
MsgBox sftID
End Sub
Public Function HoursTot()
Dim shftHrs as string
Do until ShiftTotal
shftHrs = sftPdTime where sftID = me![Shift1] - not sure how
to do this line - IT DOESN'T WORK
hrs = hrs + shftHrs
Goto next control
Loop
Me![HoursTotal] = hrs
End Function
I would also need to convert the minutes to hours somehow. I think
I've seen this before.
Any help greatly appreciated.
Regards
Kevin
One way to keep lookup values in memory would be to use Dictionary
objects. Try this as an example:
Create a new unbound form. Add a combo box that displays the [ShiftID]
values from your [RosterShifts] table. Then create "On Load" and "On
Unload" events for the form, create an "After Update" event for the
combo box, and paste the following bits of code into each. When a new
[ShiftID] value is selected in the combo box a MsgBox will pop up
telling you what the corresponding [ShiftPaidTime] is.
'-----------------------------
Option Compare Database
Option Explicit
' this requires a reference to "Microsoft Scripting Runtime"
Dim ShiftData As Dictionary
Private Sub Combo0_AfterUpdate()
' demonstrate referencing a Dictionary of Dictionary objects
With Me.Combo0
MsgBox "ShiftPaidTime for ShiftID=""" & .Value _
& """ is " & ShiftData(.Value)("ShiftPaidTime")
End With
End Sub
Private Sub Form_Load()
Dim cdb As DAO.Database, rst As DAO.Recordset
Dim ShiftRecord As Dictionary
Set ShiftData = New Dictionary
Set cdb = CurrentDb
Set rst = cdb.OpenRecordset( _
"SELECT ShiftID, ShiftDescription, ShiftPaidTime " & _
"FROM RosterShifts", _
dbOpenSnapshot)
Do While Not rst.EOF
Set ShiftRecord = New Dictionary
With ShiftRecord
.Add "ShiftDescription", rst!ShiftDescription.Value
.Add "ShiftPaidTime", rst!ShiftPaidTime.Value
End With
ShiftData.Add rst!ShiftID.Value, ShiftRecord
Set ShiftRecord = Nothing
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
Set cdb = Nothing
End Sub
Private Sub Form_Unload(Cancel As Integer)
Set ShiftData = Nothing
End Sub

Thanks Gord,
This looks great.
I think I'm getting closer.
Another of my weak points is syntax and "s and &s.
Using your code I have the shift data in memory.

Please correct me where you see me err.

I'm thinking of making a public function to place in each shift field
- (ie Shift 1 to 28) afterUpdate event
This function will identify the value of shift1 (which will be a
shiftID.value) and then query the shift data in memory to get the
ShiftPaidTime - this is the bit that I'm having difficulty with.

Once the ShiftPaidTime is known, I will add it to a variable Totalhrs.
and repeat the process for all 28 shifts.

Do you think this is an efficient way of calculating the total hrs for
an employee?
If so, do you know the syntax I need to get the ShiftPaidTime for each
shift entered by the user.
I have tried an assortment of variations of the below code but just
can't get it right: (as you can see, I'm desperate)

Dim PTime As String, RShift As String
RShift = [Shift1].Value
'PTime = Shift1.Value ("ShiftPaidTime")
PTime = RShift ("ShiftPaidTime")

Thanks again for your help.
Regards
Kevin
Hi, Kevin.

A dictionary object is a collection of "things" that can be accessed
using a key value. (In other languages it is called an "associative
array".) In this case the "things" in our ShiftData dictionary object
are dictionary objects themselves. Each "thing" in ShiftData
represents a row in our lookup table. We can access a particular row
via its ShiftID and then retrieve the values in that row by their
column names.

For example, the long way to get the ShiftPaidTime for the day shift
would be...

Dim dictRow As Dictionary, lngTime As Long
Set dictRow = ShiftData("D")
lngTime = dictRow("ShiftPaidTime")
Set dictRow = Nothing

....but a shortcut is to use...

Dim lngTime As Long
lngTime = ShiftData("D")("ShiftPaidTime")

So, building on my previous sample form, try this:

- rename the combo box to "Shift01"

- copy it and paste it into the same form, then rename the new one to
"Shift02"

- add a text box named "TotalTime"

- create "After Update" event procedures for both combo boxes and
paste the following code in the appropriate places. (The
"CalcTotalTime" procedure is new. The "Form_Load" and "Form_Unload"
procedures are the same as before.)

Private Sub Shift01_AfterUpdate()
CalcTotalTime
End Sub

Private Sub Shift02_AfterUpdate()
CalcTotalTime
End Sub

Private Sub CalcTotalTime()
Dim lngTotal As Long, i As Integer, _
strCtlName As String, strShiftID As String

lngTotal = 0
For i = 1 To 2 ' would be 28 for you
strCtlName = "Shift" & Format(i, "00")
If Not IsNull(Me.Controls(strCtlName).Value) Then
strShiftID = Me.Controls(strCtlName).Value
lngTotal = lngTotal _
+ ShiftData(strShiftID)("ShiftPaidTime")
End If
Next

Me.TotalTime.Value = lngTotal
End Sub

Mar 22 '07 #4

P: n/a
Kev
On Mar 22, 10:36 pm, "Gord" <g...@kingston.netwrote:
On Mar 21, 11:22 pm, "Kev" <kevin.vaug...@nhw.hume.org.auwrote:
On Mar 21, 11:01 pm, "Gord" <g...@kingston.netwrote:
On Mar 21, 2:14 am, "Kev" <kevin.vaug...@nhw.hume.org.auwrote:
Hello,
I have an Access 2003 database running on an XP network.
I have a datasheet subform containing a 28 day roster - shift1 to
shift28.
Each record has 1 RosterEmpID, 1 EmployeeNumber, 28 shift fields, and
1 shiftTotal field and 1 HoursTotal field.
Datasheet may look like:
Employee Shift1 Shift2 Shift3 Shift4 etc...
shiftTotal HoursTotal
Emp1 D D E
E 4 32
Emp2 D
E 2 16
Emp3 N N
N 3
30 (10hr Night shift)
The user enters shift codes into each shift field or leaves them blank
for days off. The shift codes are selected from the Shift table.
The Shift table consists of:
Table field name Example data
ShiftID D
ShiftDescription Day Shift
ShiftStartTime 07:00
ShiftEndTime 15:30
ShiftType D
ShiftPaidTime 480 (in Minutes)
I need to total the shifts and hours for each employee and am unsure
of how to
do this.
For the hours I was thinking of somehow getting the shift information
into memory without many DLookups, possibly as below. Would this place
the table information into memory where I can query it?
If so
Option Compare Database
Option Explicit
Dim db As Database
Dim RosSftrst As Recordset
Dim RosSftSQL As String 'put in form open
Dim sftID As String, sftDesc As String, sftStTime As Date, sftPdTime
As String, hrs As String
Private Sub Form_Open(Cancel As Integer)
RosSftSQL = "Select [ShiftID], [ShiftDescription], [ShiftStartTime],
[ShiftPaidTime] From RosterShifts "
Set db = CurrentDb
Set RosSftrst = db.OpenRecordset(RosSftSQL, dbOpenSnapshot)
If Not RosSftrst.EOF Then
sftID = RosSftrst![ShiftID]
sftDesc = RosSftrst![ShiftDescription]
sftStTime = RosSftrst![ShiftStartTime]
sftPdTime = RosSftrst![ShiftPaidTime]
End If
RosSftrst.Close
Set RosSftrst = Nothing
Set db = Nothing
MsgBox sftID
End Sub
Public Function HoursTot()
Dim shftHrs as string
Do until ShiftTotal
shftHrs = sftPdTime where sftID = me![Shift1] - not sure how
to do this line - IT DOESN'T WORK
hrs = hrs + shftHrs
Goto next control
Loop
Me![HoursTotal] = hrs
End Function
I would also need to convert the minutes to hours somehow. I think
I've seen this before.
Any help greatly appreciated.
Regards
Kevin
One way to keep lookup values in memory would be to use Dictionary
objects. Try this as an example:
Create a new unbound form. Add a combo box that displays the [ShiftID]
values from your [RosterShifts] table. Then create "On Load" and "On
Unload" events for the form, create an "After Update" event for the
combo box, and paste the following bits of code into each. When a new
[ShiftID] value is selected in the combo box a MsgBox will pop up
telling you what the corresponding [ShiftPaidTime] is.
'-----------------------------
Option Compare Database
Option Explicit
' this requires a reference to "Microsoft Scripting Runtime"
Dim ShiftData As Dictionary
Private Sub Combo0_AfterUpdate()
' demonstrate referencing a Dictionary of Dictionary objects
With Me.Combo0
MsgBox "ShiftPaidTime for ShiftID=""" & .Value _
& """ is " & ShiftData(.Value)("ShiftPaidTime")
End With
End Sub
Private Sub Form_Load()
Dim cdb As DAO.Database, rst As DAO.Recordset
Dim ShiftRecord As Dictionary
Set ShiftData = New Dictionary
Set cdb = CurrentDb
Set rst = cdb.OpenRecordset( _
"SELECT ShiftID, ShiftDescription, ShiftPaidTime " & _
"FROM RosterShifts", _
dbOpenSnapshot)
Do While Not rst.EOF
Set ShiftRecord = New Dictionary
With ShiftRecord
.Add "ShiftDescription", rst!ShiftDescription.Value
.Add "ShiftPaidTime", rst!ShiftPaidTime.Value
End With
ShiftData.Add rst!ShiftID.Value, ShiftRecord
Set ShiftRecord = Nothing
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
Set cdb = Nothing
End Sub
Private Sub Form_Unload(Cancel As Integer)
Set ShiftData = Nothing
End Sub
Thanks Gord,
This looks great.
I think I'm getting closer.
Another of my weak points is syntax and "s and &s.
Using your code I have the shift data in memory.
Please correct me where you see me err.
I'm thinking of making a public function to place in each shift field
- (ie Shift 1 to 28) afterUpdate event
This function will identify the value of shift1 (which will be a
shiftID.value) and then query the shift data in memory to get the
ShiftPaidTime - this is the bit that I'm having difficulty with.
Once the ShiftPaidTime is known, I will add it to a variable Totalhrs.
and repeat the process for all 28 shifts.
Do you think this is an efficient way of calculating the total hrs for
an employee?
If so, do you know the syntax I need to get the ShiftPaidTime for each
shift entered by the user.
I have tried an assortment of variations of the below code but just
can't get it right: (as you can see, I'm desperate)
Dim PTime As String, RShift As String
RShift = [Shift1].Value
'PTime = Shift1.Value ("ShiftPaidTime")
PTime = RShift ("ShiftPaidTime")
Thanks again for your help.
Regards
Kevin

Hi, Kevin.

A dictionary object is a collection of "things" that can be accessed
using a key value. (In other languages it is called an "associative
array".) In this case the "things" in our ShiftData dictionary object
are dictionary objects themselves. Each "thing" in ShiftData
represents a row in our lookup table. We can access a particular row
via its ShiftID and then retrieve the values in that row by their
column names.

For example, the long way to get the ShiftPaidTime for the day shift
would be...

Dim dictRow As Dictionary, lngTime As Long
Set dictRow = ShiftData("D")
lngTime = dictRow("ShiftPaidTime")
Set dictRow = Nothing

...but a shortcut is to use...

Dim lngTime As Long
lngTime = ShiftData("D")("ShiftPaidTime")

So, building on my previous sample form, try this:

- rename the combo box to "Shift01"

- copy it and paste it into the same form, then rename the new one to
"Shift02"

- add a text box named "TotalTime"

- create "After Update" event procedures for both combo boxes and
paste the following code in the appropriate places. (The
"CalcTotalTime" procedure is new. The "Form_Load" and "Form_Unload"
procedures are the same as before.)

Private Sub Shift01_AfterUpdate()
CalcTotalTime
End Sub

Private Sub Shift02_AfterUpdate()
CalcTotalTime
End Sub

Private Sub CalcTotalTime()
Dim lngTotal As Long, i As Integer, _
strCtlName As String, strShiftID As String

lngTotal = 0
For i = 1 To 2 ' would be 28 for you
strCtlName = "Shift" & Format(i, "00")
If Not IsNull(Me.Controls(strCtlName).Value) Then
strShiftID = Me.Controls(strCtlName).Value
lngTotal = lngTotal _
+ ShiftData(strShiftID)("ShiftPaidTime")
End If
Next

Me.TotalTime.Value = lngTotal
End Sub


Thank you Gord
This works perfectly and it's something I can apply elsewhere.
Cheers
Kevin

Mar 23 '07 #5

This discussion thread is closed

Replies have been disabled for this discussion.