[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


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