[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 17:43:56 CDT 2010


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




More information about the AccessD mailing list