Re: Recordset or Array to memory problem

On Mar 21, 2:14 am, "Kev" <kevin.vaug...@xxxxxxxxxxxxxxx> wrote:
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

.

Relevant Pages

  • Re: Recordset or Array to memory problem
    ... The shift codes are selected from the Shift table. ... Dim RosSftrst As Recordset ... Dim sftID As String, sftDesc As String, sftStTime As Date, sftPdTime ... "FROM RosterShifts", _ ...
    (comp.databases.ms-access)
  • RE: Error 3065- cannot execute select query
    ... in the line Dim db as Database, Database doesn't appear in the list of ... > Dim dbf as Database ... >> is a results table, whose fields include resultID, OpID, OpNum, date, day, ... >> Shift, and panels. ...
    (microsoft.public.access.formscoding)
  • Re: Is This Possible ... ? Yes - Upload images to an Access database
    ... It looks like your databases folder is outside the root of your web ... I created a database exactly as you said, the only change I made was to ... and underneath an 'upload' button and a 'view images' button. ... Dim con As New Data.OleDb.OleDbConnection ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Is This Possible ... ? Yes - Upload images to an Access database
    ... It looks like your databases folder is outside the root of your web ... I created a database exactly as you said, the only change I made was to ... and underneath an 'upload' button and a 'view images' button. ... Dim con As New Data.OleDb.OleDbConnection ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Is This Possible ... ? Yes - Upload images to an Access database
    ... I created a database exactly as you said, the only change I made was to ... upload it into the 'databases' folder, and not a folder called 'App_Data', ... and underneath an 'upload' button and a 'view images' button. ... Dim con As New Data.OleDb.OleDbConnection ...
    (microsoft.public.dotnet.framework.aspnet)