Problem w/SQL Statement in VBA Module

[John D]

I have a Command Button on a form. When it is clicked I want the OnClick
Event Module to determine which of 2 Financial Balance Sheet (BS) Reports to
Preview on Screen, and then print them to screen. The 2 Reports are
"RXR_1DA1_BS_1_MasterColumn" and "RXR_1EA1_BS_3_MasterColumns". (Some
Organizations have only 1 type of function, in which case their BS requires
only 1 "master column". Others have 2 types of functions, and their BS must
be shown as 3 "master columns" - 1 for each type of function and then the
consolidated report.)

The OnClick Code:
______________________________________________________
Private Sub cmdbrnViewBS_Click()

On Error GoTo HandleError

Dim stDocName As String

Dim stViewBS As String

stViewBS = "SELECT DISTINCT TAaaOrg.[BalSheetFormat:]" & _
"FROM TAaaOrg" & _
"WHERE (((TAaaOrg.OrgID)=[Forms]![FA1_OrgMaster_All]![cboOrgs]))"

DoCmd.RunSQL stViewBS

If [BalSheetFormat:] = -1 Then

stDocName = "RXR_1DA1_BS_1_MasterColumn"
DoCmd.OpenReport stDocName, acPreview

Else

stDocName = "RXR_1EA1_BS_3_MasterColumns"
DoCmd.OpenReport stDocName, acPreview

End If

Exit_cmdbrnViewBS_Click:
Exit Sub

HandleError:
(---- this is my generic Error Handler that produces the message below----)

Exit Sub

End Sub

_________________________________________________________

The code executes to the "DoCmd.RunSQL stViewBS" line, then jumps to the
Error Handler. The Error Message generated is:
_______________________________________________
"An error has occured. Please report this info:
Error Number: 3131
Description: Syntax error in FROM clause.
Module: RXR_1EA1_BS_3_MasterColumns
Procedure: cmdbrnViewBS_Click"
_______________________________________________

Help is greatly appreciated. Thanks - John D

PS - the code execution doesn't get to the If ... Then ... Else lines, so I
don't know if they are going to work either. If you see something I need to
do there, double thanks. JD

 

[Dirk Goldgar]

I have a Command Button on a form. When it is clicked I want the OnClick
Event Module to determine which of 2 Financial Balance Sheet (BS) Reports
to
Preview on Screen, and then print them to screen. The 2 Reports are
"RXR_1DA1_BS_1_MasterColumn" and "RXR_1EA1_BS_3_MasterColumns". (Some
Organizations have only 1 type of function, in which case their BS
requires
only 1 "master column". Others have 2 types of functions, and their BS
must
be shown as 3 "master columns" - 1 for each type of function and then the
consolidated report.)

The OnClick Code:
______________________________________________________
Private Sub cmdbrnViewBS_Click()

On Error GoTo HandleError

Dim stDocName As String

Dim stViewBS As String

stViewBS = "SELECT DISTINCT TAaaOrg.[BalSheetFormat:]" & _
"FROM TAaaOrg" & _
"WHERE (((TAaaOrg.OrgID)=[Forms]![FA1_OrgMaster_All]![cboOrgs]))"

DoCmd.RunSQL stViewBS

If [BalSheetFormat:] = -1 Then

stDocName = "RXR_1DA1_BS_1_MasterColumn"
DoCmd.OpenReport stDocName, acPreview

Else

stDocName = "RXR_1EA1_BS_3_MasterColumns"
DoCmd.OpenReport stDocName, acPreview

End If

Exit_cmdbrnViewBS_Click:
Exit Sub

HandleError:
(---- this is my generic Error Handler that produces the message
below----)

Exit Sub

End Sub

_________________________________________________________

The code executes to the "DoCmd.RunSQL stViewBS" line, then jumps to the
Error Handler. The Error Message generated is:
_______________________________________________
"An error has occured. Please report this info:
Error Number: 3131
Description: Syntax error in FROM clause.
Module: RXR_1EA1_BS_3_MasterColumns
Procedure: cmdbrnViewBS_Click"
_______________________________________________

Help is greatly appreciated. Thanks - John D

PS - the code execution doesn't get to the If ... Then ... Else lines, so
I
don't know if they are going to work either. If you see something I need
to
do there, double thanks. JD

There are a couple of things wrong there. You can't just RunSQL a SELECT
query; you would have to open a recordset on it. And your SQL statement is
wrong because you don't have a space after the table name, so you aren't
even getting to the point where it tells you you can't do that.

In this case, you might be better served by using the DLookup function
instead of opening a recordset. Something like this:

If DLookup("[BalSheetFormat:]", "TAaaOrg", _
"OrgID=[Forms]![FA1_OrgMaster_All]![cboOrgs]") _
= -1 _
Then
stDocName = "RXR_1DA1_BS_1_MasterColumn"
Else
stDocName = "RXR_1EA1_BS_3_MasterColumns"
End If

DoCmd.OpenReport stDocName, acPreview

 

[John D]

Thanks Dirk - works like a Champ. (I need to learn that DLookup stuff.)

John D