asked on Convert query to SQL
I have an x query that I want to convert to VBA SQL code. The query is named updqryUpdatePCMDataWarehou
The query looks like this:
UPDATE tbl_PCMFinalData SET tbl_PCMFinalData.ProjectBi
WHERE (((tbl_PCMFinalData.[Job Number])=[Forms]![frmTimeS
But I want to replace the updqryUpdatePCMDataWarehou
How do I do that?
Private Sub Form_AfterInsert()
DoCmd.SetWarnings False
DoCmd.OpenQuery "updqryUpdatePCMDataWarehouseFormToProjectBilledFalse"
DoCmd.SetWarnings True
End Sub
The query looks like this:
UPDATE tbl_PCMFinalData SET tbl_PCMFinalData.ProjectBi
WHERE (((tbl_PCMFinalData.[Job Number])=[Forms]
Last Comment
Dale Fye
ASKER
John,
The 3rd line turns red in the VBA window.
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] & "));"
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
strSQL = "UPDATE tbl_PCMFinalData SET tbl_PCMFinalData.ProjectBilled = 0 " _
& "WHERE ([Job Number]='" & me.[frmTimeSheetdetail].form.[cboJobNumber] & "'"
currentdb.execute strsql, dbfailonerror
ASKER
Dale. it is a string. But I'm getting an error:
companyname can't find the field '|1' referred to in your expression.
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 & "'"
ASKER
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
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
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
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
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Open in new window