Link to home
Create AccountLog in
Avatar of SteveL13
SteveL13Flag for United States of America

asked on

Convert query to SQL

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?
Avatar of John Tsioumpris
John Tsioumpris
Flag of Greece image

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

Avatar of SteveL13

ASKER

John,
The 3rd line turns red in the VBA window.
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

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

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

companyname can't find the field '|1' referred to in your expression.
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 !
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
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

ASKER CERTIFIED SOLUTION
Avatar of Dale Fye
Dale Fye
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account