Updating table based on main form and combo box on subform

[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!