Data Entry Form - 2 Tables

[Steve D]

Good Morning,
I am trying to set up a form that will allow me to enter hours worked
for a list of active employees. I have 2 tables tblEmployees and tblHours.
tblEmployees includes Employee Name, Start Date, Term Date(if any), tblHours
includes Employee Name, Week Ending Date, Hours where Employee Name is the
related field. I would like to create a form that allows the user to select a
week ending date from a calendar and then be able to get a sheet for data
entry that automatically populates with any employee that is active (start
date < week ending date and term date is Null). In theory my thought is this
but I am not sure how to actually make it work:

Enter Date on form
Click Button
Run Query of active employees
Populate a form based on tblHours with the employee names (results of query)
and week ending date so I can data enter the hours.

I am okay with the form to enter the date and I can run the query my problem
is the last piece. If you can assist I would greatly appreciate it.

 

[rm]

First (not to be a smarty a$$) using the employee name to link the
tables isn't good idea: But that is another issue.

There are multiple ways to accomplish your goal. You want to build the
query as you planned. The trick is that you want to use the query as
the data source for your second form.

You could dynamically build the query. It sounds like the "dynamic"
part is to harvest the date from the first form and then plug it into
a SQL statement. Just taking a stab (without a compiler handy):

Dim strSQL as string
Dim dteWeekEnding as Date

dteWeekEnding = myDateTextBox.Value 'note you need logic here to
ensure a valid date - maybe use NZ here

strSQL = "Select employeName, otherRelevantEmployeeData FROM
tblEmployees WHERE [start
date] < #" & dteWeekEnding & "# AND [term date] IS NULL;"

Now use strSQL as the data source for your second form.

You can pass that a number of ways via DOCMD.

Another way is to use the base SQL statement (the sql statement minus
the variable) as the value of a variable in the second form. Pass only
dteWeekEnding (again using DOCMD) then plug that into the SQL
statement.

Good luck with your endevour. Maybe one of the MVPs will chime in with
a better response.