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