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

**Seth Schrock** · Aug 30 '13, 06:23 PM

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** · Aug 30 '13, 06:54 PM

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** · Aug 30 '13, 07:30 PM

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** · Aug 30 '13, 07:36 PM

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.

Code:

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** · Aug 30 '13, 07:53 PM

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** · Aug 30 '13, 08:14 PM

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_Work ed" 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:

Code:

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** · Aug 30 '13, 08:39 PM

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_M aster 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.

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

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

Comment

Comment

Comment

Comment

Comment

Comment

Comment