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]

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_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 = " & Me!cboYear & ");"
MsgBox ("Test3")
'create query
Set qdfPassThru = db.CreateQueryD ef("QueryName" )
qdfPassThru.SQL = strSQL
qdfPassThru.Exe cute ' 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_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 & ");"
MsgBox ("Test3")

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

qdfPassThru.SQL = strSQL
qdfPassThru.Exe cute ' 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?