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