Help with my tables

[ryansgrl]

Three tables....Employee Information, Employee Hours, Employee PTO

Employee Information
Employee Id (key)
Employee Name
Hire Date
Job Title

Employee Hours
Hours ID (key)
Employee Name
Start Date
End Date
Pay Date
Sal/Regular Hours
Overtime Hours
Vacation Hours
Sick/Personal Hours

Employee PTO
PTO Id (key)
Hours ID
Work Day
Vacation Hours Used
Sick/Personal Hours Used

All seem to link fine in my tables. I open Employee Information table I get
a + to add those employee Hours and if needed a + to open their PTO (paid
time off) for that particular week.
Than I create a form based on Employee Information. I want to Click on a
link for "Hours" and enter hours information for THAT particular Employee,
the problem is I want it to automatically enter the employee name based on
what previous form I had opened. Why doesn't it know I'm working with that
particular employee?
If I'm working with "John Smith" Employee Information Form why can't I click
a link to Hours and "John Smith" already be ready to update? Why do I have to
use the drop down to pick John Smith?

 

[Dorian]

The way to arrange this is to have a main form for Employee and that form
contains subforms for Employee Hours and Employee PTO. That way when you work
with an Employee the Hours and PTO will automatically be linked to the
Employee record.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[Dorian]

I forgot to say, there is a lot of information in Access Help system about
subforms. You will find them a very important aspect for developing Access
applications. The Help system is a good way to increase you knowledge of
Access.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[ryansgrl]

hhmmm.....was trying to stay away from subforms. Is there any way I can open
the Hours form......from my employee form and enter info for that particular
employee?

 

[John W. Vinson]

hhmmm.....was trying to stay away from subforms. Is there any way I can open
the Hours form......from my employee form and enter info for that particular
employee?

Well, yes; you can also use an Excel spreadsheet without ever using
calculations. Why on Earth would you want to throw away one of the most
powerful, user friendly and flexible tools in Access???? Especially, in your
case, you have one parent table and two child tables; it would be very simple
to have a Form for employees (with a combo box to navigate to a particular
employee), with two subforms, one for hours and the other for PTO. What's not
to like?

One concern: in your original message you apparently are copying the employee
name from the Employees table into the Hours table. DON'T! That would be
redundant and unnecessary.

 

[ryansgrl]

OK, don't tell me how to do it!

I wasn't copying employee name it was a look up and relation to Employee
Information table. Am I doing that wrong?

 

[Dorian]

Your answer is like someone who wants to put a nail in the wall without using
a hammer. It's the tool designed for the job, so use it.
Once you learn that it will be another tool in your toolbox you can use for
future use.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[ryansgrl]

I already use sub forms in other databases that I work with. I just didn't
want all the information on ONE form. I feel like it's too much information
and messy working with just the one form.
Thanks for your help. Guess I can still try to make it look pretty!

 

[John W. Vinson]

OK, don't tell me how to do it!

Well, it's A Bad Idea and makes your code a lot more complicated, but here's
how:

Given a form frmEmployee, with a field in its Recordsource EmployeeID, and a
command button cmdHours to open frmHours based on the Hours table, something
like the following should work.

Private Sub cmdHours_Click()
Dim strWhere As String
On Error GoTo Proc_Error
strWhere = "[EmployeeID] = " & Me!EmployeeID
DoCmd.OpenForm "frmHours", WhereCondition:=strWhere,OpenArgs:=Me!EmployeeID
Proc_Exit: Exit Sub
Proc_Error:
MsgBox "Error " & Err.Number & " in cmdHours_Click: " & Err.Description
Resume Proc_Exit
End Sub

Then in frmHours Open event,

Private Sub Form_Open()
If Len(Me.OpenArgs) > 0 Then
Me.EmployeeID.DefaultValue = """" & Me.OpenArgs & """"
End If
End Sub

Untested air code, won't work as well as a subform, but should get you
started.

I wasn't copying employee name it was a look up and relation to Employee
Information table. Am I doing that wrong?

See http://www.mvps.org/access/lookupfields.htm for a critique of the Lookup
Field misfeature. It's of VERY limited value; it makes it a bit easier to use
a table datasheet but you should never be using table datasheets anyway!

 

[Richard]

Hi,

I was thinking about this approach.(Caution rookie thinking).

What about a new yes/no field in your table.
A bound check box control for that field on your main form.
A new query based on the fields you need to update, plus the yes/no field
A new update form (maybe a small popup).

When the check box is checked it launches the update form.
You would only see the one record you were currently working with.
And the optional fields you need to update.
When you exit the update form it clears the check box and refreshes the data
in the main form. Of course this is only the main points.

Or maybe a tabbed control..


Richard

 

[BruceM via AccessMonster.com]

Just curious as to how a form next to (or on top of) a form is neater than a
subform. From what I can tell you want to open the main employee form, then
open a form based on a linked table, with subform functionality but without
using a subform.

OK, don't tell me how to do it!

I wasn't copying employee name it was a look up and relation to Employee
Information table. Am I doing that wrong?
[quoted text clipped - 11 lines]

name from the Employees table into the Hours table. DON'T! That would be
redundant and unnecessary.