Also do your yourself and everyone else a favor and get rid to table lookups! This took me like twenty minutes to figure out why my original code did not work.
Is it possible to automate this more so that it detects what currencies are in use in the filtered form and adds them up accordingly? Such as a query would do. At the moment I have 5 currencies but this may increase.
I did this using a listbox and temp table. Since the amount of possible currencies changes with each filter this would be hard doing with individual textboxes in an aesthetic way. Plus you have to redesign the form if you add or delete a currency.
1. run an update query to ensure the temptable has each currencyid
2. clear out any existing total
after the filter
3. loop the recordset and add the total to the temp table for the correct currency
4. requery the listbox
Code:
Public Sub UpdateSums()
Dim rs As DAO.Recordset
Dim rsSum As DAO.Recordset
Set rs = Me.RecordsetClone
Set rsSum = CurrentDb.OpenRecordset("tblTempSums", dbOpenDynaset)
If Not rs.EOF Then rs.MoveFirst
CurrentDb.Execute "qryAppendCurrency"
CurrentDb.Execute "qryClearSums"
Do While Not rs.EOF
rsSum.FindFirst "currencyID = " & rs!Currency
rsSum.Edit
rsSum!totalCurrency = rsSum!totalCurrency + CDbl(rs!InvoiceTotalHome)
rsSum.Update
rs.MoveNext
Loop
Me.lstSums.Requery
End Sub
The other way would be to build a query and pass it the same IN filter as the report uses. The problem with that is that the query could blow up. If you had 1000 records showing then your IN filter contains thousand entries
IN(123, 456, 789,..... )
This could exceed the length of the sql string.
FYI, This could still happen in the Report if you pass a filter that includes too many records.
I did this using a listbox and temp table. Since the amount of possible currencies changes with each filter this would be hard doing with individual textboxes in an aesthetic way. Plus you have to redesign the form if you add or delete a currency.
1. run an update query to ensure the temptable has each currencyid
2. clear out any existing total
after the filter
3. loop the recordset and add the total to the temp table for the correct currency
4. requery the listbox
Code:
Public Sub UpdateSums()
Dim rs As DAO.Recordset
Dim rsSum As DAO.Recordset
Set rs = Me.RecordsetClone
Set rsSum = CurrentDb.OpenRecordset("tblTempSums", dbOpenDynaset)
If Not rs.EOF Then rs.MoveFirst
CurrentDb.Execute "qryAppendCurrency"
CurrentDb.Execute "qryClearSums"
Do While Not rs.EOF
rsSum.FindFirst "currencyID = " & rs!Currency
rsSum.Edit
rsSum!totalCurrency = rsSum!totalCurrency + CDbl(rs!InvoiceTotalHome)
rsSum.Update
rs.MoveNext
Loop
Me.lstSums.Requery
End Sub
The other way would be to build a query and pass it the same IN filter as the report uses. The problem with that is that the query could blow up. If you had 1000 records showing then your IN filter contains thousand entries
IN(123, 456, 789,..... )
This could exceed the length of the sql string.
FYI, This could still happen in the Report if you pass a filter that includes too many records.
I have a question/issue: The code for the above to put it into a report works fine but when I try to adjust the code to export that report to an email or excel it puts all the records in it? Any ideas how to stop that? The preview of the report is good and only shows the records in the filtered form. Below is the code I've got to export it to excel
Code:
Dim strOut As String
Dim rs As dao.Recordset
Set rs = Forms!MajPMultiSearch.RecordsetClone
If Forms!MajPMultiSearch.Filter = "" Or Forms!MajPMultiSearch.FilterOn = False Then
DoCmd.OutputTo acOutputReport, "ExpenseS", acFormatXLS, , , acDialog
Else
Do While Not rs.EOF
If strOut = "" Then
strOut = rs!ExpenseID
Else
strOut = strOut & ", " & rs!ExpenseID
End If
rs.MoveNext
Loop
If strOut <> "" Then
' MsgBox "Identified expenses: " & strOut
strOut = "ExpenseID IN (" & strOut & ")"
DoCmd.OutputTo acOutputReport, "ExpenseS", acFormatXLS, , , acDialog
End If
End If
DoCmd.Close acForm, "ExportMultiSearch"
DoCmd.Close acReport, "ExpenseS"
The only line I changed was the DoCmd.OutputTo, before you had it as DoCmd.OpenReport "ExpenseS", acViewPreview, , , acDialog
Do you know why it would send all the records from the report when only the filtered ones are showing?
I believe if you Open the report filtered first and then export it will work. I think you can then even open it hidden followed by the export and it will work. Make sure to close the hidden form after the export.
In the above case you are now doing nothing with the filter (strOut). So nothing will happen. In the original openreport case you passed that in as a filter in the docmd.open report "WhereCondition" argument.
The other option is you can modify the sql of the report using the query def. But I think if you open it first, pass in the strOut, then export it will work. Untested.
Private Sub cmdReport_Click()
Dim strOut As String
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
If Me.Filter = "" Or Me.FilterOn = False Then
DoCmd.OpenReport "rptexpensesLineItems", acViewPreview, , , acDialog
Else
If Not (rs.EOF And rs.BOF) Then rs.MoveFirst
Do While Not rs.EOF
If strOut = "" Then
strOut = rs!ExpenseID
Else
strOut = strOut & ", " & rs!ExpenseID
End If
rs.MoveNext
Loop
If strOut <> "" Then
MsgBox "Identified expenses: " & strOut
strOut = "ExpenseID IN (" & strOut & ")"
DoCmd.OpenReport "rptexpensesLineItems", acViewPreview, , strOut, acHidden
DoCmd.OutputTo acOutputReport, "rptExpensesLineItems", acFormatXLS, CurrentProject.Path & "\test.xls"
DoCmd.Close acReport, "rptExpensesLineItems"
MsgBox "report saved as " & CurrentProject.Path & "\text.xls"
End If
End If
End Sub
I also added this line, because it was not working for me.
If Not (rs.EOF And rs.BOF) Then rs.MoveFirst
In order to loop all the records you need to first move to the first record. If not the returned recordset position will be whatever is in the form. I am suprised without having that line there was no problem. My pointer was on the last record so this loop returned nothing. However your code will fail if you try to move first and you actually have no records. So this line checks that you do have records.
Private Sub cmdReport_Click()
Dim strOut As String
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
If Me.Filter = "" Or Me.FilterOn = False Then
DoCmd.OpenReport "rptexpensesLineItems", acViewPreview, , , acDialog
Else
If Not (rs.EOF And rs.BOF) Then rs.MoveFirst
Do While Not rs.EOF
If strOut = "" Then
strOut = rs!ExpenseID
Else
strOut = strOut & ", " & rs!ExpenseID
End If
rs.MoveNext
Loop
If strOut <> "" Then
MsgBox "Identified expenses: " & strOut
strOut = "ExpenseID IN (" & strOut & ")"
DoCmd.OpenReport "rptexpensesLineItems", acViewPreview, , strOut, acHidden
DoCmd.OutputTo acOutputReport, "rptExpensesLineItems", acFormatXLS, CurrentProject.Path & "\test.xls"
DoCmd.Close acReport, "rptExpensesLineItems"
MsgBox "report saved as " & CurrentProject.Path & "\text.xls"
End If
End If
End Sub
I also added this line, because it was not working for me.
If Not (rs.EOF And rs.BOF) Then rs.MoveFirst
In order to loop all the records you need to first move to the first record. If not the returned recordset position will be whatever is in the form. I am suprised without having that line there was no problem. My pointer was on the last record so this loop returned nothing. However your code will fail if you try to move first and you actually have no records. So this line checks that you do have records.
Thanks for all your help on this. I have tried to use this same technique on another form which search/filters just the description and budget code of the line items, it all works good. When I try to open the report the message box indicates the records to be passed through to the report, however the report is opening all the records in the DB, how do I pass the ExpenseID's across to the report? I copied all your queries, reports, and forms and just changed the record source and fields I wanted, as I say it works fine but it is just not passing across the Expense ID to filter the report? Any ideas?
Thanks for all your help on this. I have tried to use this same technique on another form which search/filters just the description and budget code of the line items, it all works good. When I try to open the report the message box indicates the records to be passed through to the report, however the report is opening all the records in the DB, how do I pass the ExpenseID's across to the report? I copied all your queries, reports, and forms and just changed the record source and fields I wanted, as I say it works fine but it is just not passing across the Expense ID to filter the report? Any ideas?
This took me like twenty minutes to figure out why my original code did not work.