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

Hi Bill

That could be:

SELECT 
  RecordID, 
  ShopOrder, 
  ItemNumber 
FROM 
  TblCIB
WHERE
  RecordID=Nz(GetLatestRecordIDFrom2("frmExciterIndividualEntry", "RecordID"), 0)

/gustav


>>> vbacreations at gmail.com 02-06-2010 13:09 >>>
Thanks Gustav. You are right. I am still hoping there is a way to have cake
and eat it too -- to refer to a control on a form but if it does not exist,
to default to null?

-----Original Message-----
From: accessd-bounces at databaseadvisors.com 
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Wednesday, June 02, 2010 4:36 AM
To: accessd at databaseadvisors.com 
Subject: Re: [AccessD] When a query refers to a control on a form which
isnot 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


>>> vbacreations at gmail.com 02-06-2010 01:03 >>>
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