[AccessD] Required Field in Function

I removed the Cancel = True, added "Else" after the MsgBox, and moved the
End If to the bottom of the code. But I still receive the RunTime Error 424
Object Required on the If IsNull ... code. Am I missing something else? What
would stop the code from running and what is causing the error?
 
If IsNull(xlsBPS.AssetID) Then
   MsgBox "Asset ID must be completed before downloading"
Else
.......
End If
End If
 
Virginia
________________________________________________________
Virginia
Your test may well be ok but all you're doinmg is setting Cancel=True; you
still carry on and execute the queries. The query execution needs to be on
an Else so that it does not run if the IsNull test is True. Setting
Cancel=True does not stop subsequent code.
 
--
Andy Lacey
http://www.minstersystems.co.uk <http://www.minstersystems.co.uk> 
 
 
 
--------- Original Message --------
From: Access Developers discussion and problem solving
<accessd at databaseadvisors.com
<http://databaseadvisors.com/mailman/listinfo/accessd> >
To: accessD at databaseadvisors.com
<http://databaseadvisors.com/mailman/listinfo/accessd>  <accessD at
databaseadvisors.com <http://databaseadvisors.com/mailman/listinfo/accessd>
>
Subject: [AccessD] Required Field in Function
Date: 02/09/04 12:45
 
 
 I have a function (code below) that is used to append data from a linked
Excel file (xlsBPS). The Excel file is linked to the database as a table.
 There is a column in the Excel file named AssetID - this is a required
field in the database. Yesterday when the user ran the append code the
AssetID was blank in one of the records in the xlsBPS file. This caused all
the records to move up one. For example:
 
 Record 1: AssetID =
             Description: This one
 
 Record 2: AssetID = ABC
             Description: My Name
 
 The results after the append code ran were:
 
 Record 1: AssetID  = ABC
             Description: This one
 
What can I add to the function to check for null fields to ensure the
AssetID is completed, if not, the code does not run? I tried adding a check
(where I added the ***** in the code, but I receive the error, RunTime 424,
Object Required.
 
 Virginia
____________________________________________________________________
 
 Function AppendBPS()
 
 'Appends new data from xlsBPS to tblFailure, tblSystemMain, and
tblFailureTimeSelected.
 
     Beep
 
     Title = "Append new BPS data from Excel"
 
     ' Work Order table import.
 
     Msg = "Your are about to modify data in this database."
     Msg = Msg & " Do you want to continue?"
     DgDef = vbQuestion + vbYesNo + vbDefaultButton1    ' Describe dialog
box.
 
     Response = MsgBox(Msg, DgDef, Title)    ' Get user response.
     If Response = vbYes Then    ' Evaluate response
 
 ********        If IsNull(xlsBPS.AssetID) Then
*******************************
 *******     MsgBox "Asset ID must be completed before
downloading"
 ********    Cancel = True
 ******* End If
 
         DoCmd.SetWarnings False
 
         DoCmd.Echo False, "Appending Data"
 
         DoCmd.Hourglass True
 
         'Append data to tables
 
         DoCmd.OpenQuery "qry_AppendBPS_tblSystemMain", acNormal,
acEdit
 'Open append query for tblSystemMain
 
         DoCmd.OpenQuery "qry_AppendBPS_tblFailures", acNormal,
acEdit 'Open append query for tblFailures
         DoCmd.OpenQuery
"qry_AppendBPS_tblFailureTimeSelected",acNormal, acEdit 'Open
append query for tblFailureTimeSelected
         DoCmd.Echo False, "Appending new data"
 
         DoCmd.Echo True
         DoCmd.Hourglass False
 
         Beep
 
         MsgBox "All done!"
 
         DoCmd.SetWarnings True
 
         DoCmd.OpenForm "frmSystemFailure", acNormal,
"qryStatusPending", , acFormEdit, acWindowNormal
 
     Else
 
        ' You chose No or pressed Enter.
 
     End If
 
 End Function
 
 _______________________________________________
 AccessD mailing list
 AccessD at databaseadvisors.com
<http://databaseadvisors.com/mailman/listinfo/accessd> 
 http://databaseadvisors.com/mailman/listinfo/accessd
<http://databaseadvisors.com/mailman/listinfo/accessd> 
 Website: http://www.databaseadvisors.com <http://www.databaseadvisors.com>