How can I list all records in a table at once on a form, and then enter data?

[DanicaDear]

I am almost embarassed to ask this question but I can't figure it out...

I want to build a simple database where I track the overtime for 17 employees. I have two tables: tblEmployees (only contains a list of names) and tblHours (where I will put overtime hours). I had originally just put this all in one table but after I couldn't make it do what I wanted I split the tables. The reason I decided to split the table was because the hourly information will repeat weekly but the employees list will be constant. I can go back to one if recommended.

What I want to do is have a form where it lists all 17 employees at one time, so the supervisor can make one pass and record all the hours for all the employees for one day on one form. (Think of an Excel sheet.) Problem one is I can't get all the employees to show up down the form. The form just has one blank, I guess waiting for me to type in an employee name. (I don't want to type in. I just want it to be there, all 17, one after the other.)

So when I get this accomplished, I will want to enter overtime for Friday, Saturday, and Sunday every week. I plan to enter Friday first. After my first Friday's entry, how would I clear the form, saving Friday's entry, and start all over to enter Saturday?

Thanks in advance. :-)

[Seth Schrock]

You will need to create a loop in VBA that goes through each employee record. Inside this loop, you would need to create a record in table that tracks the hours that would include the employee id and then just put 0 as the overtime hours as well as the date for which the overtime is being entered. Then all the supervisor needs to do is go through the list and change the overtime values from 0 to the correct value.

[DanicaDear]

Thanks Seth! Any hints on how to write the VBA to do that? I don't know how to write VBA, although I have learned from Bytes how to understand it somewhat.

These are the fields I want to collect for each employee for each day.
Date_of_OT (This will be the same for all 17 employees in one pass so I had hoped to enter in the top of my form and have the default in the 17 other fields set to copy that text box in the top, so as not to have to enter the date 17 more times)
Available_Hours (This will be the same for all 17 employees in one pass so I had hoped to enter in the top of my form and have the default in the 17 fields set to copy that text box in the top, so as not to have to enter it 17 more times)
Hours_Worked (changes per employee)
Required (changes per employee) (This let us know if the overtime worked was optional or required.)

Could a continuos form help me instead of VBA?

[DanicaDear]

I thought this would be easy, but the more I play with it the more I think it isn't. (No wonder I couldn't come up with a simple solution...)

I think I'll just use a form with a subform and the user will just have to arrow through all 17 entries...

If anyone has further tips, I'm happy to try things out.

[Seth Schrock]

Well, you will have to make several changes to make it fit your tables, but I can provide a framework for you. Warning, this is air code and is untested, so it may contain errors. Just post the exact message and number of the error as well as which line gets highlighted if you do get any errors.

Dim db As DAO.Database
Dim rstEmp As DAO.Recordset
Dim rstHours As DAO.Recordset
 
Set db = CurrentDb
Set rstEmp = db.OpenRecordset("tblEmployees", dbOpenDynaset)
Set rstHours = db.OpenRecordset("tblHours", dbOpenDynaset)
 
With rstEmp
    Do While Not .EOF
        rstHours.AddNew
 
        rstHours!EmployeeID_fk = !EmployeeID_pk
        rstHours!Date_of_OT = Me.DateFieldOnForm
        rstHours!Hours_Worked = 0
        rstHours!Required = False
 
        rstHours.Update
        .MoveNext
    Loop
End With
 
Set db = Nothing
Set rstEmp = Nothing
Set rstHours = Nothing

Lines 6 & 7 need the table names changed, line 13 needs the correct field names (EmployeeID_fk is the foreign key field in the hours table and EmployeeID_pk is the primary key field in the employee table). Line 14 needs the correct control name.

[DanicaDear]

Do I just make a form with my 5 fields ("Name" from tblEmployee and the other 4 fields from tblHours) and paste this code in the On Load event?
(Sorry for so many questions)

[DanicaDear]

Ok, I tried out what I asked in my last question.
The code executed with no errors. The "0" was insert as the code instructed.

However, the list of 17 employees did not appear down the left hand side (and therefore I couldn't fill in the 2 fields ["Hours_Worked" and "Required"] needed for each individual). However, it did save 17 records in tblHours when I dirtied the form. :-)

Also, it did not save the master date and master hours data (two fields same for everybody) in tblHOURS with the other data it saved automagically. Upon looking further though I didn't see a reference in the code for Hours_Master (the number of available OT hours) to copy into the tblHours. I feel like you could save so much time by peeking into my database. Is there a way I can attach a screen shot, or even the DB itself??

I think you're on the right track....(I'm impressed how you experts take our problems and wrap them up into code!).

Here's my current code with updated fields:

Option Compare Database
 
Private Sub Form_Load()
Dim db As DAO.Database
Dim rstEmp As DAO.Recordset
Dim rstHours As DAO.Recordset
 
Set db = CurrentDb
Set rstEmp = db.OpenRecordset("tblEmployees", dbOpenDynaset)
Set rstHours = db.OpenRecordset("tblHours", dbOpenDynaset)
 
With rstEmp
    Do While Not .EOF
        rstHours.AddNew
 
        rstHours!Name = !Name
        rstHours!Date_of_OT = Me.Date_Of_OT_Master
'should Hours_Master be referenced here so it will update?
        rstHours!Hours_Worked = 0
        rstHours!Required = False
 
        rstHours.Update
        .MoveNext
    Loop
End With
 
Set db = Nothing
Set rstEmp = Nothing
Set rstHours = Nothing
 
End Sub

[Seth Schrock]

Well, there are a couple of options for how to run this code. What I would probably do would be to Create a form that was unbound that would have your Me.Date_Of_OT_Master control as well as a button that when clicked would run the code and a subform that would contain the data that needs viewed. At the bottom of the button's OnClick event, you would need to requery the subform so that it could view the data that has been added.

And you answer your comment on line 18, you would enter a line for the Hours_Master on that line to set it to your value.