ACCESS FEED: RE: [MS_AccessPros] allow user to enter multiple values for query parameter

Selasa, 06 Agustus 2013

RE: [MS_AccessPros] allow user to enter multiple values for query parameter

Michael-

You are correct that a parameter in an Access query can contain only one
value. To search on multiple values, you need to write some VBA code to
build a filter string and then open the report with the WhereCondition
parameter. The best way to do this is to build a report filter form where
the user can enter one or more filters. If the user might select multiple
values, consider using a multi-select list box. You then need VBA code to
"parse" the values and build a filter string to open the report.

Here's some sample code that builds a filter from a multi-select list box:

Private Sub cmdSome_Click()
Dim strWhere As String, varItem As Variant
' Request to edit items selected in the list box
' If no items selected, then nothing to do
If Me!lstCName.ItemsSelected.Count = 0 Then Exit Sub
' Hide me
Me.Visible = False
' Loop through the items selected collection
For Each varItem In Me!lstCName.ItemsSelected
' Grab the EmployeeNumber column for each selected item
strWhere = strWhere & Me!lstCName.Column(0, varItem) & ","
Next varItem
' Throw away the extra comma on the "IN" string
strWhere = Left$(strWhere, Len(strWhere) - 1)
' Open the reservation requests form filtered on the selected employees
strWhere = "[EmployeeNumber] IN (" & strWhere & ")"
DoCmd.OpenForm FormName:="frmReservations", WhereCondition:=strWhere
' Close me
DoCmd.Close acForm, Me.Name
' Make sure new form has the focus
Forms!frmReservations.SetFocus
End Sub

The code builds an IN predicate using all the values selected in the list
box, then opens a target form (could also be a report) using the filter.
For multiple filters, simply add AND and the appropriate filter for other
values.

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of mbentfeld
Sent: Tuesday, August 06, 2013 9:02 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] allow user to enter multiple values for query
parameter

Hello everyone,

I am writing a query for Access front end that ties into a MS SQL database
backend. The query is based on a report originally written in Crystal
Reports that allows users to locate all shop orders for a part (or parts)
that the user specifies, with a given order status and one of several flow
codes from our MRP system. I'm rewriting the report to accommodate a user
request to display a bitmap of a drawing for each part the query returns
(which would be displayed in a report derived from the query). That is why
I'm trying to do this in Access. The status code and the flow codes
searched under would never change. However, the user needs the ability to
search for one part or many parts, and this is normally accomplished with a
parameter in the Crystal Reports version that allows for multiple values to
be entered. Everything I've know (or been able to find) on Access query
parameters indicates only one value can be entered at a time in a query
parameter. Is there a way allow multiple values to be entered for a single
field in an Access query? I'll admit my coding knowledge is rather limited,
but I'll try anything if the solution requires VB coding (or other).

Thanks in advance,

Michael

------------------------------------

Yahoo! Groups Links