How to insert records to a table that has a one to many realtionship

[Jerry Maiapu]

I have just completed a timesheet (using access 2003) and we are using it. However recently, I found out that for public holidays or power disruption the person with administrative privilege can clock in or clock out for all of the employees at a particular day once.
How can I make it possible for him to just enter
1. Clockin time
2. Clockout time and
3. Date

..on a form so that when he clicks on a cmdupdate button, it will put the dates and times into its respective fields in a table called tbltimesheet for all the employess.

I have two tables tblemplyees and tbltimesheet. They have a one to many relationship:

I can use insert into statement but the problem is:
First I have to check the correct names in tblemplyees table and insert into the tbltimesheet the dates and the clockin and clock out times for each of the employees respectively at once by clicking the cmdupdate button.

How can I achieve this?

A short table attributes

Table1

tblemplyees

eid autonumber (primary key)
efname text (first name)
esname text (second name)

……………………………………… …..
Table 2
tbltimesheet
tid autonumber (primary key)
eid number (foreign key)
date date/time (date clocked in)
clockin date/time (time clocked in)
clockout date/time (time clocked out)

[patjones]

Hi Jerry,

I'm glad you got this working.

For the method of data entry, you could have clock in/out and corresponding time in/out text boxes that become visible only when the administrator logs in. Text boxes have a Visible property that you can set as Me.[Text Box Name].Visible = False (or True) depending on whether the administrator is logged in or not.

So let's take a specific situation. Suppose the administrator comes in this morning, and there was a power outage yesterday afternoon that prevented people from clocking out. So now you have a bunch of records from yesterday with no clock out time. You would want to have a SQL query (UPDATE) that runs in order to fill in the clock out time for those people who have a null clock out time for yesterday's date.

Similarly, maybe the power outage lasts into today and people can't clock in. You'll need to have a SQL query (INSERT) that establishes new records for today. My only question to you is whether or not you need control over who gets clocked in. Because what if I don't come in today? When the administrator goes to do this mass INSERT for today's clock in, I should not be clocked in.

Let me know what you think.

Pat

[Jerry Maiapu]

Pat Here is what I propose

Hi Pat,
You are inventive and brilliant so could think ahead. Thankyou so much because I missed out some of the important aspects that you seem to ponit out. My apology for that.

Okay First of all please see the 2 attached snap shots before I propose a few things.

1. One with attribute names and
2. The sample form.

Here is my proposal on how it would work……..

Behaviour 1.
From the ‘Staff Present List’ list box the officers who are absent can be selected. Once double clicked the selected name appears on the Staff Absent List box. (See attached snap shot). If a name is accidentally selected then that name can be double click on the “Staff Absent” List box so that it appears on the ‘Staff Present List”

Behaviour 2.

On the Onclick_event of the cmdupdate
The following should be checked:
- If ‘Clockin only’ check box is selected then all staff list in the ‘Staff Present” list box should be updated with that time and date
- Else if ‘clock out only’ check box is selected then all staff list in the ‘Staff Present” list box should be updated with that time and date
- Else if “Both” option button is selected then all staff list in the ‘Staff Present” list box should be updated with those times and date

That is all what I need.

And Please Pat, I don’t’ even know how to do what I mention in Behaviour 1.
Please do help me achieve Behaviour 1 and Behaviour 2.

I would very much appreciate your time and effort..

With many Thanks

Jerry

[Jerry Maiapu]

Zip

Pat,
My fault, please find below are the 2 zipped snap shots..

Regards

[patjones]

Jerry -

For the first issue of moving items between two list boxes by double-clicking, see a short tip that I wrote about a year ago: Double-clicking to move an item between list boxes. You will simply replace 'lstA' and 'lstB' with whatever the names of your list boxes are.

For the second issue...that is interesting. I would set up an option group with three options (i.e. three check boxes numbered 1, 2, ...). Then use a Case structure to pick out the proper SQL action:

Dim j As Integer
Dim strSQL As String

