Access Form filter with two fields

Murray Brown
Murray Brown used Ask the Experts™

I am using the following Access VBA code to open a form with a filter on the sub form. I want include a second field
in the filter so "[To Licence Number]=" & oLicenceNumberPK
Would this be .Filter="[From Licence Number]=" & oLicenceNumberPK & " And [To Licence Number]=" & oLicenceNumberPK

    DoCmd.OpenForm "f_Transfers", acNormal
    With Forms!f_Transfers!f_TransferTransactions.Form
        .Filter = "[From Licence Number]=" & oLicenceNumberPK
        .FilterOn = True
    End With
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012
Yes, assuming both are numeric.  But put  it in a string:

strFilter = "[From Licence Number]=" & oLicenceNumberPK & " AND [To Licence Number]=" & oLicenceNumberPK

    DoCmd.OpenForm "f_Transfers", acNormal
    With Forms!f_Transfers!f_TransferTransactions.Form
        .Filter = strFilter
        .FilterOn = True
    End With

And I like to put the AND's, OR's, BETWEEN, etc in caps so their easy to spot in the statement.

 By doing all this, it's easy to debug because you can put a breakpoint on the .Filter line and check the value of strFilter to see if you have the syntax and values correct.

ste5anSenior Developer

First of all: Is your data model correct?  [From Number] = oNumberPK  And [To Number] = oNumberPK looks a bit strange.

Then: The name of the subform control should be different then the name of the form it contains. In other words its naming schema should be different. E.g. sfmTransactions for the control and f_Transfer_Transactions for the form.

I would move the filter code to the transfer form:

Option Compare Database
Option Explicit

Private Sub Form_Load()

  On Local Error Resume Next

  Dim LicenceNumber As Long

  If Len(Trim(Me.Form.OpenArgs & "")) > 0 Then
    LicenceNumber = Me.Form.OpenArgs
    If Err.Number = 0 Then
      sfmTransactions.Form.Filter = "[From Licence Number] = " & LicenceNumber & " AND [To Licence Number] = " & LicenceNumber
      sfmTransactions.Form.FilterOn = True
      MsgBox "Incorrect pararmeter.", vbWarning + bOkOnly
    End If
  End If

End Sub

Open in new window

And open it by:

DoCmd.OpenForm "f_Transfers", , , , , , oLicenceNumberPK

Open in new window

Distinguished Expert 2017

You have another open thread on this topic.  Why not close that one before opening a new one?
Murray Developer


Thanks Jim

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial