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

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

Jerry Maiapu
P: 259
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



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

Table 2
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)
Apr 12 '10 #1
Share this Question
Share on Google+
9 Replies

Expert 100+
P: 931
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.

Apr 13 '10 #2

Jerry Maiapu
P: 259
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

Attached Images
File Type: jpg desctriptive forms.jpg (10.2 KB, 153 views)
File Type: jpg Original Form.jpg (7.6 KB, 141 views)
Apr 14 '10 #3

Jerry Maiapu
P: 259
My fault, please find below are the 2 zipped snap shots..

Attached Files
File Type: zip desctriptive (25.5 KB, 58 views)
File Type: zip Original (19.3 KB, 56 views)
Apr 14 '10 #4

Expert 100+
P: 931
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:

Expand|Select|Wrap|Line Numbers
  1. Dim j As Integer
  2. Dim strSQL As String
  4. Select Case opgClockInOut
  6.      Case 1
  7.      'Clock everyone in
  9.      For j = 0 to Me.lst.ListCount-1
  11.           strSQL = "INSERT INTO  tbltimesheet " & _
  12.                         "VALUES (eid =", & Me.lst.Column(0, j) & _
  13.                              ", date = " & Format(Now, "mm/dd/yy") & _ 
  14.                           ", clockin = " & Format(Now, "hh:mm:ss") & _
  15.                          ", clockout = Null)"
  17.           DoCmd.RunSQL strSQL 
  19.      Next j
  21.      Case 2
  22.      'Clock everyone out...
  24.      Case Else     
  25.      'Other options...
  27. 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...and 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.

Apr 14 '10 #5

Jerry Maiapu
P: 259
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,timeout) 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.

Attached Files
File Type: zip New (38.6 KB, 70 views)
File Type: zip Attendance (30.0 KB, 41 views)
Apr 15 '10 #6

Jerry Maiapu
P: 259
Can someone help me on this?
Its now almost a month and no response..

May 5 '10 #7

Expert 100+
P: 931

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.

May 5 '10 #8

Expert 2.5K+
P: 3,532
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)>

May 5 '10 #9

Jerry Maiapu
P: 259
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,optboth) 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)
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdclockout_Click()
  2.   Dim strSQL        As String
  3.   Dim db            As DAO.Database
  4.   Dim rs            As DAO.Recordset
  5.   Dim ctl           As Control
  6.   Dim varItem, previous      As Variant
  7.   Dim intI          As Integer
  8.   Dim s, k
  11.   On Error GoTo ErrorHandler
  13.   Set db = CurrentDb()
  14.   Set rs = db.OpenRecordset("TimeSheet")
  16.   If IsNull(Me!txtdate) = True Then
  17.     MsgBox "Please enter date to update", vbOKOnly + vbExclamation, "No Date"
  18.     Exit Sub
  20.     ElseIf IsNull(Me!txtclockout) = True Then
  21.     MsgBox "Please enter clock out time to update", vbOKOnly + vbExclamation, "No Clock out time"
  22.     Exit Sub
  23.     Else
  24.     'do nothing
  25.   End If
  26.   k = MsgBox("You are about to clockout every employee who" _
  27.   & "clocked in on " & s & ". Are you sure?", vbExclamation + vbYesNo, "Confirm")
  28.   If k = vbNo Then
  29.   Exit Sub
  30.   Else
  31.    rs.MoveFirst
  32.    Do Until rs.EOF
  33.       If rs!DateWorked = Me!txtdate Then
  34.          rs.Edit
  35.          rs!TimeOut = Me!txtclockout
  36.          rs.Update
  37.       End If
  38.       rs.MoveNext
  39.    Loop
  40.  MsgBox "Updated Successfully", vbOKOnly + vbQuestion
  41.  End If
  42. ExitHandler:
  43.   Set rs = Nothing
  44.   Set db = Nothing
  45.   Exit Sub
  47. ErrorHandler:
  48.   Select Case Err
  49.     Case Else
  50.       MsgBox Err.Description
  51.       DoCmd.Hourglass False
  52.       Resume ExitHandler
  53.   End Select
  54. End Sub
For the clockin button: (will clock in names)

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdclockin_Click()
  2. Dim strSQL          As String
  3.   Dim db            As DAO.Database
  4.   Dim rs            As DAO.Recordset
  5.   Dim ctl           As Control
  6.   Dim varItem       As Variant
  8.   Dim s, r
  11. s = Format(Me!txtdate, "dddd, mmmm d yyyy")
  13. 'g = Format(f, "dddd, mmmm d yyyy")
  15.   On Error GoTo ErrorHandler
  17.   Set db = CurrentDb()
  18.   Set rs = db.OpenRecordset("TimeSheet", dbOpenDynaset, dbAppendOnly) 'store timsheet in temp table rs
  20.   'make sure a selection has been made
  21.   If Me.lstEmployees.ItemsSelected.Count = 0 Then 'itemsSelected is a inbuld property of a list box
  22.     MsgBox "Please select at least one employee to Clock In", vbOKOnly + vbExclamation, "Select an Officer"
  23.     Exit Sub
  25.   ElseIf IsNull(Me!txtdate) = True Then
  26.     MsgBox "Please enter date to clock In", vbOKOnly + vbExclamation, "No Date"
  27.     Exit Sub
  28.    ElseIf IsNull(Me!txtclockin) = True Then
  29.     MsgBox "Please enter clock time to update", vbOKOnly + vbExclamation, "No Clock in time"
  30.     Exit Sub
  31.     Else
  32.     'do nothing
  33.   End If
  34.  r = MsgBox("You are about to clock in the selected employees " _
  35.   & "for " & s & ". Are you sure?", vbExclamation + vbYesNo, "Confirm")
  36.   If r = vbNo Then
  37.   Exit Sub
  38.   Else
  39.   'add selected value(s) to table
  40.   Set ctl = Me.lstEmployees 'store list box lstEmployees in temp ctl
  41.   For Each varItem In ctl.ItemsSelected
  42.     rs.AddNew ' go to a new record in our temp table rs
  43.     rs!EmployeeId = ctl.ItemData(varItem) 'store the first column value in EmpID
  44.     rs!DateWorked = Me.txtdate 'Store the text box value in Other Value field in the rs temp table
  45.     rs!TimeIn = Me!txtclockin
  46.     rs.Update
  47.   Next varItem
  48.  MsgBox "Updated Successfully", vbOKOnly + vbQuestion
  49.  End If
  50. ExitHandler:
  51.   Set rs = Nothing
  52.   Set db = Nothing
  53.   Exit Sub
  55. ErrorHandler:
  56.   Select Case Err
  57.     Case Else
  58.       MsgBox Err.Description
  59.       DoCmd.Hourglass False
  60.       Resume ExitHandler
  61.   End Select
  62. End Sub

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

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdupdate_Click()
  2. Dim strSQL          As String
  3.   Dim db            As DAO.Database
  4.   Dim rs            As DAO.Recordset
  5.   Dim ctl           As Control
  6.   Dim varItem       As Variant
  7.   Dim p
  8.   On Error GoTo ErrorHandler
  10.   Set db = CurrentDb()
  11.   Set rs = db.OpenRecordset("TimeSheet", dbOpenDynaset, dbAppendOnly) 'store timsheet in temp table rs
  13.   'make sure a selection has been made
  14.   If Me.lstEmployees.ItemsSelected.Count = 0 Then 'itemsSelected is a inbuld property of a list box
  15.     MsgBox "Please select at least one employee to update", vbOKOnly + vbExclamation, "Select an Officer"
  16.     Exit Sub
  18.   ElseIf IsNull(Me!txtdate) = True Then
  19.     MsgBox "Please enter date to update", vbOKOnly + vbExclamation, "No Date"
  20.     Exit Sub
  21.    ElseIf IsNull(Me!txtclockin) = True Then
  22.     MsgBox "Please enter clock time to update", vbOKOnly + vbExclamation, "No Clock in time"
  23.     Exit Sub
  24.     ElseIf IsNull(Me!txtclockout) = True Then
  25.     MsgBox "Please enter clock out time to update", vbOKOnly + vbExclamation, "No Clock out time"
  26.     Exit Sub
  27.     Else
  28.     'do nothing
  29.   End If
  30. p = MsgBox("The selected employees will be updated with the " _
  31. & "date and the times you specified  .Are you sure?", vbExclamation + vbYesNo, "Confirm")
  32.   If p = vbNo Then
  33.   Exit Sub
  34.   Else
  35.   'add selected value(s) to table
  36.   Set ctl = Me.lstEmployees 'store list box lstEmployees in temp ctl
  37.   If Me.txtovertime = "No" Then
  39.   For Each varItem In ctl.ItemsSelected
  40.     rs.AddNew ' go to a new record in our temp table rs
  41.     rs!EmployeeId = ctl.ItemData(varItem) 'store the first column value in EmpID
  42.     rs!DateWorked = Me.txtdate 'Store the text box value in Other Value field in the rs temp table
  43.     rs!TimeIn = Me!txtclockin
  44.     rs!TimeOut = Me!txtclockout
  45.     rs.Update
  46.   Next varItem
  47.  MsgBox "Updated Successfully", vbOKOnly + vbQuestion, "Done"
  48.  Else
  49.  For Each varItem In ctl.ItemsSelected
  50.     rs.AddNew ' go to a new record in our temp table rs
  51.     rs!EmployeeId = ctl.ItemData(varItem) 'store the first column value in EmpID
  52.     rs!DateWorked = Me.txtdate 'Store the text box value in Other Value field in the rs temp table
  53.     rs!TimeIn = Me!txtclockin
  54.     rs!TimeOut = Me!txtclockout
  55.     rs!Overtime = "AOT"
  56.     rs.Update
  57.   Next varItem
  58.  MsgBox "Updated Successfully", vbOKOnly + vbQuestion, "Done"
  59.  End If
  61.  End If
  62. ExitHandler:
  63.   Set rs = Nothing
  64.   Set db = Nothing
  65.   Exit Sub
  67. ErrorHandler:
  68.   Select Case Err
  69.     Case Else
  70.       MsgBox Err.Description
  71.       DoCmd.Hourglass False
  72.       Resume ExitHandler
  73.   End Select
  74. End Sub
That is all and hope this hels someone out there..

Do ask if any questions..


May 10 '10 #10

Post your reply

Sign in to post your reply or Sign up for a free account.