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!

[FishVal]

I'm a bit confused....how does that code fit into mine? Which bits do i need to replace?

• create working pass-through query in Query builder and save it as e.g. [qrySomeQuery]
• then place code like the following in your form module
  

  Private Sub cmdExecuteQuery_Click()
      Dim strSQL As String
   
      strSQL = "UPDATE unit_instance_occurrences uio " & _
               "SET fes_active_places = " & _
               "(SELECT COUNT(*) FROM registration_units ru " & _
               "WHERE ru.fes_unit_instance_code = uio.fes_uins_instance_code " & _
               "AND ru.uio_occurrence_code = uio.calocc_occurrence_code " & _
               "AND ru.progress_status = 'A' " & _
               "AND ru.uio_occurrence_code = " & Me!cboYear & ");"
   
      With CurrentDb.QueryDefs("qrySomeQuery")
          .SQL = strSQL
          .Execute
      End With
  End Sub
   

[Lewe22]

Thanks!

Unfortunately still finding the same error "Operation must use an updateable query". I have just started another thread at....
http://www.thescripts.com/forum/show...22#post2826022

Any help on that would be great!!

[FishVal]

Does the query built in query builder work? If so, post the query SQL.

[Lewe22]

Unfortunately I don't have query builder. Can you suggest somewhere to get it from?

[FishVal]

Unfortunately I don't have query builder. Can you suggest somewhere to get it from?

I've meant query design which is the part of all Access versions.
Sorry if have confused you.

[MMcCarthy]

Is "unit_instance_ occurrences uio" a query or a table?

[Lewe22]

Is "unit_instance_ occurrences uio" a query or a table?

"unit_instance_ occurrences uio" is a table, as is "registration_u nits ru"

[MMcCarthy]

"unit_instance_ occurrences uio" is a table, as is "registration_u nits ru"

I'm trying to remember general SQL syntax. In Access a tablename which included spaces would have to be enclosed with square brackets. Whats the rule in your backend. You don't mention what backend system you are using.

[Lewe22]

My latest code...

Private Sub New_Click()
On Error GoTo err_New_Click
Dim db As DAO.Database
Dim qdfPassThru As DAO.QueryDef
Set db = CurrentDb
CurrentDb.Query Defs("Active Update Query").SQL =
"UPDATE fes_unit_instan ce_occurrences uio " & _
"SET fes_active_plac es = " & _
"(SELECT COUNT(*) FROM fes_registratio n_units 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 = " & Me!cboYear & ");"
CurrentDb.Query Defs("Active Update Query").Updatab le
Set qdfPassThru = db.QueryDefs("A ctive Update Query")
qdfPassThru.Exe cute ' you can leave this out if you don't want to run the query
Set qdfPassThru = Nothing
Set db = Nothing
exit_New_Click:
Exit Sub
err_New_Click:
MsgBox (Err.Descriptio n)
End Sub

"fes_unit_insta nce_occurrences " is the table name "uio" it's alias and
"fes_registrati on_units" is the table name "ru" is it's alias.
Both tables are in my access db as ODBC linked tables to a SQL server.
Also, the combo box "cboYear" is linked to another ODBC table to pick up the year.

[Lewe22]

Sorry, the line...

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

has since been removed.