VB query code

  • Thread starter GuiGuy via AccessMonster.com
  • Start date
G

GuiGuy via AccessMonster.com

Can anyone tell me why the code listed does not run from a comand button when
clicked? I have copied the query code and modified it so as to replace the
name of a field when extracting the query.

Private Sub ExtractForecastPurchases_Click()
Dim dbsCurrent As Database
Dim qryTest As QueryDef
Dim CreateSQL As String

CreateSQL = "SELECT tblHFIMR357PlannPurchasesExtract.[VENDOR], " _
& "tblHFIMR357PlannPurchasesExtract.[NAME], " _
& "tblHFIMR357PlannPurchasesExtract.[PART NUMBER], " _
& "tblHFIMR357PlannPurchasesExtract.
tblHFIMR357Purchases_Description, " _
& "tblHFIMR357PlannPurchasesExtract.AMPR,
tblHFIMR357PlannPurchasesExtract.[ORD-QTY], " _
& "tblHFIMR357PlannPurchasesExtract.ABC,
tblHFIMR357PlannPurchasesExtract.SteelClass, " _
& "tblHFIMR357PlannPurchasesExtract.DFClass,
tblHFIMR357PlannPurchasesExtract.Currency, " _
& "tblHFIMR357PlannPurchasesExtract.BYR, [ComMgrFName] & ""
"" & [commgrLName] AS ComName, tblHFIMR357PlannPurchasesExtract.[M-GP], " _
& "tblHFIMR357PlannPurchasesExtract.
tblMaterialGroup_Description, tblHFIMR357PlannPurchasesExtract.SMAT, IIf(
[MultipleQte]>1,'YES' , '""') AS AVGQte, " _
& "tblHFIMR357PlannPurchasesExtract.Period01 AS " & [MO01] &
"Qty, [Period01]*[SMAT] AS " & [MO01] & "Spend, " _
& "tblHFIMR357PlannPurchasesExtract.Period02 AS " & [Mo02] &
"Qty, [Period02]*[SMAT] AS " & [Mo02] & "Spend, " _
& "tblHFIMR357PlannPurchasesExtract.Period03 AS " & [Mo03] &
"Qty, [Period03]*[SMAT] AS " & [Mo03] & "Spend, " _
& "tblHFIMR357PlannPurchasesExtract.Period04 AS " & [Mo04] &
"Qty, [Period04]*[SMAT] AS " & [Mo04] & "Spend, " _
& "tblHFIMR357PlannPurchasesExtract.Period05 AS " & [Mo05] &
"Qty, [Period05]*[SMAT] AS " & [Mo05] & "Spend, " _
& "tblHFIMR357PlannPurchasesExtract.Period06 AS " & [Mo06] &
"Qty, [Period06]*[SMAT] AS " & [Mo06] & "Spend, " _
& "tblHFIMR357PlannPurchasesExtract.Period07 AS " & [Mo07] &
"Qty, [Period07]*[SMAT] AS " & [Mo07] & "Spend, " _
& "tblHFIMR357PlannPurchasesExtract.Period08 AS " & [Mo08] &
"Qty, [Period08]*[SMAT] AS " & [Mo08] & "Spend, " _
& "tblHFIMR357PlannPurchasesExtract.Period09 AS " & [Mo09] &
"Qty, [Period09]*[SMAT] AS " & [Mo09] & "Spend, " _
& "tblHFIMR357PlannPurchasesExtract.Period10 AS " & [Mo10] &
"Qty, [Period10]*[SMAT] AS " & [Mo10] & "Spend, " _
& "tblHFIMR357PlannPurchasesExtract.Period11 AS " & [Mo11] &
"Qty, [Period11]*[SMAT] AS " & [Mo11] & "Spend, " _
& "tblHFIMR357PlannPurchasesExtract.Period12 AS " & [Mo12] &
"Qty, [Period12]*[SMAT] AS " & [Mo12] & "Spend, " _
& "tblHFIMR357PlannPurchasesExtract.Period13 AS " & [Mo13] &
"Qty, [Period13]*[SMAT] AS " & [Mo13] & "Spend " _
& "FROM tblComMgr RIGHT JOIN tblHFIMR357PlannPurchasesExtract
ON tblComMgr.ComMgrID = tblHFIMR357PlannPurchasesExtract.BYR;"

Set dbsCurrent = CurrentDb
Set qryTest = dbsCurrent.QueryDefs("qryHFIMR357Excel")
qryTest.SQL = CreateSQL

End Sub


Thanks
 
P

Pete D.

When you say doesn't run do you mean nothing happens or that it craps out in
the middle. Have you run it in debug and seen which line it stops on?
GuiGuy via AccessMonster.com said:
Can anyone tell me why the code listed does not run from a comand button
when
clicked? I have copied the query code and modified it so as to replace
the
name of a field when extracting the query.

