[AccessD] When a query refers to a control on a form which is not open - how to supply a default?

Kenneth Ismert kismert at gmail.com
Wed Jun 2 12:21:50 CDT 2010


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
>
>
>



More information about the AccessD mailing list