Inserting a value from an Access form into a SQL pass through query

[Lewe22]

I am creating a small Access db which performs a series of updates to a SQL database.

The Access db consists of a ‘Main Form’, from which the user can run each update via a series of command buttons.

Each update has been written as a SQL pass-through query.

Example Update:

UPDATE unit_instance_o ccurrences uio
SET fes_active_plac es =
(SELECT COUNT(*) FROM registration_un its ru
WHERE ru.fes_unit_ins tance_code = uio.fes_uins_in stance_code
AND ru.uio_occurren ce_code = uio.calocc_occu rrence_code
AND ru.progress_sta tus = 'A'
AND ru.uio_occurren ce_code = 07);

Each query is dependant on a year (Eg: 07) however I want the user to determine that year from a combo box on the ‘Main Form’.

How do I best go about this?

The key thing is that i want to be able to run my pass through query from a button on the form. Before clicking the button the user must select the year. Somehow I either want to be able to insert that year as a parameter into the body of the PTQ or if necessary I can rewrite the pass-through update queries as pure Access-update queries and insert it there instead.

Have pretty much hit a wall with this so any help would be greatly appreciated!

[MMcCarthy]

Sorry, the line...

"CurrentDb.Quer yDefs("Active Update Query").Updatab le"

has since been removed.

You have set up "Active Update Query" as a pass through query though? It is an action query, yes?

Check the properties of the query.

[Lewe22]

Success at last!!!!!!!

Thank you so much.

Turns out it was as simple an error as the fact the "Active Pass Through" query was initially created as a Access update query. Upon recreating as pass through everything worked fine.

[MMcCarthy]

Success at last!!!!!!!

Thank you so much.

Turns out it was as simple an error as the fact the "Active Pass Through" query was initially created as a Access update query. Upon recreating as pass through everything worked fine.

That's great. Glad you got it working.