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
|
|||
|
|||
Auto Fill
I'm trying to enter employee training information. I want to be able to type
in an identifier (last name or employee number) and it will bring up information showing what training they received, and the date they received it. I know I have to make a table with all the information I need, but that's where I'm stuck. I dont' know what to use (form, query, etc.) and I don't know how to set any of that up. Basically, I'm totally lost Any help is GREATLY appreciated! |
#2
|
|||
|
|||
Auto Fill
On Thu, 25 Feb 2010 13:46:06 -0800, Amy wrote:
I'm trying to enter employee training information. I want to be able to type in an identifier (last name or employee number) and it will bring up information showing what training they received, and the date they received it. I know I have to make a table with all the information I need, but that's where I'm stuck. I dont' know what to use (form, query, etc.) and I don't know how to set any of that up. Basically, I'm totally lost Any help is GREATLY appreciated! The data would be *stored* in a table; selected and organized in a Query; and *displayed* on a Form. You will need at least three tables: Employees (with the employee ID, last name, first name, and other biographical data); Courses, a list of all the training courses available, with CourseID, CourseName, and other info about the course; and CoursesTaken, with fields for the EmployeeID, CourseID, and other info about this employee's participation in this course (e.g. date taken, satisfactor/unsatisfactory performance, etc.) For some resources (including a couple of sample training applications) see the links at these sites: Jeff Conrad's resources page: http://www.accessmvp.com/JConrad/acc...resources.html The Access Web resources page: http://www.mvps.org/access/resources/index.html Roger Carlson's tutorials, samples and tips: http://www.rogersaccesslibrary.com/ A free tutorial written by Crystal: http://allenbrowne.com/casu-22.html A video how-to series by Crystal: http://www.YouTube.com/user/LearnAccessByCrystal MVP Allen Browne's tutorials: http://allenbrowne.com/links.html#Tutorials -- John W. Vinson [MVP] |
#3
|
|||
|
|||
Auto Fill
Thank you for helping! Here is my next problem
I have created the tables to store all the information. My question now, is how do I get it all to connect? I have created a query and a form but I don't think I did it right. I would like to be able to type in the employee's name and have all the training they have completed show up. If I type information such as employee name and course into a form, how is the data stored? Is it saved somewhere? "John W. Vinson" wrote: On Thu, 25 Feb 2010 13:46:06 -0800, Amy wrote: I'm trying to enter employee training information. I want to be able to type in an identifier (last name or employee number) and it will bring up information showing what training they received, and the date they received it. I know I have to make a table with all the information I need, but that's where I'm stuck. I dont' know what to use (form, query, etc.) and I don't know how to set any of that up. Basically, I'm totally lost Any help is GREATLY appreciated! The data would be *stored* in a table; selected and organized in a Query; and *displayed* on a Form. You will need at least three tables: Employees (with the employee ID, last name, first name, and other biographical data); Courses, a list of all the training courses available, with CourseID, CourseName, and other info about the course; and CoursesTaken, with fields for the EmployeeID, CourseID, and other info about this employee's participation in this course (e.g. date taken, satisfactor/unsatisfactory performance, etc.) For some resources (including a couple of sample training applications) see the links at these sites: Jeff Conrad's resources page: http://www.accessmvp.com/JConrad/acc...resources.html The Access Web resources page: http://www.mvps.org/access/resources/index.html Roger Carlson's tutorials, samples and tips: http://www.rogersaccesslibrary.com/ A free tutorial written by Crystal: http://allenbrowne.com/casu-22.html A video how-to series by Crystal: http://www.YouTube.com/user/LearnAccessByCrystal MVP Allen Browne's tutorials: http://allenbrowne.com/links.html#Tutorials -- John W. Vinson [MVP] . |
#4
|
|||
|
|||
Auto Fill
Your form should be based on a query on the CoursesTaken table which
references a combo box of employees on the same form. I'll come back to the combo box later, but the query would be: SELECT * FROM [CoursesTaken] WHERE [EmployeeID] = Form![cboEmployee]; Make the form a continuous form. If you use the form wizard to create it select 'tabular' as the layout. You only need controls in the form for the CourseID and other columns like the date taken etc, not for the EmployeeID. In the form make the control bound to the CourseID column a combo box set up like this: ControlSource: CourseID RowSource: SELECT [CourseID], [CourseName]FROM [Courses] ORDER BY [CourseName]; BoundColumn: 1 ColumnCount: 2 ColumnWidths: 0cm;8cm If your units of measurement are imperial rather than metric Access will automatically convert them. The important thing is that the first dimension is zero to hide the first column and that the second is at least as wide as the combo box. In the form's header add a combo box and set it up like this: Name: cboEmployee ControlSource: Leave this blank RowSource: SELECT [EmployeeID], [FirstName] & " " & [LastName] FROM [Employees] ORDER BY [LastName], [FirstName]; BoundColumn: 1 ColumnCount: 2 ColumnWidths: 0cm;8cm In the AfterUpdate event procedure of this combo box put: Me.Requery This requeries the form so it shows only the courses for the selected employee. If you are unfamiliar with entering code in event procedures you do this by selecting the cboEmployee combo box in form design view and opening its properties sheet if its not already open. Then select the After Update event property in the properties sheet. Click on the 'build' button; that's the one on the right with 3 dots. Select 'Code Builder' in the dialogue, and click OK. The VBA window will open at the event procedure with the first and last lines already in place. Enter the line of code between these two existing lines. When the form opens it will not show any records. Select an employee from the combo box and it should then show any courses taken by the employee. You can add a new attendance at a course for the current employee by adding a new record in the blank row below the existing course attendance records simply by selecting a course in the combo box and entering the date taken etc as appropriate. Ken Sheridan Stafford, England Amy wrote: Thank you for helping! Here is my next problem I have created the tables to store all the information. My question now, is how do I get it all to connect? I have created a query and a form but I don't think I did it right. I would like to be able to type in the employee's name and have all the training they have completed show up. If I type information such as employee name and course into a form, how is the data stored? Is it saved somewhere? I'm trying to enter employee training information. I want to be able to type in an identifier (last name or employee number) and it will bring up [quoted text clipped - 33 lines] MVP Allen Browne's tutorials: http://allenbrowne.com/links.html#Tutorials -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...arted/201003/1 |
#5
|
|||
|
|||
Auto Fill
Thanks for your help. But is there any way you could dumb it down for me?
I am unfamiliar with entering any kind of code. Your help is SOOO much appreciated!!! "KenSheridan via AccessMonster.com" wrote: Your form should be based on a query on the CoursesTaken table which references a combo box of employees on the same form. I'll come back to the combo box later, but the query would be: SELECT * FROM [CoursesTaken] WHERE [EmployeeID] = Form![cboEmployee]; Make the form a continuous form. If you use the form wizard to create it select 'tabular' as the layout. You only need controls in the form for the CourseID and other columns like the date taken etc, not for the EmployeeID. In the form make the control bound to the CourseID column a combo box set up like this: ControlSource: CourseID RowSource: SELECT [CourseID], [CourseName]FROM [Courses] ORDER BY [CourseName]; BoundColumn: 1 ColumnCount: 2 ColumnWidths: 0cm;8cm If your units of measurement are imperial rather than metric Access will automatically convert them. The important thing is that the first dimension is zero to hide the first column and that the second is at least as wide as the combo box. In the form's header add a combo box and set it up like this: Name: cboEmployee ControlSource: Leave this blank RowSource: SELECT [EmployeeID], [FirstName] & " " & [LastName] FROM [Employees] ORDER BY [LastName], [FirstName]; BoundColumn: 1 ColumnCount: 2 ColumnWidths: 0cm;8cm In the AfterUpdate event procedure of this combo box put: Me.Requery This requeries the form so it shows only the courses for the selected employee. If you are unfamiliar with entering code in event procedures you do this by selecting the cboEmployee combo box in form design view and opening its properties sheet if its not already open. Then select the After Update event property in the properties sheet. Click on the 'build' button; that's the one on the right with 3 dots. Select 'Code Builder' in the dialogue, and click OK. The VBA window will open at the event procedure with the first and last lines already in place. Enter the line of code between these two existing lines. When the form opens it will not show any records. Select an employee from the combo box and it should then show any courses taken by the employee. You can add a new attendance at a course for the current employee by adding a new record in the blank row below the existing course attendance records simply by selecting a course in the combo box and entering the date taken etc as appropriate. Ken Sheridan Stafford, England Amy wrote: Thank you for helping! Here is my next problem I have created the tables to store all the information. My question now, is how do I get it all to connect? I have created a query and a form but I don't think I did it right. I would like to be able to type in the employee's name and have all the training they have completed show up. If I type information such as employee name and course into a form, how is the data stored? Is it saved somewhere? I'm trying to enter employee training information. I want to be able to type in an identifier (last name or employee number) and it will bring up [quoted text clipped - 33 lines] MVP Allen Browne's tutorials: http://allenbrowne.com/links.html#Tutorials -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...arted/201003/1 . |
#6
|
|||
|
|||
Auto Fill
I'll try, but to paraphrase Einstein, some things can be made as simple as
possible but no more so. I'm assuming you've created the tables just as John described, that the Employees table has data, and that the Courses table has been filled with course data. 1. First you need to create a query, so open the query designed; don't add any tables and then switch to SQL view. In the SQL window paste in the following in place of what's the SELECT * FROM [CoursesTaken] WHERE [EmployeeID] = Form![cboEmployee]; 2. Save the query under a suitable name. If you try and open it now it will prompt for a parameter value. Don't worry about that; its just because the form and its combo box isn't yet created. 3. Create a continuous forms view form based on this query, which you can do with the form wizard. Again if you try and open the form it will at this stage prompt for a parameter value, but don't worry about that. 4. Open the form in design view. It will at this stage probably have a text box as the control bound to the CourseID field. Chage this to a combo box which you can do by right clicking on the text box and selecting ChangeTo ïƒ* Combo Box from the short cut menu. Open the combo box's properties sheet if its not already open and set its properties as I described in my last post. 5. With the form still open in design view add a cboEmployee combo box in the form header. Set its properties as I described in my last post. 6. Finally add the line Me.Requery to the cboEmployee combo box's AfdterUpdate event procedure in the way I described. 7. Save the form. You should then be able to switch the form to form view and select an employee from the combo box to see any courses they've been assigned to in the CoursesTaken table, and add any course you wish to assign them to. If you need to assign an employee to a course which is not yet in the Courses table there is a way this can be done by typing the course name directly into the CourseID combo box on the form, but that's something we can come back to later once you've got the form working correctly otherwise. Ken Sheridan Stafford, England Amy wrote: Thanks for your help. But is there any way you could dumb it down for me? I am unfamiliar with entering any kind of code. Your help is SOOO much appreciated!!! Your form should be based on a query on the CoursesTaken table which references a combo box of employees on the same form. I'll come back to the [quoted text clipped - 76 lines] MVP Allen Browne's tutorials: http://allenbrowne.com/links.html#Tutorials -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...arted/201003/1 |
#7
|
|||
|
|||
Auto Fill
If you are seeing a weird symbol in paragraph 4 of my last post its meant to
be a right pointing arrow. Ken Sheridan Stafford, England -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...arted/201003/1 |
Thread Tools | |
Display Modes | |
|
|