Filter Customer Details

[adam]

I'm trying to figure out how to filter the customers list sheet by using the following expression:

Between [Forms]![Customers List]![txtStartDate] And [Forms]![ Customers List]![txtEndDate]

Should this be applied to the query table?

[HansV]

You can use code like this (for example in the On Click code of a command button on the form):

Me.Filter = "Between [Forms]![Customers List]![txtStartDate] And [Forms]![ Customers List]![txtEndDate]"
Me.FilterOn = True

[adam]

When tried as

Code: Select all

Private Sub Search_Click()
Me.Filter = "Between [Forms]![Customers List]![txtStartDate] And [Forms]![ Customers List]![txtEndDate]"
Me.FilterOn = True
End Sub

I'm getting the message in the attached image?

[HansV]

Sorry, I forgot to include the field name. Assuming that you want to filter on Date Reg, change

Me.Filter = "Between [Forms]![Customers List]![txtStartDate] And [Forms]![ Customers List]![txtEndDate]"

to

Me.Filter = "[Date Reg] Between [Forms]![Customers List]![txtStartDate] And [Forms]![ Customers List]![txtEndDate]"

If you want to filter on Date of Birth, use that instead of Date Reg.

[adam]

In the hope to print either all or filtered records I've modified the code given by you in post 25451 and the modification is as follows.

Code: Select all

Private Sub cmdReport_Click()
  On Error GoTo ErrHandler
  ' Get out if Serial is blank
  If IsNull(Me.Serial) Then
    MsgBox "There is no current order!", vbExclamation
    Exit Sub
  End If
  ' Save the record if it has been modified
  If Me.Dirty Then
    RunCommand acCmdSaveRecord
  End If
  ' Open the report for the current order
  DoCmd.OpenReport ReportName:="Customers", View:=acViewPreview, _
    WhereCondition:="Serial=" & Me.Serial
  Exit Sub

ErrHandler:
  If Err = 2501 Then
    ' Report canceled, no need to do anything
  Else
    ' Display error message
    MsgBox Err.Description, vbExclamation
  End If
End Sub

The whole intention is to open up the report and display records whether the Customers List is filtered or in whole list. But the modification shows blank report is open unfiltered and when filtered even with either three rows or four rows it shows only the top row.

What may be the reason for this?
Note: report showing blank when the data sheet is not filtered is because the code is designed to show filtered records. Right?

[HansV]

This code is intended to open the report with only the current record from the form. The reason for this is that you explicitly asked for it. You cannot expect the code to transform itself magically if you want something else than you asked previously.

If I understand you correctly, you want the report to show the same records as the form. That means that you have to apply the same filter to the report as has been applied to the form. This code should do that:

Code: Select all

Private Sub cmdReport_Click()
  Dim strFilter As String
  On Error GoTo ErrHandler
  ' Save the record if it has been modified
  If Me.Dirty Then
    RunCommand acCmdSaveRecord
  End If
  ' Get filter
  If Me.FilterOn Then
    strFilter = Me.Filter
  End If
  ' Open the report
  DoCmd.OpenReport ReportName:="Customers", View:=acViewPreview, _
    WhereCondition:=strFilter
  Exit Sub

ErrHandler:
  If Err = 2501 Then
    ' Report canceled, no need to do anything
  Else
    ' Display error message
    MsgBox Err.Description, vbExclamation
  End If
End Sub

[adam]

Yes Hans. You understood me correctly. The modified code has done the job.

[adam]

In the customers list the data rows get added as the last row on top instead of the last record being aged to the last row.

But in the customers table the rows get added accordingly, meaning the last record gets added to the last row and so.

How could the same be made in the form customers list?

[HansV]

You have specified that the record source of the form, tblCustomers Query, is sorted ascending by Customer Name. If you want it (and hence the form) to be sorted another way, open the query in design view and specify the sort order that you want.

x299.png

[adam]

Hans. I'm trying to sort by the "RegDate". Meaning the last date on the bottom and the first date on top?

[HansV]

Clear the sort order from Customer Name.
Add the Reg Date field to the query grid.
Set the sort order for this field to ascending.
Clear the Show check box for this field to prevent it from being displayed twice (it is already included in tblCustomers.*)

[adam]

It works fine now. Thanks for the help, Hans.