Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1457
  • Last Modified:

OpenForm Setting two criteria in the WhereCondition

I have an Access Data Project attached to an SQL2000 DB.  I need to open a form based on two criteria.  First criteria is pulled from an open form.  The second is prompted by the VBA.  Code follows:
Private Sub Pmt_Info_Click()
On Error GoTo Err_Pmt_Info_Click

    Dim DocName As String
    Dim LinkCriteria As String
    Dim LinkCriteria2 As String
    Dim Message, Title, Default, MyValue
    Message = "Enter Season"    ' Set prompt.
Title = "Season Filter"    ' Set title.
Default = "9"    ' Set default.
MyValue = InputBox(Message, Title, Default)
   
    DocName = "Payment Data Entry"
    LinkCriteria = "[Account Number] = " & Forms![Season Ticket Holders]![Account Number]
    LinkCriteria2 = "[season] = " & MyValue
    DoCmd.OpenForm DocName, , , LinkCriteria And LinkCriteria2
   
Exit_Pmt_Info_Click:
    Exit Sub

Err_Pmt_Info_Click:
    MsgBox Error$
    Resume Exit_Pmt_Info_Click
   
End Sub

I thought this would allow me to open the form with a filter to a specific account number and  a specific season.  This code however gives me a type mismatch when run.  If I use either criteria alone the form opens fine except it pulls either all accounts for a specific season or all seasons for a specific account.  Can anyone help me pull  a specific season for a specific account?
0
BCooper
Asked:
BCooper
1 Solution
 
david251Commented:
change

 LinkCriteria = "[Account Number] = " & Forms![Season Ticket Holders]![Account Number]
    LinkCriteria2 = "[season] = " & MyValue
 DoCmd.OpenForm DocName, , , LinkCriteria And LinkCriteria2

to

 LinkCriteria = "[Account Number] = " & Forms![Season Ticket Holders]![Account Number]
 LinkCriteria = LinkCriteria & " and [season] = " & MyValue
 DoCmd.OpenForm DocName, , , LinkCriteria

-David251
0
 
Jonathan KellyCommented:
i dont know if what your attempting is possible but try

LinkCriteria2 = "[season] = " & chr(34) & MyValue & chr(34)
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now