[AccessD] combo box 101

I usually do this kind of thing with a function which is set elsewhere and that function is used in 
the where clause of the query.  In fact I have a custom function which I call Fltr() because I first 
started using it for this purpose.


'
'Fltr takes two parameters, the filter name and the filter value.
'
'The first syntax can be used to set the filter value:
'
'fltr "MyFltr1", MyFltrValue
'
'The filter lstrName is used as the key into the collection, i.e. when lvarValue
'is stored, it is stored with a key of lstrName.
'
'The second syntax can be used to retrieve the value of the filter:
'
'fltr("MyFltr1")
'
'The fact that the second parameter is Optional allows us to check whether a value
'has been passed in.  If no value is passed in, then the assumption is that the filter
'is expecting to return a value.
'
'Because the filter uses a collection internally to save the values, this single
'function can store up to 32K different filter values.
'
'Because lvarValue is a variant, the value stored can be pretty much anything.
'In fact it is necessary to use ctl.VALUE if you want to store an unchanging value
'from a control, since passing in a pointer to a control will then return the
'control, whose value may change over time.
'
Public Function Fltr(lstrName As String, Optional lvarValue As Variant) As Variant
On Error GoTo Err_Fltr
Static mcolFilter As Collection
Static blnFltrInitialized As Boolean

     If Not blnFltrInitialized Then
         Set mcolFilter = New Collection
         blnFltrInitialized = True
     End If
     If IsMissing(lvarValue) Then
         On Error Resume Next
         Fltr = mcolFilter(lstrName)
         If Err <> 0 Then
             Fltr = Null
         End If
     Else
         On Error Resume Next
         mcolFilter.Remove lstrName
         mcolFilter.Add lvarValue, lstrName
         Fltr = lvarValue
     End If
Exit_Fltr:
Exit Function
Err_Fltr:
         MsgBox Err.Description, , "Error in Function basFltrFunctions.Fltr"
         Resume Exit_Fltr
     Resume 0    '.FOR TROUBLESHOOTING
End Function


John W. Colby
www.ColbyConsulting.com

On 8/10/2011 7:13 PM, Darryl Collins wrote:
> I would even go the extra step and say dynamic as you will populate
> different values based on a user choice somewhere else.  It is easy to do
> this in the VBA code and very clear to read.
>
> I much prefer to write the in SQL as it makes debugging much easier as you
> can see immediately what is going on.  If you are using a query, or worse,
> nested queries, then I find it a real PITA to get to the source of the data.
> But we are all different and like thing different ways.  No right or wrong
> really I guess.
>
>
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
> Sent: Thursday, 11 August 2011 3:17 AM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] combo box 101
>
> I call SQL generated by VBA code "dynamic code" because it is "dynamically
> generated" as opposed to
> generated by the developer at design time.
>
> John W. Colby
> www.ColbyConsulting.com
>
> On 8/10/2011 12:57 PM, William Benson wrote:
>> John what does "dynamic SQL in the control itself" mean?
>> On Aug 10, 2011 2:00 AM, "jwcolby"<jwcolby at colbyconsulting.com>   wrote:
>>> I find all of these arguments valid however I also use Rick Fisher's Find
>> and Replace which can find
>>> unused objects and allow me to delete them. When you start manipulating
>> the query in code then
>>> dynamic sql in the control itself seems superior in most cases.
>>>
>>> John W. Colby
>>> www.ColbyConsulting.com
>>>
>>> On 8/9/2011 9:44 PM, Stuart McLachlan wrote:
>>>> On 9 Aug 2011 at 21:04, William Benson (VBACreations. wrote:
>>>>
>>>>> I think those who are comfortable with a lot of queries in their
>>>>> database -- which there is no easy way to tell where that query is
>>>>> being used, either in rowsources or in dependent queries ... probably
>>>>> work in very stable object environments.
>>>>>
>>>>
>>>>
>>>> That's my main concern too.
>>>>
>>>> If you only use queries in VBA, it is easy to search and tabulate all
>> occurences of a query
>>>> name so that you can easily determine all the places it is used. That
>> means that you can
>>>> ensure that it is safe to modify/delete queries.
>>>>
>>>> If however you use them as the source of various controls/forms/reports
>> it is MUCH harder to
>>>> determine whether it is safe to modify/delete a query.
>>>>
>>>> 1. I've deleted this combobox on this form. Can I delete the query that
> I
>> used to populate it or
>>>> is it used by another combobox somewhere on another form/report?
>>>>
>>>> 2. Users now want this combobox on this form sorted by firstname instead
>> of lastname.
>>>> Can I safely change the sort order of the query - Is the same query used
>> in another
>>>> combobox on another form?
>>>> Do I have to create a new one query for this combobox or is there
> another
>> query somewhere
>>>> already that does this which I can use instead?
>>>> If I use a different query, can I safely delete the old one or is it
>> still in use elsewhere?
>>>>
>>>> 3. There have been a number of changes made to various components of the
>> application.
>>>> Which of these queries are still in use somewhere and which should be
>> deleted?
>>>>
>>>>
>>> --
>>> AccessD mailing list
>>> AccessD at databaseadvisors.com
>>> http://databaseadvisors.com/mailman/listinfo/accessd
>>> Website: http://www.databaseadvisors.com