Can anybody help me combine the f

[GodofSmallThings]

Can anybody help me combine the following into one
SQL statement:

UPDATE qryDepts RIGHT JOIN qryTraining ON qryDepts.TrainingID = qryTraining.TrainingID SET qryTraining.[Select] = Yes
WHERE (((qryDepts.Department) Is Not Null));

And....

SELECT qryTraining.[Select], qryTraining.DocumentNo, qryTraining.Name
FROM qryDepts RIGHT JOIN qryTraining ON qryDepts.TrainingID = qryTraining.TrainingID;

...I'm trying to automate a manual process.

Any ideas, or help would be appreciated.

Thanks

GodofSmallThings

 

[Schof]

From what you have written I think this is the structure of the two tables...

qryDepts
=========
[Select]
TrainingID
Department

qryTraining
============
TrainingID
DocumentNo
Name


where each department can have many training documents(?). What are the primary and foreign keys of each table? Lastly, can you describe what you want your query to do...

 

[GodofSmallThings]

Hello again,

I have 3 tables. Department, Requirements, and Training.
(The Requirements table joins the Department and Training)
I created a form that has a combobox containing Departments
that shows relevant Training titles in a subform.

I would like to create a cmd button to open a popup form
to show all of the Training titles. The Popup will serve
as a way to add and delete Training titles from the
Departments.

...What follows is my Popup form:

I have a select query based on the Training table. The
training table contains a yes/no field as a selector.

...I created a form based on the qryTable.

How do I capture selected check marks and then update
the tables so that when I switch back to the main form
the changes reflect.

I hope my explanations was simple enough. Your help would
greatly be appreciated! Thanks...

GodofSmallThings

 

[Schof]

I am assuming you have a checkbox control on the popup form whose control source is [Select]. You can then just add the following code behind the control to update the appropriate record...

Private Sub myCheckbox_AfterUpdate()
On Error GoTo Err_myCheckbox_AfterUpdate

DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE Training SET [SELECT] = " & Me.myCheckbox & " WHERE ..." ' whatever your where criteria is
DoCmd.SetWarnings True

Exit_myCheckbox_AfterUpdate:
Exit Sub

Err_myCheckbox_AfterUpdate:
msgBox err.Description
Resume Exit_myCheckbox_AfterUpdate

End Sub

Then just requery your subform when closing the popup form to reflect the changes.