Solved

OpenForm Setting two criteria in the WhereCondition

Posted on 2004-04-02
2
1,446 Views
Last Modified: 2012-05-04
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
Comment
Question by:BCooper
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 19

Accepted Solution

by:
david251 earned 175 total points
ID: 10741689
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
 
LVL 7

Expert Comment

by:Jonathan Kelly
ID: 10741706
i dont know if what your attempting is possible but try

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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question