Select Case opgClockInOut

     Case 1
     'Clock everyone in
   
     For j = 0 to Me.lst.ListCount-1
     
          strSQL = "INSERT INTO  tbltimesheet " & _
                        "VALUES (eid =", & Me.lst.Column(0, j) & _
                             ", date = " & Format(Now, "mm/dd/yy") & _ 
                          ", clockin = " & Format(Now, "hh:mm:ss") & _
                         ", clockout = Null)"
     
          DoCmd.RunSQL strSQL 

     Next j

     Case 2
     'Clock everyone out...

     Case Else     
     'Other options...

End Select

What this does is loops through each person in the list box (which I'm calling "lst") and then uses an INSERT to put that person in the table. Note that in your list box, you'll probably be displaying the name in column 1, but you'll have their "eid" as being column 0. The entries for date and clockin are self-explanatory. clockout you're setting to Null because this is the clockin transaction...a nd they won't clock out until the end of the day.

You can handle other possibilities in a similar manner in subsequent branches of the Case structure (for instance, using an UPDATE in Case 2 to fill in the clock out time for certain people from yesterday, and so forth).

As a side note, I'm sort of questioning your use of an auto-number primary key in your tables. Especially something like employee ID. If you have employer-assigned employee ID's, use that instead. Generally, I try to avoid auto-numbering, and rather aim to tie the field to something real world and verifiable.

Pat

[Jerry Maiapu]

My suggestion

Hi Pat,

Thanks a lot for all the info and hints..
Regarding the employee’s ID .. auto number primary key.. my fault.. Ok next time I’ll use some realistic ID numbers.. Thanks for that correction..

Pat, I have never used SQL via VBA and I am a bit confused here.
Ok, before I point out anything further I’d like to stress the following things:



1. Because the employee names in one of the list boxes (lststafflist) will come from the table tblemplyees (ie its row source type is : table/query and its row source is set to: SELECT [Employees].[EmployeeID], [Employees].[FirstName], [Employees].[LastName] FROM [Employees] ORDER BY [FirstName]; so therefore the names cannot moved from list box but can be selected (double clicked) to appear on the second list box (lststaffupdate ). Whatever name selected will appear on the second unbound list box (lststaffupdate ). These names will be updated with their respective dates and times when clicked on the “cmdupdate” button.

(Hint: Advantage.. If a new employee is added then his name will automatically appear on this list)

Because of this, I changed the form a bit please see the attached zipped snap shot.

A. When clicked on “Select All>>” cmd button , All employee names should appear on the second list box or/ and when double clicked on a single name the selected name should appear on the 2nd list box., with first names in column[1] and second name in column [2] just like in 1st /parent list box (lststafflist)

B. When double clicked on an employee name in the 2nd list box (lststaffupdate ), the selected name should be removed from the list

**** * Please help me achieve A and B*******

2. The date plus the clock in and clock out times will be entered/typed in the text boxes on the form (today’s Date and times…Now ().. must not be automatically entered because if there was a power disruption yesterday then yesterday’s date, clock in and clock out times should be entered not today’s. Thefore the sql must get its values( date,timein,tim eout) from the text boxes on form itself)

............... ....

Some conditions to consider:

(I) Clocking Out:
If we want to clock out only for some employees then it must check if the date on the form and the date in the table re equal. If equal, then go ahead and insert the clock out times for that date else give a msgbox “Cannot clock out. Date not found”

(II) If we want to clock in only then it should insert into a new record..
If we want to clock in and clock out for employees then this should also be inserted into a new record in the tbltimesheet table

The above is what I just propose.
Please do comment if I am wrong somewhere..

But please I respectfully ask you to help me out to achieve (1) .A .and B & 2 based on my conditions (i and ii if they are okay with. you.

I have just attached my sample attendance db (2 tables and one form).
Do help me put in the necessary codes ift you can help and zip it back

Thanks and Thanks a lot , Pat, for you tremendous assisted.. You help will be treasured.
Nobody could really help me. I am quite relying on you so once again thanks in advance.


Jerry

[Jerry Maiapu]

Can someone help me on this?
Its now almost a month and no response..

Thankyou

[patjones]

Hi,

I appreciate that you are dealing with a non-trivial problem; however, in my opinion, between my responses to this thread and to the previous thread about this project (Using sql insert into statement with access VB in a timesheet db) - I think you have the tools necessary to reach your goal. It would be hard for me to help you further without just writing all the code you need, which is something I'm not inclined to do.

Pat

[missinglinq]

Can someone help me on this?
Its now almost a month and no response

.

Your statement is absurd! zepphead80 has spent a good deal of time responding to your post! But as he also suggested, we are here to assist people in overcoming problems they are having in developing their databases,. We are not here to do the development for them! Your threads invariably present a task you want to accomplish and ask for somebody to tell you how to do it, without showing any evidence whatsoever that you've attempted to do it yourself!

I would suggest that perhaps you need to take a class or buy a book and make some attempt to at least learn the basics of Access development before attempting to create a database. IF you run into specific technical problems/questions we'll be happy to help you with that, but we will not do the work for you.

Linq ;0)>

Moderator

[Jerry Maiapu]

Hi there..
I found the soulution my self and i would like to thank those who help me all through,esp zepphead80..
For those who have followed us through, I am putting the solution code below..
I know alot have questions similar to mine..

Note: I have 3 check boxes (chkinonly, chkoutonly,optb oth) and 3 command buttons. 1 to clockin only,1 for clock out only and 1 for clockin/out..

I have also 3 text boxes. 1 for date, 1 for clock in time, 1 for clock out time


For the clockout button: (will clockout selected names in list)

Private Sub cmdclockout_Click()
  Dim strSQL        As String
  Dim db            As DAO.Database
  Dim rs            As DAO.Recordset
  Dim ctl           As Control
  Dim varItem, previous      As Variant
  Dim intI          As Integer
  Dim s, k


  On Error GoTo ErrorHandler

  Set db = CurrentDb()
  Set rs = db.OpenRecordset("TimeSheet")
 
  If IsNull(Me!txtdate) = True Then
    MsgBox "Please enter date to update", vbOKOnly + vbExclamation, "No Date"
    Exit Sub
   
    ElseIf IsNull(Me!txtclockout) = True Then
    MsgBox "Please enter clock out time to update", vbOKOnly + vbExclamation, "No Clock out time"
    Exit Sub
    Else
    'do nothing
  End If
  k = MsgBox("You are about to clockout every employee who" _
  & "clocked in on " & s & ". Are you sure?", vbExclamation + vbYesNo, "Confirm")
  If k = vbNo Then
  Exit Sub
  Else
   rs.MoveFirst
   Do Until rs.EOF
      If rs!DateWorked = Me!txtdate Then
         rs.Edit
         rs!TimeOut = Me!txtclockout
         rs.Update
      End If
      rs.MoveNext
   Loop
 MsgBox "Updated Successfully", vbOKOnly + vbQuestion
 End If
ExitHandler:
  Set rs = Nothing
  Set db = Nothing
  Exit Sub

ErrorHandler:
  Select Case Err
    Case Else
      MsgBox Err.Description
      DoCmd.Hourglass False
      Resume ExitHandler
  End Select
End Sub

For the clockin button: (will clock in names)

Private Sub cmdclockin_Click()
Dim strSQL          As String
  Dim db            As DAO.Database
  Dim rs            As DAO.Recordset
  Dim ctl           As Control
  Dim varItem       As Variant
  
  Dim s, r


s = Format(Me!txtdate, "dddd, mmmm d yyyy")

'g = Format(f, "dddd, mmmm d yyyy")

  On Error GoTo ErrorHandler

  Set db = CurrentDb()
  Set rs = db.OpenRecordset("TimeSheet", dbOpenDynaset, dbAppendOnly) 'store timsheet in temp table rs

  'make sure a selection has been made
  If Me.lstEmployees.ItemsSelected.Count = 0 Then 'itemsSelected is a inbuld property of a list box
    MsgBox "Please select at least one employee to Clock In", vbOKOnly + vbExclamation, "Select an Officer"
    Exit Sub
 
  ElseIf IsNull(Me!txtdate) = True Then
    MsgBox "Please enter date to clock In", vbOKOnly + vbExclamation, "No Date"
    Exit Sub
   ElseIf IsNull(Me!txtclockin) = True Then
    MsgBox "Please enter clock time to update", vbOKOnly + vbExclamation, "No Clock in time"
    Exit Sub
    Else
    'do nothing
  End If
 r = MsgBox("You are about to clock in the selected employees " _
  & "for " & s & ". Are you sure?", vbExclamation + vbYesNo, "Confirm")
  If r = vbNo Then
  Exit Sub
  Else
  'add selected value(s) to table
  Set ctl = Me.lstEmployees 'store list box lstEmployees in temp ctl
  For Each varItem In ctl.ItemsSelected
    rs.AddNew ' go to a new record in our temp table rs
    rs!EmployeeId = ctl.ItemData(varItem) 'store the first column value in EmpID
    rs!DateWorked = Me.txtdate 'Store the text box value in Other Value field in the rs temp table
    rs!TimeIn = Me!txtclockin
    rs.Update
  Next varItem
 MsgBox "Updated Successfully", vbOKOnly + vbQuestion
 End If
ExitHandler:
  Set rs = Nothing
  Set db = Nothing
  Exit Sub

ErrorHandler:
  Select Case Err
    Case Else
      MsgBox Err.Description
      DoCmd.Hourglass False
      Resume ExitHandler
  End Select
End Sub

For the update button....(cloc ks in and clock out selected officers in the list"

Private Sub cmdupdate_Click()
Dim strSQL          As String
  Dim db            As DAO.Database
  Dim rs            As DAO.Recordset
  Dim ctl           As Control
  Dim varItem       As Variant
  Dim p
  On Error GoTo ErrorHandler

  Set db = CurrentDb()
  Set rs = db.OpenRecordset("TimeSheet", dbOpenDynaset, dbAppendOnly) 'store timsheet in temp table rs

  'make sure a selection has been made
  If Me.lstEmployees.ItemsSelected.Count = 0 Then 'itemsSelected is a inbuld property of a list box
    MsgBox "Please select at least one employee to update", vbOKOnly + vbExclamation, "Select an Officer"
    Exit Sub
 
  ElseIf IsNull(Me!txtdate) = True Then
    MsgBox "Please enter date to update", vbOKOnly + vbExclamation, "No Date"
    Exit Sub
   ElseIf IsNull(Me!txtclockin) = True Then
    MsgBox "Please enter clock time to update", vbOKOnly + vbExclamation, "No Clock in time"
    Exit Sub
    ElseIf IsNull(Me!txtclockout) = True Then
    MsgBox "Please enter clock out time to update", vbOKOnly + vbExclamation, "No Clock out time"
    Exit Sub
    Else
    'do nothing
  End If
p = MsgBox("The selected employees will be updated with the " _
& "date and the times you specified  .Are you sure?", vbExclamation + vbYesNo, "Confirm")
  If p = vbNo Then
  Exit Sub
  Else
  'add selected value(s) to table
  Set ctl = Me.lstEmployees 'store list box lstEmployees in temp ctl
  If Me.txtovertime = "No" Then
 
  For Each varItem In ctl.ItemsSelected
    rs.AddNew ' go to a new record in our temp table rs
    rs!EmployeeId = ctl.ItemData(varItem) 'store the first column value in EmpID
    rs!DateWorked = Me.txtdate 'Store the text box value in Other Value field in the rs temp table
    rs!TimeIn = Me!txtclockin
    rs!TimeOut = Me!txtclockout
    rs.Update
  Next varItem
 MsgBox "Updated Successfully", vbOKOnly + vbQuestion, "Done"
 Else
 For Each varItem In ctl.ItemsSelected
    rs.AddNew ' go to a new record in our temp table rs
    rs!EmployeeId = ctl.ItemData(varItem) 'store the first column value in EmpID
    rs!DateWorked = Me.txtdate 'Store the text box value in Other Value field in the rs temp table
    rs!TimeIn = Me!txtclockin
    rs!TimeOut = Me!txtclockout
    rs!Overtime = "AOT"
    rs.Update
  Next varItem
 MsgBox "Updated Successfully", vbOKOnly + vbQuestion, "Done"
 End If
 
 End If
ExitHandler:
  Set rs = Nothing
  Set db = Nothing
  Exit Sub

ErrorHandler:
  Select Case Err
    Case Else
      MsgBox Err.Description
      DoCmd.Hourglass False
      Resume ExitHandler
  End Select
End Sub

That is all and hope this hels someone out there..

Do ask if any questions..

Regards

Jerry