Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Scripting in VBA 2

Status
Not open for further replies.
jalenben1

jalenben1

Programmer
Jul 22, 2008
154
US
I have a form in Access and I am having a hard time with a VBA script. What I am trying to do is click on a button I created called Generate ECM report. I want to be able to type in a beginning date and a ending date to generate this ECM report. The script I have below is:

Private Sub Generate_ECM_Report_Click()
On Error GoTo Err_Generate_ECM_Report_Click

Dim stDocName As String
Dim stWhere As String

If Not IsDate(Me.txtBeginning) And IsDate(Me.txtEnding) Then
stWhere = "[DateCreated] <= " & SQLDate(Me.txtEnding)
ElseIf IsDate(Me.txtBeginning) And Not IsDate(Me.txtEnding) Then
stWhere = "[DateCreated] >= " & SQLDate(Me.txtBeginning)
ElseIf IsDate(Me.txtBeginning) And IsDate(Me.txtEnding) Then
stWhere = "[DateCreated] Between " & SQLDate(Me.txtBeginning) & "And" & SQLDate(Me.txtEnding)
End If
'MsgBox stWhere
Debug.Print "Beginning: " & Me.txtBeginning & "Ending: " & Me.txtEnding & " StWhere: " & stWhere
DoCmd.OpenReport stDocName, acViewPreview, , stWhere
Exit_Generate_ECM_Report_Click:
Exit Sub

Err_Generate_ECM_Report_Click:
MsgBox Err.Description



End Sub

When I click on the button Generate ECM report I get the following error message:

The action or method requires a Report name argument. Can you tell me ehat that means and can you help me with this script in VBA?
 
Sort by date Sort by votes
I guess I should add that my data source is coming from a ODBC query that I created in Access by way of a Pass Through Query. The end result is a report that I created in report. The ODBC query is called qryECMReportQuery and the report in the Access Reports is called Enterprise Change Management Report. In the form I have text box for the beginning date to be typed in and I have a ending data for a date to type in..for example Beginning Date: 1/1/2011
Ending Date : 1/31/2011

When I type inthe dates and hit the command button called ECM Generate Report I would likefor the report Enterprise Change Management Report to pop up that will include the data from the dates 1/1/2011 - 1/31/2011 If I want to see the entire report without putting a data in it then I click on the command button to see the report as well
 
Upvote 0 Downvote
I would use code to change the SQL property of your pass-through query like:
Code: 
Private Sub Generate_ECM_Report_Click()
On Error GoTo Err_Generate_ECM_Report_Click    
    Dim stDocName As String
    stDocName = "Enterprise Change Management Report"
    Dim stWhere As String
    Dim strSQL as String
    Dim strQueryName as String
    strQueryName ="qryECMReportQuery"
    strWhere = "1=1 "
    If IsDate(Me.txtEnding) Then
	strWhere = strWhere & " AND DateCreated  <= " & SQLDate(Me.txtEnding)
    End If
    If IsDate(Me.txtBeginning) Then
        stWhere = strWhere & " AND DateCreated >= " & SQLDate(Me.txtBeginning)
    End If
    strSQL = "SELECT ... FROM ... WHERE " & strWhere 
   'MsgBox stWhere
    Debug.Print "Beginning: " & Me.txtBeginning & "Ending: " & Me.txtEnding & " StWhere: " & stWhere
    Currentdb.QueryDefs(strQueryName).SQL = strSQL
    DoCmd.OpenReport stDocName, acViewPreview
Exit_Generate_ECM_Report_Click:
    Exit Sub
Err_Generate_ECM_Report_Click:
    MsgBox Err.Description
End Sub
Make sure the strSQL is legitimate for your database.

Duane
Hook'D on Access
MS Access MVP
 
Upvote 0 Downvote
dhookum:

I used your code above and got an error message that the ODBC called failed then I remembered to make sure I am actually using the pass through query that is used for the report it self. That is called qryECM Query. I made that change in the code where it says strQueryName ="qryECMReportQuery". After I made that change and then clicked on the Generate the ECM report command button I got the error message "Syntax error (missing operator) in query expression '....'. I think I am close but not sure where the error is.

 
Upvote 0 Downvote
not sure where the error is
Here (in red):
"Syntax error (missing operator) in query expression [!]'....'[/!].

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
jalenben1,
I assumed you would provide your own pass-through statement at
Code: 
    strSQL = "SELECT [red]...[/red] FROM [red]...[/red] WHERE " & strWhere
