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?
LVL 1
BCooperAsked:
Who is Participating?
 
david251Connect With a Mentor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.