Updating Forms
- Thread starter KristenD
- Start date
Yes, you would. I assume that you have a foreign key field that has a value that links back to the supervisor table and that you do not have a lookup field in your table. On your form, if you have a control for that field, delete it. Then using the combo box wizard, create a combo box based on the supervisor table and link it to the foreign key field (the wizard will walk you through this process).
If you are using a combo box for you lookup table of Supervisors this needs to be placed on your form and bound to a Supervisors field in your data table .then just select your Supervisor for that particular record. I hope I have understood you requirement.
If I am creating a form, is it better to do it from a query or tables?
I fixed the form that needed updating, I scrapped the other form completely and am starting from scratch. Most of my other forms are built off of tables but I'm wondering since this form for this portion of the db, the table structure is a little different.
I fixed the form that needed updating, I scrapped the other form completely and am starting from scratch. Most of my other forms are built off of tables but I'm wondering since this form for this portion of the db, the table structure is a little different.
I typically base my forms on a table, but it really depends on what you want to present to your users. If the intent of the form is to enter/editing data, then I think the form should be based on the table with the combo box already discussed. If the form is for viewing records only, then I would go with the query approach and not use the combo box for the supervisor since you would just be displaying the supervisor name.
The form is for data entry, it has 4 tables that it will be using. I think.
Two of the tables are only filled out if a certain criteria was met. But I was thinking of using that form in a report. Having a pop up modal form.
Two of the tables are only filled out if a certain criteria was met. But I was thinking of using that form in a report. Having a pop up modal form.
How you set up the forms also depends on the relationships between the tables. For example, if a record in your main form only has 1 associated supervisor, then the combo box is the right approach if you are going to be adding/editing data. If a record in the main has many associated records in a related table, then you would typically use a subform within the main form. The subform would be based on the table that holds the many related records.
I have a main form and there will be 4 sub forms.
I have the first portion updated and it has already been out for testing for 2 months.
The second portion is I thought would be easier as there is not much to track essentially. It is one table which would be the subform and it is related to the main form in a one to many relationship.
The issue I'm having is the report that they are requesting come out of access since MOST of the data is in there. I keep hitting a brick wall as to figure out how to make it work. I can get the OSHA certifcations to track no problem it is just making that jump to connect all the tables for the report. This is where the other 2 tables come in.
I have the first portion updated and it has already been out for testing for 2 months.
The second portion is I thought would be easier as there is not much to track essentially. It is one table which would be the subform and it is related to the main form in a one to many relationship.
The issue I'm having is the report that they are requesting come out of access since MOST of the data is in there. I keep hitting a brick wall as to figure out how to make it work. I can get the OSHA certifcations to track no problem it is just making that jump to connect all the tables for the report. This is where the other 2 tables come in.
Now you are talking about a report not a form. That is different. What I typically do for a report is to create a query that joins the respective tables then use the grouping levels in the report parse out the data. Alternatively you could use a report with subreports which would be similar to a form with subforms.
That's where I'm having trouble. I have the info in the one form based off the one table but there is no data in the other tables as I have not figured out the most logical way to put it there. I was thinking a report based off of a query because the data will change week to week as the criteria (empstatus = active) changes due to the nature of the construction industry.
So I'm not sure how to tie it together...build a form off of a report? I'm at a loss!
So I'm not sure how to tie it together...build a form off of a report? I'm at a loss!
Everything is built from the tables that hold the data. Forms are for data entry, editing and viewing. Reports are for printing the data in a presentable way.
So for your forms, I would recommend using a form/subform design.
For the reports, I would start with a query that joins all of the tables and then use the reports grouping levels to get it to look nice. If that does not work then I would go with the report/subreport approach.
Ok, because the way the tables are set up now, it will not allow me to add or change a record because a related record is required in another table.
I can do the form/subform (although I don't want a datasheet for this info) but can I make it dependent on the Main form?
I attached a snapshot of the form/subform and then I will need another subform for the OSHA subform? and I only want that 2nd subform filled out only if the EmpStatus on the Main form is filled out.
I can do the form/subform (although I don't want a datasheet for this info) but can I make it dependent on the Main form?
I attached a snapshot of the form/subform and then I will need another subform for the OSHA subform? and I only want that 2nd subform filled out only if the EmpStatus on the Main form is filled out.
Attachments
When do you get the error? When entering in the main or subform?
If your have set up your relationships in the relationship window, Access will handle linking the subform to the main form.
What is the record source of the main form? the subform?
Can you provide your relationship diagram or can you post a zipped copy of the database with any sensitive data altered or removed?
You can use a little code to hide the subform until the status is selected
If your have set up your relationships in the relationship window, Access will handle linking the subform to the main form.
What is the record source of the main form? the subform?
Can you provide your relationship diagram or can you post a zipped copy of the database with any sensitive data altered or removed?
You can use a little code to hide the subform until the status is selected
It happens after I enter the information into the subform and go to move to a new record in the OSHA subform tab.
The record source of the main form is tblEmp. For the subform it is tblOSHA, tblEmpInfo and tblJobs.
I have uploaded the relationship report. I can post a zipped copy of the database if you need it. I don't have any sensitive data in the db.
The record source of the main form is tblEmp. For the subform it is tblOSHA, tblEmpInfo and tblJobs.
I have uploaded the relationship report. I can post a zipped copy of the database if you need it. I don't have any sensitive data in the db.
You do not have a direct relationship between the tblEmp and tblOSHA. Access is expecting a related record in tblEmployeeInfo that is why you are getting the error. From what I see of your table structure, it appear that tblEmployeeInfo captures an employee's many job numbers over time and that the OSHA training is related to the job and not directly to the employee. Is that a correct assessment? Is that what you wanted based on how your business operates?
If your structure is in line with your business practices, I believe you should rename the EmpID field in tblOSHA to EmpInfoID to avoid any confusion since that is to what you are joining.
In terms of forms, you need a subform based on tblEmployeeInfo and then within that form you will need the OSHA form, so a main form--subform--subsubform design.
If your structure is in line with your business practices, I believe you should rename the EmpID field in tblOSHA to EmpInfoID to avoid any confusion since that is to what you are joining.
In terms of forms, you need a subform based on tblEmployeeInfo and then within that form you will need the OSHA form, so a main form--subform--subsubform design.
I made an OOPS!! Everything is supposed to relate back to tblEmp as everything is pulled off their Employee ID
The tblOSHA is SUPPOSED to be related to tblEmp. I fixed that so now those tables are related and it is allowing me to enter and save the information. I fixed the relationships and reposted it.
Now I just need to create the form based on the EmpStatus in tblEmp to enter the data into the form based on tblEmpInfo. Is there where I would use a module or code for the pop up form? I have a code written that one of the other users of the forum wrote for SetCntrlProperties.
The tblOSHA is SUPPOSED to be related to tblEmp. I fixed that so now those tables are related and it is allowing me to enter and save the information. I fixed the relationships and reposted it.
Now I just need to create the form based on the EmpStatus in tblEmp to enter the data into the form based on tblEmpInfo. Is there where I would use a module or code for the pop up form? I have a code written that one of the other users of the forum wrote for SetCntrlProperties.
In your earlier attachment showing the form the status control was a combo box, but I do not see any joins to the EmployeeStatus field in tblEmp. Do you have the EmployeeStatus field as a lookup field in your table? Even though Access has the capability of having table-level lookup fields, using them is generally not recommended. This site explains the issues that the table-level lookups can cause. What I would recommend is to create a new table like this
tblEmploymentStatus
-pkEmpStatusID primary key, autonumber
-txtEmpStatus
Then join the above to tblEmp
tblEmp
-EmployeeID primary key, autonumber
-EmployeeName
-fkEmpStatusID foreign key to tblEmploymentStatus (must be a long integer number datatype field)
-Notes
Then on your employee form remove the current combo box for the status and rebuild a new combo box based on the tblEmploymentStatus.
The code I assume will go in the After Update event of the status combo box.
Could you please explain exactly what you want to have happen? I assume that there are multiple status types to choose from, do you want different things to happen with each one?
Could you please explain what type of data is in the employee info table and why it is on the many side of a one-to-many relationship? I just want to make sure I understand its purpose & why you only want it to be shown at certain times.
tblEmploymentStatus
-pkEmpStatusID primary key, autonumber
-txtEmpStatus
Then join the above to tblEmp
tblEmp
-EmployeeID primary key, autonumber
-EmployeeName
-fkEmpStatusID foreign key to tblEmploymentStatus (must be a long integer number datatype field)
-Notes
Then on your employee form remove the current combo box for the status and rebuild a new combo box based on the tblEmploymentStatus.
The code I assume will go in the After Update event of the status combo box.
Could you please explain exactly what you want to have happen? I assume that there are multiple status types to choose from, do you want different things to happen with each one?
Could you please explain what type of data is in the employee info table and why it is on the many side of a one-to-many relationship? I just want to make sure I understand its purpose & why you only want it to be shown at certain times.
Yes, I started it at the table level then removed it so it is only on the form at the moment.
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 statuses drive another report under skill assessment as per our EEO policy we have a strict hiring policy that we must follow (we are a government contractor).
It is only for the OSHA portion and it will end up probably being related to the next portion of the database for licensing as well.
Since we are a construction company we have employees come and go a lot so a typical employee maybe be hired, laid off, rehired, laid off etc several times a year based on the job.
