[AccessD] Required Field in Function

Maintaining the integrity of data Linked to Excel spreadsheets is often
problematic, since the Excel "databases" are subject to manipulation by the
user (ie. blank cells, changing column headings, inserting rows, text
instead of numbers, etc. One way I reduce these problems is by having a
hidden sheet that is linked to the data input cells/sheets. This sheet can
have a "validation" field (column) with an excel formula that tests for
conditions such as all fields being filled, no text where numbers should be,
etc. If the "record" fails the test the formula result can be whatever code
you wish. Since you can create your own specialized Excel formula these
validation routines can be quite elaborate. When you are reading these Excel
"records" into the Access database you can test for these codes and handle
the record appropriately. Just a thought.
Jim Hale

-----Original Message-----
From: Hollis,Virginia [mailto:HollisVJ at pgdp.usec.com]
Sent: Thursday, September 02, 2004 8: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