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_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 = 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]

The best way to do this is to create the query dynamically in VBA. For the code to work the query will already have to be created.

For the purposes of this code I have put it on a command button called cmdExecuteQuery and I've given the combo box the name cboYear as I don't know it.

Private Sub cmdExecuteQuery()
Dim db As DAO.database
Dim qdfPassThru As DAO.QueryDef
Dim strSQL As String
 
    Set db = CurrentDb
    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 & ");"
 
    'create query
    Set qdfPassThru = db.CreateQueryDef("QueryName")
    qdfPassThru.SQL = strSQL
    qdfPassThru.Execute ' you can leave this out if you don't want to run the query
 
    Set qdfPassThru = Nothing
    Set db = Nothing
 
End Sub
 

[Lewe22]

Thanks for your reply!
Have copied the code in exactly and renamed my button and combo box accordingly but am still having a problem. Nothing is happening at all when i click the button. By placing a series of messsage boxes in the code i have narrowed it down to the fact that my VB doesn't seem to understand the first 2 commands.

Dim db As DAO.database
Dim qdfPassThru As DAO.QueryDef

Even putting a message box infront of those two lines still nothing happens.
When i cut those two lines out i see the message boxes.

Any ideas???

Private Sub cmdExecuteQuery_Click()
MsgBox ("Test1")
Dim db As DAO.database
Dim qdfPassThru As DAO.QueryDef
Dim strSQL As String
MsgBox ("Test2")
Set db = CurrentDb
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 & ");"
MsgBox ("Test3")
'create query
Set qdfPassThru = db.CreateQueryDef("QueryName")
qdfPassThru.SQL = strSQL
qdfPassThru.Execute ' you can leave this out if you don't want to run the query
MsgBox ("Test4")
Set qdfPassThru = Nothing
Set db = Nothing
MsgBox ("Test5")
End Sub

[MMcCarthy]

Thanks for your reply!
Have copied the code in exactly and renamed my button and combo box accordingly but am still having a problem. Have narrowed it down to the fact that....

Dim db As DAO.database
Dim qdfPassThru As DAO.QueryDef

doesn't seem to be recognised in my visual basic. Any ideas?????

In the VBA editor, go to Tools - References and in the list of ticked reference libraries find the one called Microsoft DAO X.X Object Library and tick it.

[FishVal]

Hi, there.

Check whether your button name is [cmdExecuteQuery]
Check whether your button "OnClick" property is still properly set.

• Go to form design view.
• Open the button properties window.
• Check whether "OnClick" is set to [Event Procedure]
• Click on [...] button rightward to get to cmdExecuteQuery_Click sub.

P.S. Never mind. Haven't read your last post thoroughly. The problem definitely seems to be in references.

[Lewe22]

Thanks for both your help!

At the risk of sounding stupid.........It is now falling over after MsgBox3

Private Sub cmdExecuteQuery_Click()
MsgBox ("Test1")

Dim db As DAO.database
Dim qdfPassThru As DAO.QueryDef
Dim strSQL As String
MsgBox ("Test2")

Set db = CurrentDb
strSQL = "UPDATE fes_unit_instance_occurrences uio " & _
"SET fes_active_places = " & _
"(SELECT COUNT(*) FROM fes_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 & ");"
MsgBox ("Test3")

'create query
Set qdfPassThru = db.CreateQueryDef("Test")
MsgBox ("Test4")

qdfPassThru.SQL = strSQL
qdfPassThru.Execute ' you can leave this out if you don't want to run the query
MsgBox ("Test5")

Set qdfPassThru = Nothing
Set db = Nothing
MsgBox ("Test6")

End Sub

I can see that the update query "Test" is being created correcly but it does not run.
When trying to run "Test" independently i get the error message "Operation must use an updateable query"

Hope you can help again!!

[FishVal]

Hi, there.

At the risk of sounding stupid......... Did you delete the query before run the code 2nd time?

[Lewe22]

I have tried that, it just doesn't seem to get any further than trying to execute the update query...

[FishVal]

I have tried that, it just doesn't seem to get any further than trying to execute the update query...

I suggest you to create working pass-through query in query builder.
Then in code just change SQL expression.

CurrentDb.QueryDefs("qrySomeQuery").SQL="......."
 

[Lewe22]

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

[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_units ru"

[MMcCarthy]

"unit_instance_occurrences uio" is a table, as is "registration_units 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.QueryDefs("Active Update Query").SQL =
"UPDATE fes_unit_instance_occurrences uio " & _
"SET fes_active_places = " & _
"(SELECT COUNT(*) FROM fes_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 & ");"
CurrentDb.QueryDefs("Active Update Query").Updatable
Set qdfPassThru = db.QueryDefs("Active Update Query")
qdfPassThru.Execute ' 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.Description)
End Sub

"fes_unit_instance_occurrences" is the table name "uio" it's alias and
"fes_registration_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.QueryDefs("Active Update Query").Updatable"

has since been removed.

[MMcCarthy]

Sorry, the line...

"CurrentDb.QueryDefs("Active Update Query").Updatable"

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.