Updating table based on main form and combo box on subform (1 Viewer)

[Design by Sue]

I have a form with a sub form that is used to enter information about job training. The main form is used to enter info into the training table that includes fields for the training number, type, description, expiration, if it is obsolete and if it is mandatory. The subform is linked to the job description training table that has fields for the training number - from the main form - and the job descriptions that require that training. There can bee mulitple records for one training number as different job descriptions may require that training. On the main form there is a check box (yes/no field) that the user checks to indicate that the job training is mandatory. If the training is manditory it means all job descriptions require that training.

On the subform there is an unbound combo box with a row source is a query that displays all of the job descriptions. This alllows the user to select one of job descrptions and then click a button to addt job description to the job description training table. This updates the job description training table to add training number from the main form and the selected job description on the subform.

So I would like to create a new button that would instead create a record for each of the job descriptions in the database with the training number from the main form (just as the current button does but for all of the descriptions). Here is a modified verison of the code I am using to add one of the job descriptions currently (these are not actually the names in the database but hopefully changed to make it more understandable.)

"INSERT INTO [JobDescriptionTrainingTbl] ([TraiiningName],[JobDescription]) VALUES ('" & ESC(Parent.TrainingNumber) & "', '" & ESC([JobDescriptiom]) & "’)"

Ideally I would like this update to be done automatically when the mandatory selection is made on the main form (and if mandatory is deselected remove all job descriptions) BUT this is above my abilities so I can work with buttons on the sub form that adds all job descriptions or removes all job descriptions.

I hope that makes sense and is enough info for someone to help me get this working. Thanks in advance for any assistance
Sue

 

[The_Doc_Man]

From the description, you have a ready place where you can trigger this action. On your form, you have a "Mandatory" check box. You suggest there is a field to remember the training selection for Is/Is Not mandatory for all jobs, i.e. a "bound" control linking a Y/N field to a check box control. In that case you can put code behind either the chkbox_Change event or the chkbox_Click event. (Pick only one, of course.) The click event is probably what would cause the Change event to occur. In the event code, you can test the checkbox or the underlying Y/N field for True or False to decide whether to perform a bulk INSERT INTO or DELETE query.

Here is where you are missing something and it indicates you need a bit more study before you try to implement this.

You have a training table. You have a job table. They basically don't change if you have a mandatory or a non-mandatory training. That is, the job description doesn't change if you add a training course or if you remove one. The training description doesn't change if you add or remove a job to the list of jobs that need it. These are two independent tables that share a many-to-many relationship.

The tables don't change (much) when you add or remove a "mandatory" flag. But what DOES change is the association of the records of one table with the records of the other table.

In Access this is managed by a JUNCTION TABLE (you can look up the capitalized phrase there to find out more.) Your Job/Training Association table will hold entries to show that training class X is associated with job Y. A flag for Mandatory should be in this junction table as well, to record the fact that it got there from the MANDATORY flag on your form. From your description, it might be possible to consider that the training is not mandatory, but is allowed, for a given job.

It seems to me that you need to look at your layout a bit with that junction concept in mind, because from what you described in your original post, there would be either a logical or mechanical issue regarding how to correctly designate the relationship of a course to a job. If you DO have a case of a non-mandatory training course that belongs to a job, then you CERTAINLY need to rethink your layout.

Let me also be clear that Access absolutely can handle this relationship. But you need to first assure that your structure is corrected for the idea I just presented.

 

[Design by Sue]

Thank you for your extensive reply. I have to digest this and refer to my tables to confirm the statement. I will do this in the next day or so and post back in the hopes of continued assistance in getting this to work

 

[Design by Sue]

I am not exactly sure how to answer your question so here is a break down of the tables involved.