Private Sub ExtractForecastPurchases_Click()
Dim dbsCurrent As Database
Dim qryTest As QueryDef
Dim CreateSQL As String

CreateSQL = "SELECT tblHFIMR357PlannPurchasesExtract.[VENDOR], " _
& "tblHFIMR357PlannPurchasesExtract.[NAME], " _
& "tblHFIMR357PlannPurchasesExtract.[PART NUMBER], " _
& "tblHFIMR357PlannPurchasesExtract.
tblHFIMR357Purchases_Description, " _
& "tblHFIMR357PlannPurchasesExtract.AMPR,
tblHFIMR357PlannPurchasesExtract.[ORD-QTY], " _
& "tblHFIMR357PlannPurchasesExtract.ABC,
tblHFIMR357PlannPurchasesExtract.SteelClass, " _
& "tblHFIMR357PlannPurchasesExtract.DFClass,
tblHFIMR357PlannPurchasesExtract.Currency, " _
& "tblHFIMR357PlannPurchasesExtract.BYR, [ComMgrFName] & ""
"" & [commgrLName] AS ComName, tblHFIMR357PlannPurchasesExtract.[M-GP], "
_
& "tblHFIMR357PlannPurchasesExtract.
tblMaterialGroup_Description, tblHFIMR357PlannPurchasesExtract.SMAT, IIf(
[MultipleQte]>1,'YES' , '""') AS AVGQte, " _
& "tblHFIMR357PlannPurchasesExtract.Period01 AS " & [MO01]
&
"Qty, [Period01]*[SMAT] AS " & [MO01] & "Spend, " _
& "tblHFIMR357PlannPurchasesExtract.Period02 AS " & [Mo02]
&
"Qty, [Period02]*[SMAT] AS " & [Mo02] & "Spend, " _
& "tblHFIMR357PlannPurchasesExtract.Period03 AS " & [Mo03]
&
"Qty, [Period03]*[SMAT] AS " & [Mo03] & "Spend, " _
& "tblHFIMR357PlannPurchasesExtract.Period04 AS " & [Mo04]
&
"Qty, [Period04]*[SMAT] AS " & [Mo04] & "Spend, " _
& "tblHFIMR357PlannPurchasesExtract.Period05 AS " & [Mo05]
&
"Qty, [Period05]*[SMAT] AS " & [Mo05] & "Spend, " _
& "tblHFIMR357PlannPurchasesExtract.Period06 AS " & [Mo06]
&
"Qty, [Period06]*[SMAT] AS " & [Mo06] & "Spend, " _
& "tblHFIMR357PlannPurchasesExtract.Period07 AS " & [Mo07]
&
"Qty, [Period07]*[SMAT] AS " & [Mo07] & "Spend, " _
& "tblHFIMR357PlannPurchasesExtract.Period08 AS " & [Mo08]
&
"Qty, [Period08]*[SMAT] AS " & [Mo08] & "Spend, " _
& "tblHFIMR357PlannPurchasesExtract.Period09 AS " & [Mo09]
&
"Qty, [Period09]*[SMAT] AS " & [Mo09] & "Spend, " _
& "tblHFIMR357PlannPurchasesExtract.Period10 AS " & [Mo10]
&
"Qty, [Period10]*[SMAT] AS " & [Mo10] & "Spend, " _
& "tblHFIMR357PlannPurchasesExtract.Period11 AS " & [Mo11]
&
"Qty, [Period11]*[SMAT] AS " & [Mo11] & "Spend, " _
& "tblHFIMR357PlannPurchasesExtract.Period12 AS " & [Mo12]
&
"Qty, [Period12]*[SMAT] AS " & [Mo12] & "Spend, " _
& "tblHFIMR357PlannPurchasesExtract.Period13 AS " & [Mo13]
&
"Qty, [Period13]*[SMAT] AS " & [Mo13] & "Spend " _
& "FROM tblComMgr RIGHT JOIN
tblHFIMR357PlannPurchasesExtract
ON tblComMgr.ComMgrID = tblHFIMR357PlannPurchasesExtract.BYR;"

Set dbsCurrent = CurrentDb
Set qryTest = dbsCurrent.QueryDefs("qryHFIMR357Excel")
qryTest.SQL = CreateSQL

End Sub


Thanks
Click to expand...
 
G

GuiGuy via AccessMonster.com

When the error comes up it asks if I want to debug or end. When I debug, the
code states is highlighted in yellow and there is a yellow arrow to the left
on the last line of this code.

tHANKS
 
G

GuiGuy via AccessMonster.com

When the error comes up it asks if I want to debug or end. When I debug, the
code states is highlighted in yellow and there is a yellow arrow to the left
on the last line of this code.

Thanks
 
G

GuiGuy via AccessMonster.com

Let me clarify. The yellow arrow is on the line with the FROM statement.
 
P

Pete D.