Had you provided your P-T sql statement, code for SQLDate, and type of database someone might have been able to suggest a more complete SQL statement.

Duane
Hook'D on Access
MS Access MVP
 
Upvote 0 Downvote
I am sorry if I am not being clear enough....

I rewrote the code and have the following:

Private Sub Generate_ECM_Report_Click()
On Error GoTo Err_Generate_ECM_Report_Click

Dim stDocName As String
stDocName = "Enterprise Change Management Report"
Dim stWhere As String
Dim strSQL As String
Dim strQueryName As String
strQueryName = "qryECM Query"
strWhere = "1=1 "

If Not IsDate(Me.txtBeginning) And IsDate(Me.txtEnding) Then stWhere = "[DateCreated] <= " & SQLDate(Me.txtEnding)

If IsDate(Me.txtBeginning) And Not IsDate(Me.txtEnding) Then stWhere = "[DateCreated] >= " & SQLDate(Me.txtBeginning)

If IsDate(Me.txtBeginning) And IsDate(Me.txtEnding) Then stWhere = "[DateCreated] Between" & SQLDate(Me.txtBeginning) & "And" & SQLDate(Me.txtEnding)

End If

'MsgBox stWhere
Debug.Print "Beginning: " & Me.txtBeginning & "Ending: " & Me.txtEnding & " stWhere: " & stWhere
DoCmd.OpenReport stDocName, acViewPreview, , stWhere
Exit_Generate_ECM_Report_Click:
Exit Sub

Err_Generate_ECM_Report_Click:
MsgBox Err.Description



End Sub

I now got the error message Compile error: End If without block If

Again I have a form where I created a text box for Beginning Date and Ending Date where I would like for the end users to put a date for the beginning dtae and put a date for the ending date then click on the the Generate ECM command button to out the report with those date or The end user could not put in dates and generate the entire reports witl all of the dates.
 
Upvote 0 Downvote
Why not use the code as I suggested? If you don't understand something, please ask for clarification.

I never use and IF and THEN on the same line when coding.

Do you have any reply to my statement
dhookom said:
Had you provided your P-T sql statement, code for SQLDate, and type of database someone might have been able to suggest a more complete SQL statement.
Click to expand...

Duane
Hook'D on Access
MS Access MVP
 
Upvote 0 Downvote
Sorry dhookum...wasnt pay attention to details of your last response. Here is the PT Query for the ECM report:

SELECT ECM_Change.ChangeID, ECM_Change.DateCreated, ECM_Change.ScheduledStartDate, ECM_Change.ScheduledEndDate, ECM_Change.ScheduledDuration, ECM_Change.Category, ECM_Change.Item, ECM_Change."Change-Mgmt-Approval-", ECM_Change.Region, ECM_Change.Status, ECM_Change.ClosureCode, ECM_Change.Classification, ECM_Change.Description, ECM_Change."Device-Alias+", ECM_Change.AssignedGroup, ECM_Change.AssignedToFullName
FROM ECM_Change ECM_Change
WHERE (ECM_Change.AssignedGroup='CORP - Huawei Tier II') AND (ECM_Change.DateCreated>={ts '2011-01-01 00:00:00'})
ORDER BY ECM_Change.DateCreated DESC
 
Upvote 0 Downvote
Sorry dhookom:

Here is the P-T SQL statement

SELECT ECM_Change.ChangeID, ECM_Change.DateCreated, ECM_Change.ScheduledStartDate, ECM_Change.ScheduledEndDate, ECM_Change.ScheduledDuration, ECM_Change.Category, ECM_Change.Item, ECM_Change."Change-Mgmt-Approval-", ECM_Change.Region, ECM_Change.Status, ECM_Change.ClosureCode, ECM_Change.Classification, ECM_Change.Description, ECM_Change."Device-Alias+", ECM_Change.AssignedGroup, ECM_Change.AssignedToFullName
FROM ECM_Change ECM_Change
WHERE (ECM_Change.AssignedGroup='CORP - Huawei Tier II') AND (ECM_Change.DateCreated>={ts '2011-01-01 00:00:00'})
ORDER BY ECM_Change.DateCreated DESC
 
