jwcolby
jwcolby at colbyconsulting.com
Wed Jun 2 12:27:31 CDT 2010
Ooops, meant to say Fltr() function.
John W. Colby
www.ColbyConsulting.com
Kenneth Ismert wrote:
> Bill Benson, Gustav
>
> I use global functions instead. For your situation, I would use something
> like:
>
> Public Function Jobs_Date(Optional vDate As Variant) As Variant
> Static vParm As Variant
> If Not IsMissing(vDate) Then
> vParm = vDate
> ElseIf IsEmpty(vParm) Then
> vParm = Null ' default
> End If
> Jobs_Date = vParm
> End Function
>
>
>
>
>
>> ---------- Forwarded message ----------
>> From: "Bill Benson \(vbacreations\)" <vbacreations at gmail.com>
>> To: "'Access Developers discussion and problem solving'" <
>> accessd at databaseadvisors.com>
>> Date: Tue, 1 Jun 2010 18:43:56 -0400
>> Subject: [AccessD] When a query refers to a control on a form which is not
>> open - how to supply a default?
>> SELECT TblCIB.RecordID, TblCIB.ShopOrder, TblCIB.ItemNumber FROM TblCIB
>> WHERE (((TblCIB.RecordID)=[Forms]![frmExciterIndividualEntry]![RecordID]))
>>
>> The above sql is causing the user to be prompted for a value of
>> [Forms]![frmExciterIndividualEntry]![RecordID] when that form is not open
>> and/or the control not populated. I would like that when there is no form
>> or
>> control available at runtime, the query just assumes a 0 or "" [default] so
>> that my users are not prompted. Is there some simple [single expression]
>> way
>> to do this short of the workaround I show below, which seems like
>> overkill??
>>
>> How I changed the where clause:
>> WHERE TblCIB.RecordID=
>> GetLatestRecordIDFrom("frmExciterIndividualEntry","RecordID",4)
>>
>>
>> 'In a standard module
>> Function GetLatestRecordIDFrom(strForm As String, CtrlName As String,
>> TypeExpected As Long) As Long
>> Dim Frm As Form
>> Dim Ctrl As control
>> On Error Resume Next
>> Set Frm = Forms(strForm)
>> If Not Frm Is Nothing Then
>> Set Ctrl = Frm.Controls(CtrlName)
>> If Not Ctrl Is Nothing Then
>> Select Case TypeExpected
>> Case Is = dbText
>> GetLatestRecordIDFrom = Nz(Ctrl.Value, "")
>> Case Is = dbLong, Is = dbDouble, Is = dbSingle, Is = dbCurrency
>> GetLatestRecordIDFrom = Nz(Ctrl.Value, 0)
>> Case Is = dbBoolean
>> GetLatestRecordIDFrom = Nz(Ctrl.Value, False)
>> End Select
>> End If
>> End If
>> Exit_Function:
>> End Function
>>
>>
>>
>>
>> ---------- Forwarded message ----------
>> From: "Bill Benson \(vbacreations\)" <vbacreations at gmail.com>
>> To: "'Access Developers discussion and problem solving'" <
>> accessd at databaseadvisors.com>
>> Date: Tue, 1 Jun 2010 19:03:30 -0400
>> Subject: Re: [AccessD] When a query refers to a control on a form which is
>> not open - how to supply a default?
>> My function was incorrect anyway, would have needed to be something which
>> tested not only the control but also the form: I am sure this can be
>> written
>> better but this is not my goal, my goal is a simple way to substitute in
>> the
>> sql when environment does not support the query due to the absence of the
>> open form and/or populated control.
>>
>> Thanks.
>>
>>
>> Function GetLatestRecordIDFrom2(strForm As String, CtrlName As String,
>> TypeExpected As Long)
>> Dim Frm As Form
>> Dim Ctrl As control
>> On Error Resume Next
>> Set Frm = Forms(strForm)
>> If Frm Is Nothing Then
>> Select Case TypeExpected
>> Case Is = dbText
>> GetLatestRecordIDFrom2 = ""
>> GoTo Exit_Function
>> Case Is = dbLong, Is = dbDouble, Is = dbSingle, Is = dbCurrency
>> GetLatestRecordIDFrom2 = 0
>> GoTo Exit_Function
>> Case Is = dbBoolean
>> GetLatestRecordIDFrom2 = False
>> GoTo Exit_Function
>> End Select
>> Else
>> Set Ctrl = Frm.Controls(CtrlName)
>> If Ctrl Is Nothing Then
>> Select Case TypeExpected
>> Case Is = dbText
>> GetLatestRecordIDFrom2 = ""
>> GoTo Exit_Function
>> Case Is = dbLong, Is = dbDouble, Is = dbSingle, Is = dbCurrency
>> GetLatestRecordIDFrom2 = 0
>> GoTo Exit_Function
>> Case Is = dbBoolean
>> GetLatestRecordIDFrom2 = False
>> GoTo Exit_Function
>> End Select
>> Else
>> Select Case TypeExpected
>> Case Is = dbText
>> GetLatestRecordIDFrom2 = Nz(Ctrl.Value, "")
>> Case Is = dbLong, Is = dbDouble, Is = dbSingle, Is = dbCurrency
>> GetLatestRecordIDFrom2 = Nz(Ctrl.Value, 0)
>> Case Is = dbBoolean
>> GetLatestRecordIDFrom2 = Nz(Ctrl.Value, False)
>> End Select
>> End If
>> End If
>>
>> Exit_Function:
>> End Function
>>
>>
>>
>>
>> ---------- Forwarded message ----------
>> From: "Gustav Brock" <Gustav at cactus.dk>
>> To: <accessd at databaseadvisors.com>
>> Date: Wed, 02 Jun 2010 10:35:42 +0200
>> Subject: Re: [AccessD] When a query refers to a control on a form which is
>> not open - how to supply a default
>> Hi Bill
>>
>> You can let your function return Null for "not found", and adjust your SQL
>> like this:
>>
>> SELECT
>> RecordID,
>> ShopOrder,
>> ItemNumber
>> FROM
>> TblCIB
>> WHERE
>> RecordID=Nz(GetLatestRecordIDFrom2("frmExciterIndividualEntry",
>> "RecordID"), [RecordID])
>>
>>
>> Function GetLatestRecordIDFrom2(strForm As String, CtrlName As String) As
>> Variant
>> Dim Frm As Form
>> Dim Ctrl As control
>> On Error Resume Next
>> Set Frm = Forms(strForm)
>> If Frm Is Nothing Then
>> GetLatestRecordIDFrom2 = Null
>> Else
>> Set Ctrl = Frm.Controls(CtrlName)
>> If Ctrl Is Nothing Then
>> GetLatestRecordIDFrom2 = Null
>> Else
>> GetLatestRecordIDFrom2 = Ctrl.Value
>> Set Ctrl = Nothing
>> End If
>> Set Frm = Nothing
>> End If
>>
>> End Function
>>
>> /gustav
>>
>>
>> ---------- Forwarded message ----------
>> From: jwcolby <jwcolby at colbyconsulting.com>
>> To: Access Developers discussion and problem solving <
>> accessd at databaseadvisors.com>
>> Date: Wed, 02 Jun 2010 08:19:49 -0400
>> Subject: Re: [AccessD] When a query refers to a control on a form which is
>> not open - how to supply a default?
>> Bill,
>>
>> I use a function I call "fltr()" to do this. Fltr is a function that I can
>> SET to a value with one call and RETRIEVE a value with another call. I have
>> removed error handling to make it easier to see how it works.
>>
>> The concept is that fltr has TWO parameters. The first parameter is the
>> NAME of the filter to be set or retrieved. The second parameter is OPTIONAL
>> and is the VALUE of the filter to be SET.
>>
>> Filters are stored internally to the function in a static collection, so
>> this one function can store thousands of these filters.
>>
>> So...
>>
>> If you call
>>
>> Fltr "frmExciterRecID",
>> [Forms]![frmExciterIndividualEntry]![RecordID]
>>
>> you SET the VALUE [Forms]![frmExciterIndividualEntry]![RecordID] into the
>> fltr "frmExciterRecID"
>>
>> If you call fltr("frmExciterRecID") you RETRIEVE or READ fltr
>> "frmExciterRecID"
>>
>> So your code can now SET a filter wherever you need one, typically in code
>> in the OnOpen or OnCurrent, and the value can be read back out anywhere you
>> want, but specifically in a query.
>>
>> This allows you to MANUALLY SET a filter value, perhaps in the debug
>> window, and then open the query. Since you set the filter, the query uses
>> the filter and life is good.
>>
>> How it works:
>>
>> The very first time the function is called this code sets up the static
>> collection which holds the filter values.
>>
>> Static mcolFilter As Collection
>>
>> If mcolFilter Is Nothing Then
>> Set mcolFilter = New Collection
>> End If
>>
>> The following code handles the case where you are just reading values back
>> out (not SETTING the filter)
>>
>> If IsMissing(lvarValue) Then
>> On Error Resume Next
>> Fltr = mcolFilter(lstrName)
>> If Err <> 0 Then
>> Fltr = Null
>> End If
>>
>> The following code handles a value being passed IN and if so stores it in
>> the collection:
>>
>> Else
>> On Error Resume Next
>> mcolFilter.Remove lstrName
>> mcolFilter.Add lvarValue, lstrName
>> Fltr = lvarValue
>> End If
>>
>> Notice that we ALWAYS delete the old value and store the new value passed
>> in.
>>
>> The following is the entire filter function
>>
>> Public Function Fltr(lstrName As String, Optional lvarValue As Variant) As
>> Variant
>> Static mcolFilter As Collection
>>
>> 'First pass set the collection up
>> If mcolFilter Is Nothing Then
>> Set mcolFilter = New Collection
>> End If
>>
>> 'lvarValue is an optional parameter.
>> 'if it is missing then we are READING the filter
>> If IsMissing(lvarValue) Then
>> On Error Resume Next
>> Fltr = mcolFilter(lstrName)
>> If Err <> 0 Then
>> Fltr = Null
>> End If
>> 'if it is not missing then we are WRITING a filter value
>> Else
>> On Error Resume Next
>> mcolFilter.Remove lstrName
>> mcolFilter.Add lvarValue, lstrName
>> Fltr = lvarValue
>> End If
>> End Function
>>
>>
>>
>> John W. Colby
>> www.ColbyConsulting.com
>>
>>
>>