forms/subforms

[jzwp22]

To properly evaluate the queries, I had to populate tblEmpJobQual. To do this I used an append query: qryAppendApplicableQualsToAllExistingEmps. This allows the link between the employee, the job title that is assigned to them and the qualifications for that job. Once that table is populated, then an employee can be evaluated on each qualification assigned to them. I created a form frmEmployee with several subforms. If you go to the inner most subform you will see where to rate the employee as basic, proficient, master. Once some of those evaluations are populated, you can better evaluate a query that brings all of the information together. So, first I had to find the most recent evaluation. As I described in my earlier post this is best handled with a totals query. The query name is qryMostRecentEvaluation; I then used that to create the query: qryMostRecentEvalwithDetail

I then used this query in conjuction with another query that gets all of the details about an employee and the qualifications that have to be rated on to arrive at a final summary query: qryEmpDetailsWithMostRecentEvaluationResults

All of the queries and the forms/subforms are in the attached database.

 

[sunshine076]

Wow thanks for your help I thought I knew what I was doing and in which I new that it woudl be a challenged task but I didn't realize how much went into this project. Thank you greatly. You do know your Access.

Will the Employee form allow me to change to cross rate employees? I know that I still need to add additional documents such as training I would do the same as you did for the employees right?

 

[jzwp22]

I'm not sure what you mean by cross rate employees. Do you mean employees who are trained in multiple job titles? The structure I provide should allow you to link an employee with multiple job titles and the qualifications associated with those job titles. You will probably need additional tables to handle the training, but exactly which ones depend on how your business handles it. If training is tied to a qualification then that would be handled differently than if training is directly tied to an employee.

 

[sunshine076]

Couldn't I just copy the main training form but somehow change it to where all employees are required to attend firefighting training, lockout/tagout etc? But yet if I do it this way there is really no where to have specific numbers match up to employees. I figure what I could do was just duplicate (or attempt) the form that you created and substitute the data for departmental training with those who need all training. This training is directly tied to each employee everyone has to be trained. Any suggestions

 

[jzwp22]

The underlying table structure is what's important. The structure for qualifications since it is tied to the job title is totally different than something that would be tied directly to the employee.

tblTrainingCourses
-pkTrainingCourseID primary key, autonumber
-txtTrainingCourseName


tblEmployeeTrainingRequired
-pkEmpTrainReqID primary key, autonumber
-fkEmpID foreign key to employee table
-fkTrainingCourseID foreign key to tblTrainingCourses
-dteCompleted

You can still show the required training in a subform on the employee form I created yesterday.

 

[sunshine076]

For the list of qualifications and their dates is there by chance another way to convert that so that you can see each list for each employee instead of individual?

 

[jzwp22]

Yes, you can set up a form similar to the frmEmployee and show the related qualifications in a subform set to datasheet view rather than single form view, but you will not be able to enter the related evaluation information (date & basic, proficient or master) since you cannot embed a subform into a form that is in datasheet view. I routinely set up forms for data entry separate from forms for data viewing/editing.

 

[sunshine076]

Ok I still may have one or two more questions but right now I am going get working on the other training sheet. Isn't there any other option to be able to view the form instead of it being embeded. I would like to be able to have the form with each individual job title and their quals with b,p,master dates But you are the expert on this one I will try what you have suggested. Thanks.

I did notice that under the qualifications scroll bar it showed all jobs for all job titles. Is there a way to just have it show for that particular job such as polish or light production. I know that the group is tied into each job title and therefore showing all the jobs under the group.

 

[jzwp22]

Regarding this:

..option to be able to view the form instead of it being embeded

Yes, you can show the data in other ways, but to be able to enter data you, the subform is the best approach unless you want to learn some Visual Basic for Application (VBA) coding. That might be something to consider after you have a good handle on forms and queries. You can do some macros, but I have found the VBA coding to be much more flexible and powerful.

Regarding this:

I would like to be able to have the form with each individual job title and their quals with b,p,master dates

You will need a query (actually several queries joined together) to do this. Once you have the query created, you can base a form on it, but be cautioned that the data might not be editable, but you can still use it to view the data.

 

