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. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|