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!
-
Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Creating a combo box that runs a report
- Thread starter Mattine
- Start date
By limiting the query that feeds the report you can get those results. In the query that feed the report place a reference to the report that looks like this:
Like [Forms]![MyFormName]![MyComboName]
In the row source of the combo you have two options, if there are not many customers you can use a value list and type the customer id's and place a * in there to signify ALL. When the query executes it will look at the combo and if it's a *, you'll get ALL customers.
The other way to populate the combo box is with a union query, similar to this:
SELECT DISTINCTROW MyTable.CustomerID
FROM MyTable
UNION
SELECT "*" from MyTable
ORDER BY MyTable.CustomerID;
Then you'd get all the customer ID's with a * at the top for ALL.
HTH Joe Miller
joe.miller@flotech.net
Like [Forms]![MyFormName]![MyComboName]
In the row source of the combo you have two options, if there are not many customers you can use a value list and type the customer id's and place a * in there to signify ALL. When the query executes it will look at the combo and if it's a *, you'll get ALL customers.
The other way to populate the combo box is with a union query, similar to this:
SELECT DISTINCTROW MyTable.CustomerID
FROM MyTable
UNION
SELECT "*" from MyTable
ORDER BY MyTable.CustomerID;
Then you'd get all the customer ID's with a * at the top for ALL.
HTH Joe Miller
joe.miller@flotech.net
- Thread starter
- #3
You can do the same thing with multiple combos, you just have to be careful that they are set to ALL so that if one is filled out and another is blank, the blank one will get rid of all the records. As for clearing them, you can issue a command after the docmd.openreport in code to set them back to * like this:
[MyCombo] = "*"
HTH Joe Miller
joe.miller@flotech.net
[MyCombo] = "*"
HTH Joe Miller
joe.miller@flotech.net
- Thread starter
- #5
Dear Joe,
Here is the syntax for the print button that runs the reports:
Private Sub btnPrintToPrinter_Click()
On Error GoTo Err_btnPrintToPrinter_Click
Dim mRptName As String
Dim mWhereClause As String, mWhereClauseUsed As Integer
Dim mRptDest As Integer
Dim mRptPrints As Integer
Select Case Me![PrinterDestination]
Case 1 ' Destination is Screen
mRptDest = acPreview
Case 2 ' Destination is MS Word
mRptDest = 0
Case 3 ' Destination is printer
mRptDest = acNormal
End Select
Dim myDB As Database, MyQuery As QueryDef
Set myDB = DBEngine.Workspaces(0).Databases(0)
mRptName = "rptTaskListing"
Set MyQuery = myDB.QueryDefs("qryTaskSelector"
MyQuery.SQL = "SELECT DISTINCT qryTasks.* FROM qryTasks"
If [txtDateFrom] > "" And IIf(IsNull([txtDateTo]), "", [txtDateTo]) = "" Then
[txtDateTo] = [txtDateFrom]
End If
mWhereClauseUsed = 0
mWhereClause = ""
If [cboTaskStatus] > "" Then
mWhereClauseUsed = -1
Select Case cboTaskStatus
Case "New", "Hold", "In Process", "Follow-up", "Completed", "Cancelled"
mWhereClause = mWhereClause + "([Status]='" + [cboTaskStatus] + "')"
Case "All Open"
mWhereClause = mWhereClause + "Not([Status]='Completed' or [Status]='Cancelled')"
Case "All Closed"
mWhereClause = mWhereClause + "([Status]='Completed' or [Status]='Cancelled')"
Case "*"
DoCmd.ShowAllRecords
End Select
End If
If cboClient = "*" Then
DoCmd.ShowAllRecords
End If
'If [cboClient] > "" Then
'If mWhereClauseUsed = -1 Then
'mWhereClause = mWhereClause + " AND "
'End If
'mWhereClauseUsed = -1
'mWhereClause = mWhereClause + "([C_LinkCode]=" + [cboClient] + ""
'End If
If [cboEmployee] > "" Then
If mWhereClauseUsed = -1 Then
mWhereClause = mWhereClause + "And"
End If
mWhereClauseUsed = -1
mWhereClause = mWhereClause + "([EmployeeAssigned]='" + [cboEmployee] + "')"
End If
I am just wondering if you can let me know why my syntax: If cboClient="*" then docmd.showallrecords doesn't work. I am assuming it has to do with the where clause. Someone else had done all of this programming beforehand. What would syntax should I use to make the show all records command work?
Thank-you!
Sincerely,
~Mattine
Here is the syntax for the print button that runs the reports:
Private Sub btnPrintToPrinter_Click()
On Error GoTo Err_btnPrintToPrinter_Click
Dim mRptName As String
Dim mWhereClause As String, mWhereClauseUsed As Integer
Dim mRptDest As Integer
Dim mRptPrints As Integer
Select Case Me![PrinterDestination]
Case 1 ' Destination is Screen
mRptDest = acPreview
Case 2 ' Destination is MS Word
mRptDest = 0
Case 3 ' Destination is printer
mRptDest = acNormal
End Select
Dim myDB As Database, MyQuery As QueryDef
Set myDB = DBEngine.Workspaces(0).Databases(0)
mRptName = "rptTaskListing"
Set MyQuery = myDB.QueryDefs("qryTaskSelector"
MyQuery.SQL = "SELECT DISTINCT qryTasks.* FROM qryTasks"
If [txtDateFrom] > "" And IIf(IsNull([txtDateTo]), "", [txtDateTo]) = "" Then
[txtDateTo] = [txtDateFrom]
End If
mWhereClauseUsed = 0
mWhereClause = ""
If [cboTaskStatus] > "" Then
mWhereClauseUsed = -1
Select Case cboTaskStatus
Case "New", "Hold", "In Process", "Follow-up", "Completed", "Cancelled"
mWhereClause = mWhereClause + "([Status]='" + [cboTaskStatus] + "')"
Case "All Open"
mWhereClause = mWhereClause + "Not([Status]='Completed' or [Status]='Cancelled')"
Case "All Closed"
mWhereClause = mWhereClause + "([Status]='Completed' or [Status]='Cancelled')"
Case "*"
DoCmd.ShowAllRecords
End Select
End If
If cboClient = "*" Then
DoCmd.ShowAllRecords
End If
'If [cboClient] > "" Then
'If mWhereClauseUsed = -1 Then
'mWhereClause = mWhereClause + " AND "
'End If
'mWhereClauseUsed = -1
'mWhereClause = mWhereClause + "([C_LinkCode]=" + [cboClient] + "
"'End If
If [cboEmployee] > "" Then
If mWhereClauseUsed = -1 Then
mWhereClause = mWhereClause + "And"
End If
mWhereClauseUsed = -1
mWhereClause = mWhereClause + "([EmployeeAssigned]='" + [cboEmployee] + "')"
End If
I am just wondering if you can let me know why my syntax: If cboClient="*" then docmd.showallrecords doesn't work. I am assuming it has to do with the where clause. Someone else had done all of this programming beforehand. What would syntax should I use to make the show all records command work?
Thank-you!
Sincerely,
~Mattine
- Thread starter
- #7
Dear Joe,
But I am pretty sure that I still need this syntax to be able to select one client within the combo box to run a report specifically for that client:
'mWhereClauseUsed = -1
'mWhereClause = mWhereClause + "([C_LinkCode]=" + [cboClient] + ""
Where would I add your case statement?
Sincerely,
~Mattine
But I am pretty sure that I still need this syntax to be able to select one client within the combo box to run a report specifically for that client:
'mWhereClauseUsed = -1
'mWhereClause = mWhereClause + "([C_LinkCode]=" + [cboClient] + "
"Where would I add your case statement?
Sincerely,
~Mattine
Change this:
[tt]
If [cboTaskStatus] > "" or [cboTaskStatus] <> "*" Then
mWhereClauseUsed = -1
Select Case cboTaskStatus
Case "New", "Hold", "In Process", "Follow-up", "Completed", "Cancelled"
mWhereClause = mWhereClause + "([Status]='" + [cboTaskStatus] + "')"
Case "All Open"
mWhereClause = mWhereClause + "Not([Status]='Completed' or [Status]='Cancelled')"
Case "All Closed"
mWhereClause = mWhereClause + "([Status]='Completed' or [Status]='Cancelled')"
End Select
End If
[/tt]
All I did was add an or to check for *, if cboTaskType is blank or * then there is no where clause needed, so no need to run the select.
HTH Joe Miller
joe.miller@flotech.net
[tt]
If [cboTaskStatus] > "" or [cboTaskStatus] <> "*" Then
mWhereClauseUsed = -1
Select Case cboTaskStatus
Case "New", "Hold", "In Process", "Follow-up", "Completed", "Cancelled"
mWhereClause = mWhereClause + "([Status]='" + [cboTaskStatus] + "')"
Case "All Open"
mWhereClause = mWhereClause + "Not([Status]='Completed' or [Status]='Cancelled')"
Case "All Closed"
mWhereClause = mWhereClause + "([Status]='Completed' or [Status]='Cancelled')"
End Select
End If
[/tt]
All I did was add an or to check for *, if cboTaskType is blank or * then there is no where clause needed, so no need to run the select.
HTH Joe Miller
joe.miller@flotech.net
- Thread starter
- #9
When I use this syntax for the cboclient:
If [cboClient] > "" Or [cboClient] <> "*" Then
If mWhereClauseUsed = -1 Then
mWhereClause = mWhereClause + " AND "
End If
mWhereClauseUsed = -1
mWhereClause = mWhereClause + "([C_LinkCode]=" + [cboClient] + ""
End If
When I choose "*" in the combo box, I receive an OpenReport Cancelled Error.
Any ideas?
Thank-you for your help!
Sincerely,
~Mattine
If [cboClient] > "" Or [cboClient] <> "*" Then
If mWhereClauseUsed = -1 Then
mWhereClause = mWhereClause + " AND "
End If
mWhereClauseUsed = -1
mWhereClause = mWhereClause + "([C_LinkCode]=" + [cboClient] + "
"End If
When I choose "*" in the combo box, I receive an OpenReport Cancelled Error.
Any ideas?
Thank-you for your help!
Sincerely,
~Mattine
Ok, maybe I'm butting in where I'm not wanted, but wouldn't it just be easier to leave the combo box blank in order to run the report for all customers?
I have the following code in a database with the same format as you're describing.
LinkFilter = ""
If Not (IsNull(Me!Dept)) Then
LinkFilter = "[Department] = " & dQuote & Me!Dept & dQuote
End If
NumEntries = DCount("*", "Item Id Report Query", LinkFilter)If NumEntries < 1 Then
MsgBox "There are no audits which match the specified criteria.", vbInformation
Else
RptName = Me!ReportName
DoCmd.OpenReport RptName, acViewPreview, , LinkFilter
DoCmd.Close acForm, "ReportForm"
End If
I just tell the users to leave the combo box empty if they want to run the report and show all departments or they can use it to report a single department.
I have the following code in a database with the same format as you're describing.
LinkFilter = ""
If Not (IsNull(Me!Dept)) Then
LinkFilter = "[Department] = " & dQuote & Me!Dept & dQuote
End If
NumEntries = DCount("*", "Item Id Report Query", LinkFilter)If NumEntries < 1 Then
MsgBox "There are no audits which match the specified criteria.", vbInformation
Else
RptName = Me!ReportName
DoCmd.OpenReport RptName, acViewPreview, , LinkFilter
DoCmd.Close acForm, "ReportForm"
End If
I just tell the users to leave the combo box empty if they want to run the report and show all departments or they can use it to report a single department.
- Thread starter
- #11
Dear Maquis,
That would be VERY cool, but the user has multiple combo boxes. For example, he would like to sort by employee in one occurrence, client in another, and status in another. What I am wrestling with is how to create three different reports using three different combo boxes. I would like to show all records for each; client, employee, and status, but they have to be sorted by those respective fields in order to work.
If you look above, you can see some of the syntax on the combo boxes.
Let me know what you think would be the best way!
Sincerely,
~Mattine
That would be VERY cool, but the user has multiple combo boxes. For example, he would like to sort by employee in one occurrence, client in another, and status in another. What I am wrestling with is how to create three different reports using three different combo boxes. I would like to show all records for each; client, employee, and status, but they have to be sorted by those respective fields in order to work.
If you look above, you can see some of the syntax on the combo boxes.
Let me know what you think would be the best way!
Sincerely,
~Mattine
Mattine,
Usually I create an option group on the form calling the report which allows the user to choose the sort order on the report. You can then set the orderby property on the report to match the selected option.
I don't currently have any databases using this functionally, or I would send you the code. I can't remember if the orderby setting should go in the report open event or the click event of the button calling the report. (If I dig something up, I'll let you know.)
Multiple combo boxes shouldn't be a problem. The above snippet of code was "snipped" from a form with 4 combo boxes. Each combo box just built upon LinkFilter.
Usually I create an option group on the form calling the report which allows the user to choose the sort order on the report. You can then set the orderby property on the report to match the selected option.
I don't currently have any databases using this functionally, or I would send you the code. I can't remember if the orderby setting should go in the report open event or the click event of the button calling the report. (If I dig something up, I'll let you know.)
Multiple combo boxes shouldn't be a problem. The above snippet of code was "snipped" from a form with 4 combo boxes. Each combo box just built upon LinkFilter.
Ok, I lied. I do have a database using the sort option group. Here is the code.
The form consists of 4 combo boxes (Begin Quarter, End Quarter, Building and Event) and 2 option groups (Sort Order and Attendance)
I put this code in the click event of the run report button:
Private Sub Print_Click()
On Error GoTo Err_Print_Click
Dim stDocName As String
If (Me!BegQuarter > Me!EndQuarter) And (Not (IsNull(Me!EndQuarter))) Then
MsgBox "Error! Beginning quarter cannot be later than end quarter!", vbCritical
Me!BegQuarter.SetFocus
Exit Sub
End If
Criteria = ""
If Not (IsNull(Me!BegQuarter)) Then
Criteria = "[Event Date] >= #" & Me!BegQuarter & "#"
End If
If Not (IsNull(Me!EndQuarter)) Then
If Criteria = "" Then
Criteria = "[Event Date] <= #" & Me!EndQuarter & "#"
Else
Criteria = Criteria & "And [Event Date] <= #" & Me!EndQuarter & "#"
End If
End If
If Not (IsNull(Me!Building)) Then
If Criteria = "" Then
Criteria = "[Building Id] = " & Me!Building
Else
Criteria = Criteria & "And [Building Id] = " & Me!Building
End If
End If
If Not (IsNull(Me!Event)) Then
If Criteria = "" Then
Criteria = "[Event Id] = " & Me!Event
Else
Criteria = Criteria & "And [Event Id] = " & Me!Event
End If
End If
If Me!Attendance = 1 Then 'No participants
If Criteria = "" Then
Criteria = "([NumParticipants] = 0 or IsNull([NumParticipants]))"
Else
Criteria = Criteria & "And ([NumParticipants] = 0 or IsNull([NumParticipants]))"
End If
Else
If Me!Attendance = 2 Then
If Criteria = "" Then
Criteria = "[NumParticipants] > 0 "
Else
Criteria = Criteria & "And [NumParticipants] > 0"
End If
End If
End If
stDocName = "Event Schedule"
DoCmd.OpenReport stDocName, acPreview, , Criteria
Exit_Print_Click:
Exit Sub
Err_Print_Click:
MsgBox Err.Description
Resume Exit_Print_Click
End Sub
----------------
And this code in the open event of the report:
Private Sub Report_Open(Cancel As Integer)
Select Case Forms![Schedule Report Select].[sortorder]
Case 1 'Sort by Building then by Date
Me.GroupLevel(0).ControlSource = "Building"
Me.GroupLevel(1).ControlSource = "Event Date"
Case 2 'Sort by Event Name then by Date
Me.GroupLevel(0).ControlSource = "Event Name"
Me.GroupLevel(1).ControlSource = "Event Date"
Case 3 'Sort descending by # of Participants then by date
Me.GroupLevel(0).ControlSource = "NumParticipants"
Me.GroupLevel(0).sortorder = True
Me.GroupLevel(1).ControlSource = "Event Date"
Case Else 'Sort by Date
Me.GroupLevel(0).ControlSource = "Event Date"
End Select
DoCmd.Close acForm, "Schedule Report Select"
End Sub
I hope this helps some. B-)
The form consists of 4 combo boxes (Begin Quarter, End Quarter, Building and Event) and 2 option groups (Sort Order and Attendance)
I put this code in the click event of the run report button:
Private Sub Print_Click()
On Error GoTo Err_Print_Click
Dim stDocName As String
If (Me!BegQuarter > Me!EndQuarter) And (Not (IsNull(Me!EndQuarter))) Then
MsgBox "Error! Beginning quarter cannot be later than end quarter!", vbCritical
Me!BegQuarter.SetFocus
Exit Sub
End If
Criteria = ""
If Not (IsNull(Me!BegQuarter)) Then
Criteria = "[Event Date] >= #" & Me!BegQuarter & "#"
End If
If Not (IsNull(Me!EndQuarter)) Then
If Criteria = "" Then
Criteria = "[Event Date] <= #" & Me!EndQuarter & "#"
Else
Criteria = Criteria & "And [Event Date] <= #" & Me!EndQuarter & "#"
End If
End If
If Not (IsNull(Me!Building)) Then
If Criteria = "" Then
Criteria = "[Building Id] = " & Me!Building
Else
Criteria = Criteria & "And [Building Id] = " & Me!Building
End If
End If
If Not (IsNull(Me!Event)) Then
If Criteria = "" Then
Criteria = "[Event Id] = " & Me!Event
Else
Criteria = Criteria & "And [Event Id] = " & Me!Event
End If
End If
If Me!Attendance = 1 Then 'No participants
If Criteria = "" Then
Criteria = "([NumParticipants] = 0 or IsNull([NumParticipants]))"
Else
Criteria = Criteria & "And ([NumParticipants] = 0 or IsNull([NumParticipants]))"
End If
Else
If Me!Attendance = 2 Then
If Criteria = "" Then
Criteria = "[NumParticipants] > 0 "
Else
Criteria = Criteria & "And [NumParticipants] > 0"
End If
End If
End If
stDocName = "Event Schedule"
DoCmd.OpenReport stDocName, acPreview, , Criteria
Exit_Print_Click:
Exit Sub
Err_Print_Click:
MsgBox Err.Description
Resume Exit_Print_Click
End Sub
----------------
And this code in the open event of the report:
Private Sub Report_Open(Cancel As Integer)
Select Case Forms![Schedule Report Select].[sortorder]
Case 1 'Sort by Building then by Date
Me.GroupLevel(0).ControlSource = "Building"
Me.GroupLevel(1).ControlSource = "Event Date"
Case 2 'Sort by Event Name then by Date
Me.GroupLevel(0).ControlSource = "Event Name"
Me.GroupLevel(1).ControlSource = "Event Date"
Case 3 'Sort descending by # of Participants then by date
Me.GroupLevel(0).ControlSource = "NumParticipants"
Me.GroupLevel(0).sortorder = True
Me.GroupLevel(1).ControlSource = "Event Date"
Case Else 'Sort by Date
Me.GroupLevel(0).ControlSource = "Event Date"
End Select
DoCmd.Close acForm, "Schedule Report Select"
End Sub
I hope this helps some. B-)
- Thread starter
- #14
Dear Maquis,
This is cool, but would there be any way to sort the report by choosing "*" to show all records in a report for a client, task status, or employee? Is there any way to select * within one combo box and have it sort by that particular combo box, such as employee?
Here is what I have right now. It resolves based off of a Print Button.
Private Sub btnPrintToPrinter_Click()
On Error GoTo Err_btnPrintToPrinter_Click
Dim mRptName As String
Dim mWhereClause As String, mWhereClauseUsed As Integer
Dim mRptDest As Integer
Dim mRptPrints As Integer
Select Case Me![PrinterDestination]
Case 1 ' Destination is Screen
mRptDest = acPreview
Case 2 ' Destination is MS Word
mRptDest = 0
Case 3 ' Destination is printer
mRptDest = acNormal
End Select
Dim myDB As Database, MyQuery As QueryDef
Set myDB = DBEngine.Workspaces(0).Databases(0)
mRptName = "rptTaskListing"
Set MyQuery = myDB.QueryDefs("qryTaskSelector"
MyQuery.SQL = "SELECT DISTINCT qryTasks.* FROM qryTasks"
If [txtDateFrom] > "" And IIf(IsNull([txtDateTo]), "", [txtDateTo]) = "" Then
[txtDateTo] = [txtDateFrom]
End If
mWhereClauseUsed = 0
mWhereClause = ""
If [cboTaskStatus] > "" Or [cboTaskStatus] <> "*" Then
mWhereClauseUsed = -1
Select Case cboTaskStatus
Case "New", "Hold", "In Process", "Follow-up", "Completed", "Cancelled"
mWhereClause = mWhereClause + "([Status]='" + [cboTaskStatus] + "')"
Case "All Open"
mWhereClause = mWhereClause + "Not([Status]='Completed' or [Status]='Cancelled')"
Case "All Closed"
mWhereClause = mWhereClause + "([Status]='Completed' or [Status]='Cancelled')"
Case "*"
DoCmd.ShowAllRecords
End Select
End If
If [cboClient] > "" Then
If mWhereClauseUsed = -1 Then
mWhereClause = mWhereClause + " AND "
End If
mWhereClauseUsed = -1
mWhereClause = mWhereClause + "([C_LinkCode]=" + [cboClient] + ""
End If
If [cboEmployee] > "" Or [cboEmployee] <> "*" Then
mWhereClauseUsed = -1
Select Case cboEmployee
Case "ACC"
mWhereClause = mWhereClause + "Not([EmployeeAssigned]='ANY' or [EmployeeAssigned]='CCC' or [EmployeeAssigned]='DC' or [EmployeeAssigned]= 'DR' or [EmployeeAssigned]= 'GM' or [EmployeeAssigned]='JF')"
End Select
'If mWhereClauseUsed = -1 Then
'mWhereClause = mWhereClause + "And"
'End If
'mWhereClauseUsed = -1
'mWhereClause = mWhereClause + "([EmployeeAssigned]='" + [cboEmployee] + "')"
End If
If [txtDateFrom] > "" Then
If mWhereClauseUsed = -1 Then
mWhereClause = mWhereClause + " AND "
End If
mWhereClauseUsed = -1
mWhereClause = mWhereClause + "([DateRequested]>=#" + Str([txtDateFrom]) + "#)"
End If
If [txtDateTo] > "" Then
If mWhereClauseUsed = -1 Then
mWhereClause = mWhereClause + " AND "
End If
mWhereClauseUsed = -1
mWhereClause = mWhereClause + "([DateRequested]<=#" + Str([txtDateTo]) + "#)"
End If
If mWhereClause > "" Then
MyQuery.SQL = MyQuery.SQL + " WHERE " + mWhereClause
End If
MyQuery.SQL = MyQuery.SQL + ";"
MyQuery.Close
For mRptPrints = 1 To Me.[NumCopies]
Select Case Me.PrinterDestination
Case 2
'The following line "exports" the report to a word document (*.RTF) and starts word.
'DoCmd OutputTo acREPORT, mRptName, acFORMATRTF, IIf(Left(gUserID, 4) = "DCOH", "C:\", "M:\DATA\" & Mid(mRptName, 4, 8) + ".RTF", True
DoCmd.OutputTo acReport, mRptName, acFormatRTF, gUserTempDir & Mid(mRptName, 4, 8) + ".RTF", True
Case 1, 3
DoCmd.OpenReport mRptName, mRptDest
cboTaskStatus = ""
cboClient = ""
cboEmployee = ""
Case 4
'The following line "exports" the report to a text file (*.TXT).
DoCmd.OutputTo acReport, mRptName, acFormatTXT, IIf(Left(gUserID, 4) = "DCOH", "C:\", gUserTempDir) & Mid(mRptName, 4, 8) + ".RTF", True
End Select
Next
Exit_btnPrintToPrinter_Click:
Exit Sub
Err_btnPrintToPrinter_Click:
MsgBox "Error #" & Err.Number & ": " & Err.Description
Resume Exit_btnPrintToPrinter_Click
End Sub
This is cool, but would there be any way to sort the report by choosing "*" to show all records in a report for a client, task status, or employee? Is there any way to select * within one combo box and have it sort by that particular combo box, such as employee?
Here is what I have right now. It resolves based off of a Print Button.
Private Sub btnPrintToPrinter_Click()
On Error GoTo Err_btnPrintToPrinter_Click
Dim mRptName As String
Dim mWhereClause As String, mWhereClauseUsed As Integer
Dim mRptDest As Integer
Dim mRptPrints As Integer
Select Case Me![PrinterDestination]
Case 1 ' Destination is Screen
mRptDest = acPreview
Case 2 ' Destination is MS Word
mRptDest = 0
Case 3 ' Destination is printer
mRptDest = acNormal
End Select
Dim myDB As Database, MyQuery As QueryDef
Set myDB = DBEngine.Workspaces(0).Databases(0)
mRptName = "rptTaskListing"
Set MyQuery = myDB.QueryDefs("qryTaskSelector"
MyQuery.SQL = "SELECT DISTINCT qryTasks.* FROM qryTasks"
If [txtDateFrom] > "" And IIf(IsNull([txtDateTo]), "", [txtDateTo]) = "" Then
[txtDateTo] = [txtDateFrom]
End If
mWhereClauseUsed = 0
mWhereClause = ""
If [cboTaskStatus] > "" Or [cboTaskStatus] <> "*" Then
mWhereClauseUsed = -1
Select Case cboTaskStatus
Case "New", "Hold", "In Process", "Follow-up", "Completed", "Cancelled"
mWhereClause = mWhereClause + "([Status]='" + [cboTaskStatus] + "')"
Case "All Open"
mWhereClause = mWhereClause + "Not([Status]='Completed' or [Status]='Cancelled')"
Case "All Closed"
mWhereClause = mWhereClause + "([Status]='Completed' or [Status]='Cancelled')"
Case "*"
DoCmd.ShowAllRecords
End Select
End If
If [cboClient] > "" Then
If mWhereClauseUsed = -1 Then
mWhereClause = mWhereClause + " AND "
End If
mWhereClauseUsed = -1
mWhereClause = mWhereClause + "([C_LinkCode]=" + [cboClient] + "
"End If
If [cboEmployee] > "" Or [cboEmployee] <> "*" Then
mWhereClauseUsed = -1
Select Case cboEmployee
Case "ACC"
mWhereClause = mWhereClause + "Not([EmployeeAssigned]='ANY' or [EmployeeAssigned]='CCC' or [EmployeeAssigned]='DC' or [EmployeeAssigned]= 'DR' or [EmployeeAssigned]= 'GM' or [EmployeeAssigned]='JF')"
End Select
'If mWhereClauseUsed = -1 Then
'mWhereClause = mWhereClause + "And"
'End If
'mWhereClauseUsed = -1
'mWhereClause = mWhereClause + "([EmployeeAssigned]='" + [cboEmployee] + "')"
End If
If [txtDateFrom] > "" Then
If mWhereClauseUsed = -1 Then
mWhereClause = mWhereClause + " AND "
End If
mWhereClauseUsed = -1
mWhereClause = mWhereClause + "([DateRequested]>=#" + Str([txtDateFrom]) + "#)"
End If
If [txtDateTo] > "" Then
If mWhereClauseUsed = -1 Then
mWhereClause = mWhereClause + " AND "
End If
mWhereClauseUsed = -1
mWhereClause = mWhereClause + "([DateRequested]<=#" + Str([txtDateTo]) + "#)"
End If
If mWhereClause > "" Then
MyQuery.SQL = MyQuery.SQL + " WHERE " + mWhereClause
End If
MyQuery.SQL = MyQuery.SQL + ";"
MyQuery.Close
For mRptPrints = 1 To Me.[NumCopies]
Select Case Me.PrinterDestination
Case 2
'The following line "exports" the report to a word document (*.RTF) and starts word.
'DoCmd OutputTo acREPORT, mRptName, acFORMATRTF, IIf(Left(gUserID, 4) = "DCOH", "C:\", "M:\DATA\"
& Mid(mRptName, 4, 8) + ".RTF", TrueDoCmd.OutputTo acReport, mRptName, acFormatRTF, gUserTempDir & Mid(mRptName, 4, 8) + ".RTF", True
Case 1, 3
DoCmd.OpenReport mRptName, mRptDest
cboTaskStatus = ""
cboClient = ""
cboEmployee = ""
Case 4
'The following line "exports" the report to a text file (*.TXT).
DoCmd.OutputTo acReport, mRptName, acFormatTXT, IIf(Left(gUserID, 4) = "DCOH", "C:\", gUserTempDir) & Mid(mRptName, 4, 8) + ".RTF", True
End Select
Next
Exit_btnPrintToPrinter_Click:
Exit Sub
Err_btnPrintToPrinter_Click:
MsgBox "Error #" & Err.Number & ": " & Err.Description
Resume Exit_btnPrintToPrinter_Click
End Sub
- Status
Similar threads
- Locked
- Question
- Locked
- Question