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

Bill Benson (vbacreations) vbacreations at gmail.com
Tue Jun 1 18:03:30 CDT 2010


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




More information about the AccessD mailing list