[sunshine076]

I was actually able to create the training form without any problems. Yeah mee. Is there a command or function when creating a report that if I only want one record lets say John Doe I would only get all his information on one sheet instead of it being sheet after sheet? Thanks for all your help.

 

[dendalee]

I am having a similar problem with my database, although I feel like my table structures are ok. Is there anything else that can cause a record to duplicate on all other records? I.E.:

I have a database which tracks FMLA.

The main table:

Employees

EmployeeID is the primary key.

I have built relationships with the following tables:

Accumulated Hours
FMLA (which holds the dates the employee used FMLA time)

These relationships, updates work fine on the Employees form (I used subforms for Accumulated Hours and FMLA on the Employees form.)

Each employee has his or her own separate record when entered on the employee form.

Then I added a new table: Paperwork Tracking

I built the relationship for paperwork tracking/employees the same exact way I did the other two tables which work fine. I then added the subform for paperwork tracking to the Employees form (same thing I did with the other two). When this one is updated however, the record shows for every single employee. I can't get it to separate for each employee.

Any suggestions?

 

[sunshine076]

Have you by chance taken a look at my database? I have it linked above. My form ended up having several subforms and it is possible that your might require the same.

 

[jzwp22]

Regarding your report question, yes there is a way to print only the displayed record currently being viewed on a form. Of course, you will have to create a report first. Then using the command button wizard, add a command button to your form (Report Operations--Preview Report or Print Report --provide report name & step through the rest of the wizard). Go into the code behind the button, it will look something like this. This code will open the report in print preview mode.

    Dim stDocName As String

    stDocName = "Your report name here"
    DoCmd.OpenReport stDocName, acPreview

Amend the code as follows. You will have to substitute your own primary key field for the undelying table of the form. The me.name is the corresponding form control name for that field. In the following example, the pkEmpID is the field name in the employee table and me.pkEmpID is the corresponding form control name.

DoCmd.OpenReport stDocName, acPreview, , "[pkEmpID]=" & Me.pkEmpID

 

[sunshine076]

ok I will give it a try. When this form prints for the qualifications will it print all of them or just what is previewed on the screen. I have decided to leave it how you have it set up.

 

[jzwp22]

What will print depends on how you set up your report (not the form). The data printed in the report will correspond to the employee shown on the form.

 

[jzwp22]

In response to dendalee's question, can you post your table structure in a format similar to the following?

tblName
-fieldname
-fieldname

Please include the relevant tables and fieldnames and how the tables are related to one another. I would not worry about the forms at this point; we have to make sure your table structure is sound.

 

[sunshine076]

For my report I have done what you have suggested but I think that I have gotten something mixed up. I have used the brackets but the error I get is that I have an extra ) in EmpID so I currently have them taken out of the equation but I am still getting the error.

 

[dendalee]

Regarding the problem I was having: I got it fixed:

I simply created a new empty database, copied the tables into the new database without the data (because it was all sample data anyway), and recreated the relationships. Once that was done, I transferred in all of my queries, forms and reports, and voila.

After creating a new Employee form and dragging the other forms in as subforms, it works like a charm. Don't know what the problem was in the old database. Thankfully, I'm not having any issues with this one. Strange thing is, I did exactly the same thing to create the relationships. No changes.

Weird, but I'm glad it's fixed. Thanks for your help.

 

[jzwp22]

You had this in your code

..."EmpID=" & frmEmployees

The frmEmployees references the form, you have to reference the control on the form that holds the employee ID which is empID. You have to precede the empID with the prefix "me" to indicate the current form, so it should look like this

..."EmpID=" & me.EmpID

This was only 1 problem. The other was that the empID field has to be listed in the query that is bound to the report you are trying to print. It wasn't. I amended the underlying query to bring in the EmpID, so it is now working. I've attached the corrected DB.

 

[sunshine076]

I have been able to get the report to print but it still has the next employees name on it. Can I do a pg break between names so that I am only getting one name?

If I enter in any employee ID number and preview it the screen is blank. I only get data from the first page when I print preview