Solved How to pass parameters on a pass through query in ms access (1 Viewer)

nector

Member
Local time
Today, 07:38
Joined
Jan 21, 2020
Messages
388
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?

Code:
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

George Hepworth
Local time
Yesterday, 21:38
Joined
Nov 25, 2004
Messages
2,042
You have to change the SQL inside the query with your new string. Do you know how to do that?
 

GPGeorge

George Hepworth
Local time
Yesterday, 21:38
Joined
Nov 25, 2004
Messages
2,042
Note also that your SQL string is a SELECT, not an UPDATE, so it will also have to be changed,
 

nector

Member
Local time
Today, 07:38
Joined
Jan 21, 2020
Messages
388
Okay sorry people I was locked out of the meeting , below is the code where do I change

Query name : QryJobWorksCompleted

Code:
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

Code:
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"
 
Last edited:

cheekybuddha

AWF VIP
Local time
Today, 05:38
Joined
Jul 21, 2014
Messages
2,337
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 ?

Code:
  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 :oops: 😖 )
 
Last edited:

cheekybuddha

AWF VIP
Local time
Today, 05:38
Joined
Jul 21, 2014
Messages
2,337
Perhaps a small improvement:
Code:
  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

Member
Local time
Today, 07:38
Joined
Jan 21, 2020
Messages
388
Well I tried it I have an error see picture


JobTwo errors.png



Jobs Errors.png
 

GPGeorge

George Hepworth
Local time
Yesterday, 21:38
Joined
Nov 25, 2004
Messages
2,042
The error message is very clear. Add dbSeeChanges to the execute line when using a SQL Server table.

.Execute dbFailOnError + dbSeeChanges
 

cheekybuddha

AWF VIP
Local time
Today, 05:38
Joined
Jul 21, 2014
Messages
2,337
Is this actually a pass-through query?

Or just a query using linked tables?
 

sonic8

AWF VIP
Local time
Today, 06:38
Joined
Oct 27, 2015
Messages
1,001
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.
 

cheekybuddha

AWF VIP
Local time
Today, 05:38
Joined
Jul 21, 2014
Messages
2,337
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

Member
Local time
Today, 07:38
Joined
Jan 21, 2020
Messages
388
Kindly do not worry you have already helped me and all is working okay.

Regards

Chris
 

cheekybuddha

AWF VIP
Local time
Today, 05:38
Joined
Jul 21, 2014
Messages
2,337
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

Member
Local time
Today, 07:38
Joined
Jan 21, 2020
Messages
388
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

Code:
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

AWF VIP
Local time
Today, 05:38
Joined
Jul 21, 2014
Messages
2,337
Even with linked tables you can still use a pass-through query (if necessary!)

Code:
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
 

Users who are viewing this thread

Top Bottom