Solved

OpenForm Setting two criteria in the WhereCondition

Posted on 2004-04-02
2
1,438 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
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

856 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