Upvote 0 Downvote
Try this code:
Code: 
Private Sub Generate_ECM_Report_Click()
On Error GoTo Err_Generate_ECM_Report_Click    
    Dim stDocName As String
    stDocName = "Enterprise Change Management Report"
    Dim stWhere As String
    Dim strSQL as String
    Dim strQueryName as String
    strQueryName ="qryECM Query"
    strWhere = "1=1 "
    If IsDate(Me.txtEnding) Then
        [green]'SQLDate() should create the proper format for the pass-through[/green]
        strWhere = strWhere & " AND DateCreated  <= " & SQLDate(Me.txtEnding)
    End If
    If IsDate(Me.txtBeginning) Then
        stWhere = strWhere & " AND DateCreated >= " & SQLDate(Me.txtBeginning)
    End If
    '[green]build a sql statement[/green]
    strSQL = "SELECT ChangeID, DateCreated, ScheduledStartDate, " & _
        "ScheduledEndDate, ScheduledDuration, Category, Item, " & _
        """Change-Mgmt-Approval-"", Region, Status, ClosureCode, " & _
        "Classification, Description, ""Device-Alias+"", " & _
        "AssignedGroup, AssignedToFullName "
    strSQL = strSQL & "FROM ECM_Change " & _
        "WHERE AssignedGroup='CORP - Huawei Tier II' AND " & strWere & _
        " ORDER BY ECM_Change.DateCreated DESC "
    [green]'print the SQL statement for troubleshooting[/green]
    Debug.Print strSQL
    [green]'change the SQL property of the pass-through[/green]
    Currentdb.QueryDefs(strQueryName).SQL = strSQL
    DoCmd.OpenReport stDocName, acViewPreview

Exit_Generate_ECM_Report_Click:
    Exit Sub
Err_Generate_ECM_Report_Click:
    MsgBox Err.Description
End Sub
If this doesn't work as expected, come back with the result of the debug.print. You can also attempt to open [qryECM Query] so trouble-shoot.

Duane
Hook'D on Access
MS Access MVP
 
Upvote 0 Downvote
dhookom:

I now get this error message:

Syntax error (missing operator) in query expression 'AssignedGroup='CORP - Huawei Tier' AND'.
 
Upvote 0 Downvote
Please respond to my request "come back with the result of the debug.print"

Did you even try my suggestion "You can also attempt to open [qryECM Query] so trouble-shoot"?

Looking at my suggested code
Code: 
 "WHERE AssignedGroup='CORP - Huawei Tier II' AND " & strWere & _
should be
Code: 
 "WHERE AssignedGroup='CORP - Huawei Tier II' AND " & [red]strWhere[/red] & _


Duane
Hook'D on Access
MS Access MVP
 
Upvote 0 Downvote
I changed the code to "WHERE AssignedGroup='CORP - Huawei Tier II' AND " & strWhere & _ and got this error message:

Syntax error (missing operator) in query expression 'AssignedGroup='CORP - Huawei Tier' AND' 1=1 AND DateCreated <=.

For your suggestion I am not understanding what you mean "You can also attempt to open [qryECM Query] so trouble-shoot"? Are you saying to open that query and troubleshoot the SQL statement? I am able to run the query without any problems
 
Upvote 0 Downvote
Provide your entire code. You should not have the single quote in red from the code I provided:
Code: 
'AssignedGroup='CORP - Huawei Tier' AND[b][red]'[/red][/b] 1=1 AND DateCreated <=.

After running this, open the debug window (press Ctrl+G) and post the SQL statement into a reply.

You could also post the SQL statement from the query.

Duane
Hook'D on Access
MS Access MVP
 
Upvote 0 Downvote
I pressed Ctrl + G and here is the code:


SELECT ChangeID, DateCreated, ScheduledStartDate, ScheduledEndDate, ScheduledDuration, Category, Item, "Change-Mgmt-Approval-", Region, Status, ClosureCode, Classification, Description, "Device-Alias+", AssignedGroup, AssignedToFullName FROM ECM_Change WHERE AssignedGroup = 'CORP - Huawei Tier II' AND 1=1 ORDER BY ECM_Change.DateCreated DESC
SELECT ChangeID, DateCreated, ScheduledStartDate, ScheduledEndDate, ScheduledDuration, Category, Item, "Change-Mgmt-Approval-", Region, Status, ClosureCode, Classification, Description, "Device-Alias+", AssignedGroup, AssignedToFullName FROM ECM_Change WHERE AssignedGroup = 'CORP - Huawei Tier II' AND 1=1 ORDER BY ECM_Change.DateCreated DESC
SELECT ChangeID, DateCreated, ScheduledStartDate, ScheduledEndDate, ScheduledDuration, Category, Item, "Change-Mgmt-Approval-", Region, Status, ClosureCode, Classification, Description, "Device-Alias+", AssignedGroup, AssignedToFullName FROM ECM_Change WHERE AssignedGroup='CORP - Huawei Tier II' AND ORDER BY ECM_Change.DateCreated DESC
SELECT ChangeID, DateCreated, ScheduledStartDate, ScheduledEndDate, ScheduledDuration, Category, Item, "Change-Mgmt-Approval-", Region, Status, ClosureCode, Classification, Description, "Device-Alias+", AssignedGroup, AssignedToFullName FROM ECM_Change WHERE AssignedGroup='CORP - Huawei Tier II' AND 1=1 ORDER BY ECM_Change.DateCreated DESC
SELECT ChangeID, DateCreated, ScheduledStartDate, ScheduledEndDate, ScheduledDuration, Category, Item, "Change-Mgmt-Approval-", Region, Status, ClosureCode, Classification, Description, "Device-Alias+", AssignedGroup, AssignedToFullName FROM ECM_Change WHERE AssignedGroup='CORP - Huawei Tier II' AND 1=1 ORDER BY ECM_Change.DateCreated DESC
SELECT ChangeID, DateCreated, ScheduledStartDate, ScheduledEndDate, ScheduledDuration, Category, Item, "Change-Mgmt-Approval-", Region, Status, ClosureCode, Classification, Description, "Device-Alias+", AssignedGroup, AssignedToFullName FROM ECM_Change WHERE AssignedGroup='CORP - Huawei Tier II' AND 1=1 ORDER BY ECM_Change.DateCreated DESC
SELECT ChangeID, DateCreated, ScheduledStartDate, ScheduledEndDate, ScheduledDuration, Category, Item, "Change-Mgmt-Approval-", Region, Status, ClosureCode, Classification, Description, "Device-Alias+", AssignedGroup, AssignedToFullName FROM ECM_Change WHERE AssignedGroup='CORP - Huawei Tier II' AND 1=1 ORDER BY ECM_Change.DateCreated DESC
SELECT ChangeID, DateCreated, ScheduledStartDate, ScheduledEndDate, ScheduledDuration, Category, Item, "Change-Mgmt-Approval-", Region, Status, ClosureCode, Classification, Description, "Device-Alias+", AssignedGroup, AssignedToFullName FROM ECM_Change WHERE AssignedGroup= CORP - Huawei Tier II AND 1=1 ORDER BY ECM_Change.DateCreated DESC
SELECT ChangeID, DateCreated, ScheduledStartDate, ScheduledEndDate, ScheduledDuration, Category, Item, "Change-Mgmt-Approval-", Region, Status, ClosureCode, Classification, Description, "Device-Alias+", AssignedGroup, AssignedToFullName FROM ECM_Change WHERE AssignedGroup= CORP - Huawei Tier II AND 1=1 ORDER BY ECM_Change.DateCreated DESC
SELECT ChangeID, DateCreated, ScheduledStartDate, ScheduledEndDate, ScheduledDuration, Category, Item, "Change-Mgmt-Approval-", Region, Status, ClosureCode, Classification, Description, "Device-Alias+", AssignedGroup, AssignedToFullName FROM ECM_Change ORDER BY ECM_Change.DateCreated DESC
SELECT ChangeID, DateCreated, ScheduledStartDate, ScheduledEndDate, ScheduledDuration, Category, Item, "Change-Mgmt-Approval-", Region, Status, ClosureCode, Classification, Description, "Device-Alias+", AssignedGroup, AssignedToFullName FROM ECM_Change WHERE AssignedGroup= CORP - Huawei Tier II AND 1=1 ORDER BY ECM_Change.DateCreated DESC
SELECT ChangeID, DateCreated, ScheduledStartDate, ScheduledEndDate, ScheduledDuration, Category, Item, "Change-Mgmt-Approval-", Region, Status, ClosureCode, Classification, Description, "Device-Alias+", AssignedGroup, AssignedToFullName FROM ECM_Change WHERE AssignedGroup= CORP - Huawei Tier II AND 1=1 ORDER BY ECM_Change.DateCreated DESC
SELECT ChangeID, DateCreated, ScheduledStartDate, ScheduledEndDate, ScheduledDuration, Category, Item, "Change-Mgmt-Approval-", Region, Status, ClosureCode, Classification, Description, "Device-Alias+", AssignedGroup, AssignedToFullName FROM ECM_Change WHERE AssignedGroup= CORP - Huawei Tier II AND 1=1 ORDER BY ECM_Change.DateCreated DESC
SELECT ChangeID, DateCreated, ScheduledStartDate, ScheduledEndDate, ScheduledDuration, Category, Item, "Change-Mgmt-Approval-", Region, Status, ClosureCode, Classification, Description, "Device-Alias+", AssignedGroup, AssignedToFullName FROM ECM_Change WHERE AssignedGroup= CORP - Huawei Tier II1=1 ORDER BY ECM_Change.DateCreated DESC
SELECT ChangeID, DateCreated, ScheduledStartDate, ScheduledEndDate, ScheduledDuration, Category, Item, "Change-Mgmt-Approval-", Region, Status, ClosureCode, Classification, Description, "Device-Alias+", AssignedGroup, AssignedToFullName FROM ECM_Change WHERE AssignedGroup= CORP - Huawei Tier II1=1 ORDER BY ECM_Change.DateCreated DESC
SELECT ChangeID, DateCreated, ScheduledStartDate, ScheduledEndDate, ScheduledDuration, Category, Item, "Change-Mgmt-Approval-", Region, Status, ClosureCode, Classification, Description, "Device-Alias+", AssignedGroup, AssignedToFullName FROM ECM_Change WHERE AssignedGroup= CORP - Huawei Tier II1=1 ORDER BY ECM_Change.DateCreated DESC
SELECT ChangeID, DateCreated, ScheduledStartDate, ScheduledEndDate, ScheduledDuration, Category, Item, "Change-Mgmt-Approval-", Region, Status, ClosureCode, Classification, Description, "Device-Alias+", AssignedGroup, AssignedToFullName FROM ECM_Change WHERE AssignedGroup= CORP - Huawei Tier II ORDER BY ECM_Change.DateCreated DESC
SELECT ChangeID, DateCreated, ScheduledStartDate, ScheduledEndDate, ScheduledDuration, Category, Item, "Change-Mgmt-Approval-", Region, Status, ClosureCode, Classification, Description, "Device-Alias+", AssignedGroup, AssignedToFullName FROM ECM_Change WHERE 'AssignedGroup= CORP - Huawei Tier II' ORDER BY ECM_Change.DateCreated DESC
SELECT ChangeID, DateCreated, ScheduledStartDate, ScheduledEndDate, ScheduledDuration, Category, Item, "Change-Mgmt-Approval-", Region, Status, ClosureCode, Classification, Description, "Device-Alias+", AssignedGroup, AssignedToFullName FROM ECM_Change WHERE AssignedGroup= CORP - Huawei Tier II ORDER BY ECM_Change.DateCreated DESC
SELECT ChangeID, DateCreated, ScheduledStartDate, ScheduledEndDate, ScheduledDuration, Category, Item, "Change-Mgmt-Approval-", Region, Status, ClosureCode, Classification, Description, "Device-Alias+", AssignedToGroup, AssignedToFullName FROM ECM_Change WHERE AssignedToGroup= CORP - Huawei Tier II ORDER BY ECM_Change.DateCreated DESC
SELECT ChangeID, DateCreated, ScheduledStartDate, ScheduledEndDate, ScheduledDuration, Category, Item, "Change-Mgmt-Approval-", Region, Status, ClosureCode, Classification, Description, "Device-Alias+", AssignedGroup, AssignedToFullName FROM ECM_Change WHERE AssignedGroup = CORP - Huawei Tier II ORDER BY ECM_Change.DateCreated DESC



Here is the Pass Thrpugh Qry again:

SELECT ECM_Change.ChangeID, ECM_Change.DateCreated, ECM_Change.ScheduledStartDate, ECM_Change.ScheduledEndDate, ECM_Change.ScheduledDuration, ECM_Change.Category, ECM_Change.Item, ECM_Change."Change-Mgmt-Approval-", ECM_Change.Region, ECM_Change.Status, ECM_Change.ClosureCode, ECM_Change.Classification, ECM_Change.Description, ECM_Change."Device-Alias+", ECM_Change.AssignedGroup, ECM_Change.AssignedToFullNameFROM ECM_Change ECM_ChangeWHERE (ECM_Change.AssignedGroup='CORP - Huawei Tier II') AND (ECM_Change.DateCreated>={ts '2011-01-01 00:00:00'}) ORDER BY ECM_Change.DateCreated DESC
 
Upvote 0 Downvote
Apparently you have run this many times. If you had included the code (meaning vba) as I suggested, we might be able to determine why the debug.print of:
Code: 
SELECT ChangeID, DateCreated, ScheduledStartDate, ScheduledEndDate, ScheduledDuration, Category, Item, "Change-Mgmt-Approval-", Region, Status, ClosureCode, Classification, Description, "Device-Alias+", AssignedGroup, AssignedToFullName FROM ECM_Change WHERE AssignedGroup = 'CORP - Huawei Tier II' AND 1=1  ORDER BY ECM_Change.DateCreated DESC
is not the same as the SQL of [qryECM Query].
Code: 
SELECT ECM_Change.ChangeID, ECM_Change.DateCreated, ECM_Change.ScheduledStartDate, ECM_Change.ScheduledEndDate, ECM_Change.ScheduledDuration, ECM_Change.Category, ECM_Change.Item, ECM_Change."Change-Mgmt-Approval-", ECM_Change.Region, ECM_Change.Status, ECM_Change.ClosureCode, ECM_Change.Classification, ECM_Change.Description, ECM_Change."Device-Alias+", ECM_Change.AssignedGroup, ECM_Change.AssignedToFullNameFROM ECM_Change ECM_ChangeWHERE (ECM_Change.AssignedGroup='CORP - Huawei Tier II') AND (ECM_Change.DateCreated>={ts '2011-01-01 00:00:00'}) ORDER BY ECM_Change.DateCreated DESC

Did you enter anything into the date text boxes?

Duane
Hook'D on Access
MS Access MVP
 
Upvote 0 Downvote
Yes...I kept inputting the dates. Would you suggest I start that process over?
 
Upvote 0 Downvote
I am not understanding what code of the debug print you are referring to
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Kennelbloke
  • Locked
  • Solved
Subform not updating in parent form
Replies
10
Views
561
Kennelbloke
Kennelbloke
PrimoSteve
  • Locked
  • Question
Fill in 2nd Textbox2 from Textbox1
Replies
3
Views
443
PrimoSteve
PrimoSteve
Xavierparkash
  • Locked
  • Question
MS Access Slow Search in Linked Database
Replies
0
Views
238
Xavierparkash
Xavierparkash
caykamanj1966
  • Locked
  • Question
Duplicate Records in DataSheet View
Replies
5
Views
489
Andrzejek
Andrzejek
Modex
  • Locked
  • Question
How to Close Desktop Application Windows using VBA
Replies
3
Views
252
Andrzejek
Andrzejek

Part and Inventory Search

Sponsor

Back
Top