[AccessD] Required Field in Function

Have you declared xlsBPS as an Excel worksheet or is it an attached
Excel "table"?  I would suspect the referencing xlsBPS.AssetID is giving
you the problem.

Charlotte Foust


-----Original Message-----
From: Hollis,Virginia [mailto:HollisVJ at pgdp.usec.com] 
Sent: Thursday, September 02, 2004 6:28 AM
To: accessd at databaseadvisors.com
Subject: [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> 
 

 

-- 
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com