Updating Forms

[jzwp22]

Your table structure in Access will not support what you show in the spreadsheet you provided. If an employee can have many statuses over time, that describes a one-to-many relationship so having 1 status in the employee table will not work. Additionally, you will need to track WHEN each status change occurs since that is important to your reporting for a period of time.

I think the employee table should be modified as follows:

tblEmp
-EmployeeID primary key, autonumber
-EmployeeName
-Notes

tblEmployeeStatus
-pkEmpStatusID primary key, autonumber
-fkEmployeeID foreign key to tblEmp
-fkEmpStatusID foreign key to tblEmploymentStatus
-dteStatusEff (date the status became effective)

Regarding the spreadsheet, where in your database do you store the certificate/card info relative to OSHA? Can an employee go from none to certificate to card over time? How are you tracking that. (I have no idea what the certificate or card means so you might have to explain that part).

 

[KristenD]

Your table structure in Access will not support what you show in the spreadsheet you provided. If an employee can have many statuses over time, that describes a one-to-many relationship so having 1 status in the employee table will not work.

Ok, because what I have been doing currently is just changing the status so it overwrites what was previously there. SO this is NOT a good practice?

Regarding the spreadsheet, where in your database do you store the certificate/card info relative to OSHA? Can an employee go from none to certificate to card over time? How are you tracking that. (I have no idea what the certificate or card means so you might have to explain that part).

I have that stored in tblOSHA in the field Document. Same thing I was going to overwrite that data as I really don't want to track when they had no training, then a certificate then a card.

Essentially what this training is, is a 10 or 30 hour construction safety course that employees are required to take in order to be on jobs as required by various contracts. Some contracts may not require it but as employees transfer to different jobs those contracts may have a 10 hour course requirement or to have taken the 10 hour course in the past 5 years. So an employee may have 2 OSHA 10's and an OSHA 30 which would be the many side on the tblOSHA to the 1 tblEmp.

The reason I am tracking the card vs certificate is the card is a valid document recognized by OSHA and various gov't agencies as it has a trackable number on the card. The certificate is just proof of taking the course but it takes 8-10 weeks to receive a card. So this is just a way for me to stay on top of those employees that have only turned in certificates and not the cards.

I hope I'm making sense.

 

[jzwp22]

Ok, because what I have been doing currently is just changing the status so it overwrites what was previously there. SO this is NOT a good practice?

If just overwriting makes sense with the process you are trying to model then it is OK, but for the reporting that you have to do, overwriting may not make any sense. Since you know your application better than I do, it is really your call. For your reporting, does OSHA care if an employee left in the middle of the year? If so, if you mark that employee as No Rehire, you may miss them in your reporting for the year. Whereas if you track the employee's status over time you can find those employees who were active at any point within a prescribed time period using the structure I proposed.

Same thing I was going to overwrite that data as I really don't want to track when they had no training, then a certificate then a card.

If it doesn't matter from your reporting perspective then overwriting is OK

 

[KristenD]

If just overwriting makes sense with the process you are trying to model then it is OK, but for the reporting that you have to do, overwriting may not make any sense. Since you know your application better than I do, it is really your call

For all reporting, it will only matter who is currently working. When they are rehired, laid off, etc. is tracked through the Skill Assessment tab.

Once an employee is marked No Rehire, down the road once I get the database up and running I would like to export those employees to an "archived database" so they don't show up at all.

The main purpose of the database was originally a performance tracking for hiring purposes to aid in following our EEO policy. Now it has grown into a tracking database for tracking the OSHA as well as state licenses and other certifications necessary for the industry.

Essentially what it will be used for is to make sure all active employees are up to date with their licenses and we have all proper documentation for other certifications as well as reports of when certs expire and so on and so forth.

As of right now, it takes me almost a full work day to do all the reporting as it stands now between 4 excel spreadsheets and manipulating data. This is done weekly for all active employees. This is why the status over time does not matter is because I could realistically have in a weeks time period 30 employees come and go. Once they are no longer active, I don't want to or need to worry about them. Once they become active again then I will need to track everything.

I hope I explained that well enough!

 

[jzwp22]

Thank you for providing the details. So you would not need this table I suggested earlier

tblEmployeeStatus
-pkEmpStatusID primary key, autonumber
-fkEmployeeID foreign key to tblEmp
-fkEmpStatusID foreign key to tblEmploymentStatus
-dteStatusEff (date the status became effective)

So going back to your form issue...

