Decided to add an Invoice Form. This form saves invoices with unique number, which is associated with a client. This form should filter expenses based on a date range between BeginDate and EndDate.
There is an Expense Form. This form takes individual expenses and saves with unique number. This form is placed on the Invoice Form and should filter when a filter button is clicked after entering the BeginDate and EndDate located on the Invoice Form.
Tried many variables but unlike other forms with subform relationships an error occurs when attempting to filter or all expenses are displayed without filtering.
The question is whether anyone else has solved this issue and if so then how?
It must be a form and not a report because invoice must have unique number, be saved, queried, and calculated later for additional invoices.
Example of date filter with button that executes query of subform:
Example for Expense Form that is placed as subform on Invoice Form:
Example Error message (one of many different messages depending on methods tried):
Other error messages has issue with the WHERE clause when trying to append the query because it does not recognize the #" & [Forms]![frmViewMain]![frmInvoice]![txtBeginDateRange] & "# And #" & [Forms]![frmViewMain]![frmInvoice]![txtEndDateRange] & "# for a query, which is expected:
Example VBA on main form when filter button clicked:
Code:
Private Sub btnFilterExpense_Click()
Dim strSQL As String
Dim strFilter As String
Dim strFiltered As String
strSQL = "SELECT [CaseNum], [ExpenseNum], [ExpenseType], [ExpenseDescription], [ExpenseAmount], [ExpenseQuantity], [ExpenseTotal], [ExpenseDate] FROM tblCaseExpense ORDER BY [ExpenseDate] "
CurrentDb.OpenRecordset strSQL
strFilter = "WHERE [ExpenseStatus] = 'Not Paid' And [ExpenseDate] Between #" & [Forms]![frmViewMain]![frmInvoice]![txtBeginDateRange] & "# And #" & [Forms]![frmViewMain]![frmInvoice]![txtEndDateRange] & "# "
strFiltered = strSQL & strFilter
Me.subfrmQueryCaseExpense.SourceObject = strFiltered
Me.subfrmQueryCaseExpense.Requery
End Sub
The following produces results in subform without error but also without filtering dates:
Code:
Private Sub btnFilterExpense_Click()
Dim strSQL As String
strSQL = "SELECT [CaseNum], [ExpenseNum], [ExpenseType], [ExpenseDescription], [ExpenseAmount], [ExpenseQuantity], [ExpenseTotal], [ExpenseDate] FROM tblCaseExpense ORDER BY [ExpenseDate] "
CurrentDb.OpenRecordset strSQL
CurrentDb.QueryDefs("qryInvoiceExpense").SQL = strSQL
Me.subfrmQueryCaseExpense.SourceObject = "query.qryInvoiceExpense"
End Sub
The following produces error described earlier:
Code:
Private Sub btnFilterExpense_Click()
Dim strSQL As String
Dim strFilter As String
Dim strFiltered As String
strSQL = "SELECT [CaseNum], [ExpenseNum], [ExpenseType], [ExpenseDescription], [ExpenseAmount], [ExpenseQuantity], [ExpenseTotal], [ExpenseDate] FROM tblCaseExpense ORDER BY [ExpenseDate] "
CurrentDb.OpenRecordset strSQL
strFilter = "WHERE [CaseNum] = 'txtCaseNum' And [ExpenseStatus] = 'Not Paid' And [ExpenseDate] Between #" & [Forms]![frmViewMain]![frmInvoice]![txtBeginDateRange] & "# And #" & [Forms]![frmViewMain]![frmInvoice]![txtEndDateRange] & "# "
strFiltered = strSQL & strFilter
CurrentDb.QueryDefs("qryInvoiceExpense").SQL = strFiltered
Me.subfrmQueryCaseExpense.SourceObject = "query.qryInvoiceExpense"
End Sub