Multiple Values using Form

[Guest]

Access Experts:

I have a form with an unbound text box that I want to be able to pull
multiple values at the same time.

The field is job code and I want to be able to pull multiple job codes
separated by comma (i.e., 04264, 04265) and the query/report will give me the
results for these two job codes.

I have been able to do this using parameters within a query, but I have not
been able to do it using a form (i.e., InStr([Enter Job
Code],[tfvjobcodes].[JobCode])

Any help is appreciated.

 

[Roger Carlson]

Why not use a multi-select list box? On my website
(www.rogersaccesslibrary.com), is a small Access database sample called
"CreateQueries2.mdb" which illustrates how to do this with a multi-select
listbox.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Guest]

Scott,

Roger's method is robust and versatile, and would be my preference, however,
some people like entering text in a textbox. If that fits your users, the
following parses a list separated by commas into an array and builds a Where
clause to pass to the OpenForm method. The Trim function gets rid of any
unwanted spaces the user may have entered.

Note there is no error-handling code.

Dim strDocName As String
Dim strWhere As String
Dim astrCriteria() As String
Dim intI As Integer

strDocName = "YourForm"

If Nz(Me![txtYourTextbox]) <> 0 Then

' Split textbox value into a string array
astrCriteria = Split(Me![txtYourTextbox], ",")
strWhere = ""

' Process each array element, adding it to the Where condition
' Strip off any leading or trailing spaces with Trim
For intI = 0 To UBound(astrCriteria)
strWhere = strWhere & "[JobCode]=" & "'" &
Trim(astrCriteria(intI)) & "' OR "
Next intI

' Strip off last " OR "
strWhere = Left(strWhere, Len(strWhere) - 4)

' Reopen form with Where condition
DoCmd.OpenForm strDocName, , , strWhere
Else
MsgBox "No criteria entered."
End If

Hope that helps.
Sprinke