Gustav Brock
Gustav at cactus.dk
Wed Jun 2 06:23:59 CDT 2010
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