Solved How to pass parameters on a pass through query in ms access

[nector]

I have tried to pass a parameter to a pass through query in access but instead of updating one of the row its updating all rows where did I go wrong?

Private Sub CboTempSigning_AfterUpdate()
Dim db As DAO.Database
Dim strSQL As String
Set db = CurrentDb
strSQL = "SELECT * FROM [tblJobWorks] WHERE [tblJobWorks].[ID] = " & Me.[CboJobComplete]
DoCmd.SetWarnings False
DoCmd.OpenQuery "QryJobWorksCompleted"
db.Close
Set db = Nothing
MsgBox "Your Document has been signed and posted", vbInformation, "Please Proceed"
End Sub

 

[GPGeorge]

You have to change the SQL inside the query with your new string. Do you know how to do that?

 

[GPGeorge]

Note also that your SQL string is a SELECT, not an UPDATE, so it will also have to be changed,

 

[CJ_London]

I have tried to pass a parameter to a pass through query in access

Where is the code that does that?

 

[nector]

Okay sorry people I was locked out of the meeting , below is the code where do I change

Query name : QryJobWorksCompleted

UPDATE tblJobWorks SET tblJobWorks.CompleteJob = '2'

The above query is running okay against access database , the problem with SQL server cloud is where to put the parameters

Here is a full update query in access database vs access database

UPDATE tblJobWorks SET tblJobWorks.CompleteJob = "2"
WHERE (((tblJobWorks.ID)=[Forms]![frmJobWorks]![CboJobComplete]));


Running the above query in VBA

DoCmd.SetWarnings False
DoCmd.OpenQuery "QryJobWorksCompleted"
Beep
MsgBox "This document is now approved cannot be edited", vbOKOnly, "Internal Audit Manager"

 

[cheekybuddha]

So you need to get the query via the QueryDefs collection, then change the SQL, then run it.

Firstly, why is field CompleteJob a text type field? Or is it actially a numeric datatype in table tblJobWorks ?

  With CurrentDb.QueryDefs("QryJobWorksCompleted")
    .SQL = "UPDATE tblJobWorks SET CompleteJob = 2 " & _
           "WHERE ID = " & Forms.frmJobWorks.CboJobComplete & ";"   ' If this code is in form frmJobWorks' module then use Me.CboJobComplete instead
    .ReturnsRecords = False
    .Execute dbFailOnError
  End With
  Beep
  MsgBox "This document is now approved cannot be edited", vbOKOnly, "Internal Audit Manager"

Avoid messing around with DoCmd.SetWarnings (ie use DAO methods instead of DoCmd.OpenQuery etc) - otherwise it will bite you in the rear when you least expect it.
(You didn't set it back on in the code you posted, so you will miss any further errors in you application )

 

[cheekybuddha]

Perhaps a small improvement:

  With CurrentDb
    With .QueryDefs("QryJobWorksCompleted")
      .SQL = "UPDATE tblJobWorks SET CompleteJob = 2 " & _
             "WHERE ID = " & Forms.frmJobWorks.CboJobComplete & ";"   ' If this code is in form frmJobWorks' module then use Me.CboJobComplete instead
      .ReturnsRecords = False
      .Execute dbFailOnError
    End With
    Beep
    If .RecordsAffected > 0 Then
      MsgBox "This document is now approved cannot be edited", vbOKOnly, "Internal Audit Manager"
    Else
      MsgBox "No record was updated", vbOKOnly, "Internal Audit Manager"
    End If
  End With

 

[nector]

Well I tried it I have an error see picture

 

[GPGeorge]

The error message is very clear. Add dbSeeChanges to the execute line when using a SQL Server table.

.Execute dbFailOnError + dbSeeChanges

 

[cheekybuddha]

Is this actually a pass-through query?

Or just a query using linked tables?

 

[sonic8]

Well I tried it I have an error see picture

Are you sure the QryJobWorksCompleted is a Pass-Through-Query? - The error message you show in the screenshot indicates it is not.

 

[nector]

Many thanks

cheekybuddha​

It has worked very

 

[cheekybuddha]

In your screenshot in Post #8 you have declared a variable called Cancel

This variable does nothing.

It seems you might be confusing with a BeforeUpdate event which has a Cancel parameter as part of its signature, and will prevent the update when set to True.

 

[nector]

Kindly do not worry you have already helped me and all is working okay.

Regards

Chris

 

[cheekybuddha]

OK, no worries, Chris.

Please will you take a minute to explain here exactly what you did to solve the problem in this thread.

This will help other users who come to the forum and find themselves with a similar problem

 

[nector]

Many thanks I will explain it this way, a passthrough query cannot work in that environment especially on linked tables, therefore, to solve this problem I reverted the query back to a normal select query

Private Sub CboJobComplete_AfterUpdate()
Dim answer As Integer
Dim Cancel As Integer
answer = MsgBox("This Job Will Close Permanently This Means That You will Not Be Able To Edit It Anymore Do you want to Continue?", vbQuestion + vbYesNo, "Internal Audit Manager")
Beep
If answer = vbYes Then
Cancel = False
 With CurrentDb
    With .QueryDefs("QryJobWorksCompleted")
      .SQL = "UPDATE tblJobWorks SET CompleteJob = 2 " & _
             "WHERE ID = " & Forms.frmJobWorks.CboJobComplete & ";"   ' If this code is in form frmJobWorks' module then use Me.CboJobComplete instead
      .ReturnsRecords = False
      .Execute dbFailOnError
    End With
 
      MsgBox "This document is now approved cannot be edited", vbOKOnly, "Internal Audit Manager"
End With
ElseIf answer = vbNo Then
Beep
Cancel = True
MsgBox "The opperation is now abborted", vbOKOnly, "Internal Audit Manager"
End If
End Sub

On this one I like the speed!!

 

[cheekybuddha]

Even with linked tables you can still use a pass-through query (if necessary!)

Private Sub CboJobComplete_AfterUpdate()

  Dim answer As Integer

  answer = MsgBox("This Job Will Close Permanently This Means That You will Not Be Able To Edit It Anymore Do you want to Continue?", vbQuestion + vbYesNo, "Internal Audit Manager")
  Beep
  If answer = vbYes Then
    With CurrentDb
      With .QueryDefs("QryJobWorksCompleted")
        .Connect = .TableDefs("tblJobWorks").Connect            ' <-- Set up pass-through, using connection string from a linked table
        .SQL = "UPDATE tblJobWorks SET CompleteJob = 2 " & _
               "WHERE ID = " & Me.CboJobComplete & ";"
        .ReturnsRecords = False
        .Execute dbFailOnError
      End With
      MsgBox "This document is now approved cannot be edited", vbOKOnly, "Internal Audit Manager"
    End With
  Else
    Beep
    MsgBox "The opperation is now abborted", vbOKOnly, "Internal Audit Manager"
  End If

End Sub

 

[cheekybuddha]

On this one I like the speed!!

Since you are happy with the speed using normal linked tables, then there is probably no need to use a pass-through.

 

[nector]

Yes for sure