MS Access database support - using a function as the criteria for a field

Author

using a function as the criteria for a field

ChasW

2006-04-08, 7:27 am

I just read these 2 pages. These are most helpful, but leave me with
a question.

http://www.mvps.org/access/queries/qry0005.htm
http://www.mvps.org/access/forms/frm0007.htm

I have a Multi Select Listbox that will define query criteria for a
query that is the record source for another form.

Certain individual selections in this list box may act a label for a
group of other selections, so I am using a VBA function to create the
string that will define the WHERE clause of the SQL statement for the
query.

Quoting a portion of the above link: "Note: You can still use a
parameterized query provided you pass the entire Where clause to it
via code as a parameter. (eg. Have the query reference a hidden
control to which you manually assign the complete WHERE clause using
the following logic.)"

Assuming I understand the above quote correctly, I guess what I am
seeking is more or less syntax for assigning a complete WHERE clause
as opposed to a specific field criteria from a control.

Anybody care to give an example of this?

Chas

Ed Robichaud

2006-04-08, 11:28 am

I've tried a bunch, but the method below seems to be the easiest way to
implement multi-pick combo boxes. It's copied from a very generous MVP, but
I've forgotten who.

Column in Query:

IsSelectedVar("frmMyForm","cmbMyMultiPick",[myField])

Function in module:
====================
====================
=
Function IsSelectedVar( _
strFormName As String, _
strListBoxName As String, _
varValue As Variant) _
As Boolean
'strFormName is the name of the form
'strListBoxName is the name of the listbox
'varValue is the field to check against the listbox
Dim lbo As ListBox
Dim item As Variant
If IsNumeric(varValue) Then
varValue = Trim(Str(varValue))
End If
Set lbo = Forms(strFormName)(s
trListBoxName)
If lbo.ItemsSelected.Count > 0 Then
For Each item In lbo.ItemsSelected
If lbo.ItemData(item) = varValue Then
IsSelectedVar = True
Exit Function
End If
Next
Else
IsSelectedVar = True
End If

End Function
====================
====================
=

<ChasW> wrote in message news:bgse32haos9irah
fq0dn0k2fbov7ll5lle@
4ax.com...
>I just read these 2 pages. These are most helpful, but leave me with
> a question.
>
> http://www.mvps.org/access/queries/qry0005.htm
> http://www.mvps.org/access/forms/frm0007.htm
>
> I have a Multi Select Listbox that will define query criteria for a
> query that is the record source for another form.
>
> Certain individual selections in this list box may act a label for a
> group of other selections, so I am using a VBA function to create the
> string that will define the WHERE clause of the SQL statement for the
> query.
>
> Quoting a portion of the above link: "Note: You can still use a
> parameterized query provided you pass the entire Where clause to it
> via code as a parameter. (eg. Have the query reference a hidden
> control to which you manually assign the complete WHERE clause using
> the following logic.)"
>
> Assuming I understand the above quote correctly, I guess what I am
> seeking is more or less syntax for assigning a complete WHERE clause
> as opposed to a specific field criteria from a control.
>
> Anybody care to give an example of this?
>
> Chas
>
>

Ed Robichaud

2006-04-08, 11:28 am

Due credit to Duane Hookom; here is the original source of the solution that
I quoted.

[url]http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName=[/ url]'Listbox%20with%
20Multiselection%20u
sed%20in%20Query'

"Ed Robichaud" <edrobichaud@wdn.com> wrote in message
news:4437d164$0$3707
$2ff6ac69@news.wdn.com...
> I've tried a bunch, but the method below seems to be the easiest way to
> implement multi-pick combo boxes. It's copied from a very generous MVP,
> but I've forgotten who.
>
> Column in Query:
>
> IsSelectedVar("frmMyForm","cmbMyMultiPick",[myField])
>
>
> Function in module:
> ====================
====================
=
> Function IsSelectedVar( _
> strFormName As String, _
> strListBoxName As String, _
> varValue As Variant) _
> As Boolean
> 'strFormName is the name of the form
> 'strListBoxName is the name of the listbox
> 'varValue is the field to check against the listbox
> Dim lbo As ListBox
> Dim item As Variant
> If IsNumeric(varValue) Then
> varValue = Trim(Str(varValue))
> End If
> Set lbo = Forms(strFormName)(s
trListBoxName)
> If lbo.ItemsSelected.Count > 0 Then
> For Each item In lbo.ItemsSelected
> If lbo.ItemData(item) = varValue Then
> IsSelectedVar = True
> Exit Function
> End If
> Next
> Else
> IsSelectedVar = True
> End If
>
> End Function
> ====================
====================
=
>
>
> <ChasW> wrote in message
> news:bgse32haos9irah
fq0dn0k2fbov7ll5lle@
4ax.com...
>
>

Sponsored Links