The Training table has 6 fields, but the only two pertenant to this problem are
Training Number (Text)
Job Description (Text which is the title of the training
Manditory field (yes/no).
The mainform mentioned in my original post adds thei nformation to this table (the other fields are lists in my original post but as I said they don't really have any part of this issue)

The Job Description table has one field:
Job Description (text) and has one line for each of the job descriptions at the company
This table is updated in another form but again not part of this issue.

The Job Description - Training table has 2 fields:
Job Description (text) This is linked to the Job Description from the Job Description table
Required Training (Text) this this is linked to the Training Number from the Training table
There are many of each of these in combination, each Job Description can have any number of Required Trainings and there be many Trainings thart are required by many Job Descrptions

I hope this information with my original post is enough to get assistance with this code.

Again thank you for any replies.

 

[mike60smart]

I am not exactly sure how to answer your question so here is a break down of the tables involved.

The Training table has 6 fields, but the only two pertenant to this problem are
Training Number (Text)
Job Description (Text which is the title of the training
Manditory field (yes/no).
The mainform mentioned in my original post adds thei nformation to this table (the other fields are lists in my original post but as I said they don't really have any part of this issue)

The Job Description table has one field:
Job Description (text) and has one line for each of the job descriptions at the company
This table is updated in another form but again not part of this issue.

The Job Description - Training table has 2 fields:
Job Description (text) This is linked to the Job Description from the Job Description table
Required Training (Text) this this is linked to the Training Number from the Training table
There are many of each of these in combination, each Job Description can have any number of Required Trainings and there be many Trainings thart are required by many Job Descrptions

I hope this information with my original post is enough to get assistance with this code.

Again thank you for any replies.

Hi Sue
Are you able to upload a zipped copy of the database with no confidential data?

 

[Design by Sue]

It is a huge database - I am not sure how I could remove all confidential data. There are many forms, tables, queries and reports and I could not be sure I could get everything removed. Could I trim down the database to only the 3 tables and the form I am talking about?

 

[Pat Hartman]

It is hard to see the whole problem. I think you are missing at least one table.

You could post the relevant part of your database diagram. Make sure you expand the tables enough so that we can see the PKs and all the relevant FKs as well as the join lines.

I understand the concept of what you want to do and to do it, you use an append query. The append query has two arguments.
1. the FROM JobDescID
2. the TO EmpID

So you add a row to the employee job table and then run the query that appends the related training to the junction table.

That's as close as I can get based on your description. The names are murky and I'm pretty sure you don't have the correct structure so post the picture that shows what you have. OR just make a db with just these tables. the jobs and training tables shouldn't be proprietary and you can add a few rows of test data for the other tables.

 

[Design by Sue]

Thank you - I will try to do this on Friday. I don't understand what table I might be missing. In my original post I showed the code that is working from the subform - my understanding is that I just need to do something similar on the main form on the mandatory check box. And I am sure that is only using the 3 tables I mentioned. (Yes the table names are vague as I am trying not to use the actual names from my database for security (maybe not necessary))Can you give me an idea of what other table I might be missing?

 

[Gasman]

It is a huge database - I am not sure how I could remove all confidential data. There are many forms, tables, queries and reports and I could not be sure I could get everything removed. Could I trim down the database to only the 3 tables and the form I am talking about?
?

You only need enough to show the problem.
Create a new DB and import what you need to show the problem.
Bring in some data as well. If split make them local in the upload DB.

Then run something like this to obfuscate the data where needed.

 

[Pat Hartman]

Nope, it's not all here. You didn't send the BE. It is probably better to just embed the tables in the test db

 

[Design by Sue]

Tables are in the database not separated (for this upload)

 

[Pat Hartman]

These are linked tables. They are not embedded

And this is the error message:

 

[Design by Sue]

Sorry - let me check

 

[Design by Sue]

Thank you for you patience! It has been so long since I worked with access and my "advisor" was my brother who passed away - so it has been hard getting back to this. I think all is here now. (hopefully I got it right this time)

 

[spaLOGICng]

I have a form with a sub form that is used to enter information about job training. The main form is used to enter info into the training table that includes fields for the training number, type, description, expiration, if it is obsolete and if it is mandatory. The subform is linked to the job description training table that has fields for the training number - from the main form - and the job descriptions that require that training. There can bee mulitple records for one training number as different job descriptions may require that training. On the main form there is a check box (yes/no field) that the user checks to indicate that the job training is mandatory. If the training is manditory it means all job descriptions require that training.

On the subform there is an unbound combo box with a row source is a query that displays all of the job descriptions. This alllows the user to select one of job descrptions and then click a button to addt job description to the job description training table. This updates the job description training table to add training number from the main form and the selected job description on the subform.

So I would like to create a new button that would instead create a record for each of the job descriptions in the database with the training number from the main form (just as the current button does but for all of the descriptions). Here is a modified verison of the code I am using to add one of the job descriptions currently (these are not actually the names in the database but hopefully changed to make it more understandable.)

"INSERT INTO [JobDescriptionTrainingTbl] ([TraiiningName],[JobDescription]) VALUES ('" & ESC(Parent.TrainingNumber) & "', '" & ESC([JobDescriptiom]) & "’)"

Ideally I would like this update to be done automatically when the mandatory selection is made on the main form (and if mandatory is deselected remove all job descriptions) BUT this is above my abilities so I can work with buttons on the sub form that adds all job descriptions or removes all job descriptions.

I hope that makes sense and is enough info for someone to help me get this working. Thanks in advance for any assistance
Sue

If the Subform is already bound to the Job Description Table, why not make the Control source of the Combo Box to the respective Field? Rather than having to write code to perform the update, just place a Me.Refresh in the AfterUpdate event and be done with it!

 

[Design by Sue]

If the Subform is already bound to the Job Description Table, why not make the Control source of the Combo Box to the respective Field? Rather than having to write code to perform the update, just place a Me.Refresh in the AfterUpdate event and be done with it!

I don't understand this at all - sorry but how would this work with out code to update the table with the necessary information?

I think I have successfully uploaded the database as requested. I am really hopeful that someone can hep me with the code I need. I am getting totally confused. Thanks!

 

[Gasman]

Can you please add the relationships yourself and then upload again?
I am not understanding this table?

Same description with differenr SOP ? and repeated SOP ?
Please also test it before uploading. I get this if I click on the unbound combo for job description.

 

[spaLOGICng]

I don't understand this at all - sorry but how would this work with out code to update the table with the necessary information?

I think I have successfully uploaded the database as requested. I am really hopeful that someone can hep me with the code I need. I am getting totally confused. Thanks!

I have looked at your Database and it is difficult to understand what your goal is.

If your [JOD SOP TBL] is to be the source for the Description you do not need Combo boxes. You can have regular Text Boxes bound to the respective Columns to insert Data into the Table.


Aside from that, I just do not know what you trying to accomplish. You have Primary Keys that are Text Fields. You do not have any Relationships defined between Tables, etc. The last thing you should focus on is how the Application should appear (UI), Get the Tables structurally sound first, then work on the UI.

Typically, a Table will have an ID column of a Number Type, usually the Long Integer, that become the Unique Value and Primary key for the Record. That ID is then stored to whatever Child Table may be part of the relationships, not Text Fields.

You can create Unique Indexes on Text Fields to prevent Duplicates.

Enumerator Tables are Lookup Tables. The same concept is used here for a primary key. The ID is stored in the main table. Enumerators are usually represented on the Screen with Combo boxes and List Boxes. CB and LB's can be bound to the field that is sourced to the form. When the Description is selected, the ID is stored to the Record, not the Description, otherwise you end up with a lot of redundant data and wasted space. Numbers have a very low physical footprint that Text does. This is the primary purpose for an RDBMS apart from storing data.

So, after a review of what you have so far, I recommend you take a step back and rethink your tables, and then set up Relationships between your Tables as others have indicated.

You can download any number of Templates in Access for you to learn the basics of Access, as a Database and an Application.

As for me, I never bind a Form or Report to a Table or a Select Statement. I always create Queries and use those as Record Sources. I also use only Queries for Lookups or Enumerators for Row Sources. Very seldom will I ever use a Value List. Why? Because it makes it easier to manage the Application, and renders Queries as re-usable as possible, also reducing the physical footprint of the application. For Lookups, I change the Recordset Type to Snapshot.

Relational Database Design

 

[Design by Sue]

Can you please add the relationships yourself and then upload again?
I am not understanding this table?
View attachment 114077
Same description with differenr SOP ? and repeated SOP ?
Please also test it before uploading. I get this if I click on the unbound combo for job description.
View attachment 114078

I will update and add the relationships tomorrow and upload. The JD SOP TBL is a many to many (hope that is correctly stated:
There are many job descriptions and many SOP (Training courses). All job descriptions can require many SOPs and all SOPs can be required by any number of job descriptions.

I am not sure what the error message means, can you tell me what caused it?

This is only a small part of the database and I am trying to only give the tables that are needed for this coding. I have deleted some records to trim the files down, maybe I deleted a record it is looking for.

I will try to do better in the next upload. I know that what I need to do is possible and probably pretty simple, I just don't know how to write the code to do it. As I have stated before I really appreciate you all sticking with me on this. I apologize that i can't express it clearly enough.

 

[gemma-the-husky]

I will update and add the relationships tomorrow and upload. The JD SOP TBL is a many to many (hope that is correctly stated.

A table isn't many to many. Relationships between tables are many to many, but since we can't represent a many to many relationship in a database, we have to devolve that relationship into two 1 to many relationships.

So if you have employees and training courses, you need an employees table, a courses table, and an employee-courses table.

The employees table is not directly related to the courses table. It's the employee-courses table that provides the relationships.

So one employee can take zero, or any number if courses (1 to many) and courses can be taken by many employees (1 to many).

The employee courses table holds the employee ID and the course ID, and maybe the course date and other information about that particular attendance.

It sounds like you are standardising the course requirements for different jobs. I presume you then give these different jobs to different employees.

So you get employees job-description and employee-jobs. You then join the skills by having the job-descriptions linked to a trading-courses table.

So I can see an issue where you have a product awareness course, say, that is required for multiple jobs, and you only need an employee to take that course once. In that case you would need to redesign your table relationships to help manage that schema, as otherwise there might indirectly be duplication of courses an employee needs to take. Offhand, I'm not sure of the right database structure to achieve the best outcome.

I think we need to understand precisely how your tables relate to one another to guide you, though. Does that all make sense?

So this isn't really "forms" at all. It's tables, because if you get the table structure right, the form design should flow from the table design.