Hi,
I found some errors and corrected them which was causing it to fall through
to the last line. I have realigned it for easier reading and need you to
compare to your tables now for incorrect field/table names. Watch for line
wrapping after pasting back into the VBA window.

Option Compare Database
Option Explicit

Private Sub ExtractForecastPurchases_Click()
Dim dbsCurrent As Database
Dim qryTest As QueryDef
Dim CreateSQL01 As String
Dim CreateSQL02 As String
Dim CreateSQL03 As String
Dim CreateSQL04 As String
Dim CreateSQL_All As String

CreateSQL01 = "SELECT tblHFIMR357PlannPurchasesExtract.[VENDOR], " _
& "tblHFIMR357PlannPurchasesExtract.[NAME], " _
& "tblHFIMR357PlannPurchasesExtract.[PART NUMBER], " _
& "tblHFIMR357PlannPurchasesExtract." _
& "tblHFIMR357Purchases_Description , """ _
& "tblHFIMR357PlannPurchasesExtract.AMPR," _
& "tblHFIMR357PlannPurchasesExtract.[ORD-QTY] , """ _
& "tblHFIMR357PlannPurchasesExtract.ABC,"
CreateSQL02 = "tblHFIMR357PlannPurchasesExtract.SteelClass , """ _
& "tblHFIMR357PlannPurchasesExtract.DFClass," _
& "tblHFIMR357PlannPurchasesExtract.Currency , """ _
& "tblHFIMR357PlannPurchasesExtract.BYR, [ComMgrFName] &
"""" & [commgrLName] AS ComName," _
& "tblHFIMR357PlannPurchasesExtract.[M-GP], " _
& "tblHFIMR357PlannPurchasesExtract.
tblMaterialGroup_Description," _
& "tblHFIMR357PlannPurchasesExtract.SMAT, IIf(
[MultipleQte]1,'YES' , '""') AS AVGQte, "
CreateSQL03 = "tblHFIMR357PlannPurchasesExtract.Period01 AS " & [MO01] &
"Qty, [Period01]*[SMAT] AS " & [MO01] & "Spend, " _
& "tblHFIMR357PlannPurchasesExtract.Period02 AS " & [Mo02] &
"Qty, [Period02]*[SMAT] AS " & [Mo02] & "Spend, " _
& "tblHFIMR357PlannPurchasesExtract.Period03 AS " & [Mo03] &
"Qty, [Period03]*[SMAT] AS " & [Mo03] & "Spend, " _
& "tblHFIMR357PlannPurchasesExtract.Period04 AS " & [Mo04] &
"Qty, [Period04]*[SMAT] AS " & [Mo04] & "Spend, " _
& "tblHFIMR357PlannPurchasesExtract.Period05 AS " & [Mo05] &
"Qty, [Period05]*[SMAT] AS " & [Mo05] & "Spend, " _
& "tblHFIMR357PlannPurchasesExtract.Period06 AS " & [Mo06] &
"Qty, [Period06]*[SMAT] AS " & [Mo06] & "Spend, " _
& "tblHFIMR357PlannPurchasesExtract.Period07 AS " & [Mo07] &
"Qty, [Period07]*[SMAT] AS " & [Mo07] & "Spend, " _
& "tblHFIMR357PlannPurchasesExtract.Period08 AS " & [Mo08] &
"Qty, [Period08]*[SMAT] AS " & [Mo08] & "Spend, " _
& "tblHFIMR357PlannPurchasesExtract.Period09 AS " & [Mo09] &
"Qty, [Period09]*[SMAT] AS " & [Mo09] & "Spend, " _
& "tblHFIMR357PlannPurchasesExtract.Period10 AS " & [Mo10] &
"Qty, [Period10]*[SMAT] AS " & [Mo10] & "Spend, " _
& "tblHFIMR357PlannPurchasesExtract.Period11 AS " & [Mo11] &
"Qty, [Period11]*[SMAT] AS " & [Mo11] & "Spend, " _
& "tblHFIMR357PlannPurchasesExtract.Period12 AS " & [Mo12] &
"Qty, [Period12]*[SMAT] AS " & [Mo12] & "Spend, " _
& "tblHFIMR357PlannPurchasesExtract.Period13 AS " & [Mo13] &
"Qty, [Period13]*[SMAT] AS " & [Mo13] & "Spend "
CreateSQL04 = "FROM tblComMgr RIGHT JOIN
tblHFIMR357PlannPurchasesExtract ON tblComMgr.ComMgrID =
tblHFIMR357PlannPurchasesExtract.BYR;"
CreateSQL_All = CreateSQL01 & CreateSQL02 & CreateSQL03 & CreateSQL04
Set dbsCurrent = CurrentDb
Set qryTest = dbsCurrent.QueryDefs("qryHFIMR357Excel")
qryTest.SQL = CreateSQL_All

End Sub
 
Top