There are 4 statuses to choose from: Active, Eligible, No Rehire and Disability. I only want a form to pop up with Active is this is what will drive the report I am looking to do. I will attach it as right now it is done in Excel. Hopefully the report will explain what I am trying to do. Basically, I want a report of all Active employees grouped by job to show their OSHA training and what current documentation we have on file for them.

The pop up form is one thing, your report is another. I would treat the report separately. You would use a query and since the report you presented is based on job number, then I would start there. You would filter the employees related to a job to only include those employees whose status is active. Once you have the query returning the records you want, then I would create the report based on that query.

Now for the form, having a pop-up form is a little trickier since you have to link the data to the person listed in the main form. A little easier approach is to have the applicable form as a subform on your employee main form and just make it visible or not depending on the status combo box. You will need code in the after update event of the status combo box but also in the on current event of the form. Luckily that code will be essentially the same for both events.

 

[KristenD]

I think I get that.

When I create the form, under the property sheet on the OnCurrent and AfterUpdate rows is where I would put the code in order to make it visible based on EmpStatus.

 

[jzwp22]

In general that is correct, but the form has its set of events and the combo box has its set. So you want the On Current event of the form and the after update event of the combo box.

You will need to click in the applicable row which should activate a small button with 3 dots (...), click that button & you will be prompted as to whether you want to create an expression, a macro or code. (I generally favor code--you can do more).

If you set up the combo box with the primary key value included, that is typically set as the bound column of the combo box and it is what you work with in code.

The code for the after update event of the combo box will look something like this:

IF me.comboboxname= x THEN
me.subformname.visible=true
ELSE
me.subformname.visible=false
END IF


The x in the above would correspond to the primary key value of the record of the status you want.

For the on current event of the form, are you going to be using the form for adding new records as well as editing existing records? If so, what do you want to have happen to the subform when you add a new record?

 

[KristenD]

For the on current event of the form, are you going to be using the form for adding new records as well as editing existing records?

It will be for both adding and editing records.

If so, what do you want to have happen to the subform when you add a new record?

It should stay open until I go to the next employee. For the most part once all of the data is entered through the OSHA tab, unless we have a new hire the subform for the tblEmpInfo will be the main form used to create the weekly report. DOes that make sense?

 

[jzwp22]

For the on current event of the form, we have to check to make sure whether it is a new record or not


IF me.NewRecord THEN
me.subformname.visible=true
ELSE
IF me.comboboxname= x THEN
me.subformname.visible=true
ELSE
me.subformname.visible=false
END IF
END IF

..unless we have a new hire the subform for the tblEmpInfo will be the main form used to create the weekly report

You do not base a report on a form, you can only base a report on a table or query.


So your next step is to create the query that you will use for the report. It usually helps to have some sample data populated in the various tables to make sure the query is returning the information you want to include in the report.

 

[KristenD]

You do not base a report on a form, you can only base a report on a table or query

Yes, I'm sorry I misspoke.

I will work on getting sample data into the db tonight and tomorrow and post tomorrow once it is complete.

Thank you thank you!!!!

 

[jzwp22]

You're welcome. Let me know if you have any questions in developing the query you will need for the report. Keep in mind that the data in the query will show a lot of repeated values that will be taken care of with the grouping you can do in the report.

 

[KristenD]

I am in the process of getting the data in as I had another project come up yesterday so I had to put this one on hold.

I have both forms ready for entry BUT I can't get the form to pop up with the Active status.

I put it as a Subform on the Main form but when i go to the next record or find an employee that is active the form does not pop up.

I used the codes that you had given me and it's not working correctly. For some reason it keeps erroring and highlighting the first line of the code but that's it.
What am I doing wrong??

 

[jzwp22]

Can you post a copy of the database with any sensitive data altered or removed? Make sure to run Access' Compact and Repair utility from the tool menu before zipping and posting the database.

 

[KristenD]

Here is the compressed version.

It is actually doing the opposite it is visible if the employee is NOT active now.

Thank you for taking a look at it!

 

[jzwp22]

OK, I saw several issues. First, you had the code in the on current event of the form; that part was correct, but you also had code in the after update event of the form. That code should be in the after update event of the employment status combo box.


Also, you referenced me.OSHAbyJob. The subform name was actually tblEmpInfo. I went ahead and changed it to OSHAbyJob. While in code if you type me. a window should pop up with various control & property names to choose from. OSHAbyJob was not showing up in the list--that is how I found the naming issue.

Next, I noticed that the subform was not linked to the main form, so I took care of that also.

The DB with the corrected form is attached