A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Help with my tables



 
 
Thread Tools Display Modes
  #1  
Old April 8th, 2010, 03:26 PM posted to microsoft.public.access.gettingstarted
ryansgrl
external usenet poster
 
Posts: 5
Default Help with my tables

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?

  #2  
Old April 8th, 2010, 04:03 PM posted to microsoft.public.access.gettingstarted
Dorian
external usenet poster
 
Posts: 542
Default Help with my tables

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".


"ryansgrl" wrote:

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?

  #3  
Old April 8th, 2010, 04:05 PM posted to microsoft.public.access.gettingstarted
Dorian
external usenet poster
 
Posts: 542
Default Help with my tables

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" wrote:

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?

  #4  
Old April 8th, 2010, 04:33 PM posted to microsoft.public.access.gettingstarted
ryansgrl
external usenet poster
 
Posts: 5
Default Help with my tables

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?


"Dorian" wrote:

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" wrote:

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?

  #5  
Old April 8th, 2010, 05:28 PM posted to microsoft.public.access.gettingstarted
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Help with my tables

On Thu, 8 Apr 2010 08:33:02 -0700, ryansgrl
wrote:

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.

--

John W. Vinson [MVP]
  #6  
Old April 8th, 2010, 06:53 PM posted to microsoft.public.access.gettingstarted
ryansgrl
external usenet poster
 
Posts: 5
Default Help with my tables


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?


"John W. Vinson" wrote:

On Thu, 8 Apr 2010 08:33:02 -0700, ryansgrl
wrote:

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.

--

John W. Vinson [MVP]
.

  #7  
Old April 8th, 2010, 07:09 PM posted to microsoft.public.access.gettingstarted
Dorian
external usenet poster
 
Posts: 542
Default Help with my tables

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" wrote:

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?


"Dorian" wrote:

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" wrote:

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?

  #8  
Old April 8th, 2010, 07:16 PM posted to microsoft.public.access.gettingstarted
ryansgrl
external usenet poster
 
Posts: 5
Default Help with my tables

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!

"Dorian" wrote:

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" wrote:

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?


"Dorian" wrote:

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" wrote:

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?

  #9  
Old April 8th, 2010, 09:38 PM posted to microsoft.public.access.gettingstarted
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Help with my tables

On Thu, 8 Apr 2010 10:53:01 -0700, ryansgrl
wrote:

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!
--

John W. Vinson [MVP]
  #10  
Old April 9th, 2010, 04:55 AM posted to microsoft.public.access.gettingstarted
Richard
external usenet poster
 
Posts: 1,419
Default Help with my tables

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






 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:27 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.