Convert query to SQL

SteveL13
SteveL13 used Ask the Experts™
on
I have an x query that I want to convert to VBA SQL code.  The query is named updqryUpdatePCMDataWarehouseFormToProjectBilledFalse and I'm using it like this:

Private Sub Form_AfterInsert()

    DoCmd.SetWarnings False
        DoCmd.OpenQuery "updqryUpdatePCMDataWarehouseFormToProjectBilledFalse"
    DoCmd.SetWarnings True
    
    End Sub

Open in new window


The query looks like this:

UPDATE tbl_PCMFinalData SET tbl_PCMFinalData.ProjectBilled = False
WHERE (((tbl_PCMFinalData.[Job Number])=[Forms]![frmTimeSheetHeader].[Form]![frmTimeSheetdetail].[Form].[cboJobNumber]));

But I want to replace the updqryUpdatePCMDataWarehouseFormToProjectBilledFalse with a SQL statement in the VBA window.

How do I do that?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
John TsioumprisSoftware & Systems Engineer

Commented:
Dim strSQL as String
strSQL = "UPDATE tbl_PCMFinalData SET tbl_PCMFinalData.ProjectBilled = False
WHERE (((tbl_PCMFinalData.[Job Number])= " & [Forms]![frmTimeSheetHeader].[Form]![frmTimeSheetdetail].[Form].[cboJobNumber] & "));"

DoCmd.OpenQuery strSQL

Open in new window

Author

Commented:
John,
The 3rd line turns red in the VBA window.
John TsioumprisSoftware & Systems Engineer

Commented:
yeap...you need string continuation...

strSQL = "UPDATE tbl_PCMFinalData SET tbl_PCMFinalData.ProjectBilled = False " & _ 
" WHERE (((tbl_PCMFinalData.[Job Number])= " & [Forms]![frmTimeSheetHeader].[Form]![frmTimeSheetdetail].[Form].[cboJobNumber] & "));"

Open in new window

Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
Assuming that JobNumber is numeric, and that the code is going to be fired from some event on frmTimeSheetHeader, I would use
strSQL = "UPDATE tbl_PCMFinalData SET tbl_PCMFinalData.ProjectBilled = 0 " _
       & "WHERE ([Job Number]=" & me.[frmTimeSheetdetail].form.[cboJobNumber]
currentdb.execute strsql, dbfailonerror

Open in new window

If it is a string, use:
strSQL = "UPDATE tbl_PCMFinalData SET tbl_PCMFinalData.ProjectBilled = 0 " _
       & "WHERE ([Job Number]='" & me.[frmTimeSheetdetail].form.[cboJobNumber] & "'"
currentdb.execute strsql, dbfailonerror

Open in new window

Author

Commented:
Dale.  it is a string.  But I'm getting an error:

companyname can't find the field '|1' referred to in your expression.
John TsioumprisSoftware & Systems Engineer

Commented:
Since its a string
strSQL = "UPDATE tbl_PCMFinalData SET tbl_PCMFinalData.ProjectBilled = False " & _
 " WHERE Job Number = '" & Form_frmTimeSheetdetail.CboNumber & "'"

Open in new window

Strange how a CboNumber is string !

Author

Commented:
As it turns out the issue was from a Microsoft security update,  All I had to do was do an Update to my copy of office.

https://docs.microsoft.com/en-us/officeupdates/semi-annual-channel-2019#version-1902-november-22
ste5anSenior Developer

Commented:
e.g.

Option Compare Database
Option Explicit

Private Sub Form_AfterInsert()

  Const SQL_UPDATE As String = _
    "UPDATE tbl_PCMFinalData " & _
    "SET tbl_PCMFinalData.ProjectBilled = False " & _
    "WHERE tbl_PCMFinalData.[Job Number] = CLng([Forms]![frmTimeSheetHeader].[Form]![frmTimeSheetdetail].[Form].[cboJobNumber]);"

  CurrentDbC.Execute SQL_UPDATE, dbFailOnError Or dbSeeChanges

End Sub

Open in new window

Or when the event is on that form:

Option Compare Database
Option Explicit

Private Sub Form_AfterInsert()

  Const SQL_UPDATE As String = _
    "UPDATE tbl_PCMFinalData " & _
    "SET tbl_PCMFinalData.ProjectBilled = False " & _
    "WHERE tbl_PCMFinalData.[Job Number] = {0};"

  CurrentDbC.Execute Replace(SQL_UPDATE, "{0}", CLng(cboJobNumber.Value)), dbFailOnError Or dbSeeChanges

End Sub

Open in new window

With the following in a standard module:

Option Compare Database
Option Explicit

Private m_CurrentDb As DAO.Database

Public Property Get CurrentDbC() As DAO.Database

  If m_CurrentDb Is Nothing Then
    Set m_CurrentDb = CurrentDb
  End If

  Set CurrentDbC = m_CurrentDb

End Property

Open in new window

Owner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010
Commented:
Steve,

One of the reasons not to use the syntax
Docmd.Setwarnings False
DoCmd.OpenQuery "updqryUpdatePCMDataWarehouseFormToProjectBilledFalse"
DoCmd.SetWarnings True

Open in new window

if you had simply used:
currentdb.querydefs("updqryUpdatePCMDataWarehouseFormToProjectBilledFalse").Execute dbfailonerror

Open in new window

you would have received an error message that read:

 "Query 'updqryUpdatePCMDataWarehouseFormToProjectBilledFalse' is corrupt!"

And we could have told you what the problem was.

I strongly recommend that you stop using the previous syntax as it is a recipe for all sorts of other problems.

Dale

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial