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

[Gasman]

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.

if I click on the unbound combo for job description.

 

[Design by Sue]

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.

 

[gemma-the-husky]

You may be able to what you want by adding a suitable unique key.

In step 1, for instance, instead of checking whether a record already exists simply try to add all the records, and just ignore any records that fail to be added because they are rejected as duplicates. It depends whether you are happy to accept access checking for you, rather than explicitly doing it yourself.

You ought to be able to automate your process to use bulk queries to insert all the necessary records, I would have thought.

 

[Design by Sue]

You may be able to what you want by adding a suitable unique key.

In step 1, for instance, instead of checking whether a record already exists simply try to add all the records, and just ignore any records that fail to be added because they are rejected as duplicates. It depends whether you are happy to accept access checking for you, rather than explicitly doing it yourself.

You ought to be able to automate your process to use bulk queries to insert all the necessary records, I would have thought.

Why would I need a unique key - is that to avoid duplicates? If access automatically removed duplicates that is what I want

 

[gemma-the-husky]

Yes. Rather than you having to see if a key pair has already been inserted, just try to insert it (ie as one of all the keys for a second time, and it will fail with an intereceptible error), which you can safely ignore if it's a duplicate key error.

It's worth having a unique key anyway, as otherwise you might try to insert a duplicate in other places in your database.

 

[Design by Sue]

I know I need to do an append query that will update the JD SOP TBL to add all job description from the Job Description TBL as the Job Description field in that table and for each of these add the SOP number indicated on the main form. I started to create the append table but am stumped. I have the Job Description TBL results listing all of the Job Descriptions but how to I get the number from the form into the query as the Required SOP? I know this is so easy for many of you but I am so confused at this point. Can someone please help?

 

[Design by Sue]

Ok so I am making some headway - I found I need to put the code on the afterupdate. I am sure this is what I need for both the append and the delete (if the mandatory is unchecked to remove the job descriptions/SOP from the JD SOP TBL) So please can someone point get me to where I can do the append code to the JD SOP TBL?

Here' is what I need to write as code and am stumped. Please help

Insert into JD SOP TBL and create a new record for each of the combinations possible:
Where [Job Description] is every record from the JobDescriptionQRY which lists all Job Descriptions
And [ Required SOP] is the same number as on the form in the Number control

The JobDescriptionQRY mentioned in this post is not in the uploaded database but is simply a query of the Job Description TBL and lists all of the job descriptions in the database. I am not sure this query is needed but it was the starts of trying to do a append query that failed!

 

[Design by Sue]

Making headway - the following code seems to work. (this is the sql from the append query)!!!

INSERT INTO [JD SOP TBL] ( [Job Description], [Required SOP] )
SELECT [JD SOP TBL].[Job Description] AS Expr1, [Forms]![SOP FRM]![Number].[value] AS Expr2
FROM [JD SOP TBL]
GROUP BY [JD SOP TBL].[Job Description], [Forms]![SOP FRM]![Number].[value];

 

[Design by Sue]

Thank you all that helped - I got this working!