[AccessD] Required Field in Function

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



--------- Original Message --------
From: Access Developers discussion and problem solving
<accessd at databaseadvisors.com>
To: accessD at databaseadvisors.com <accessD at databaseadvisors.com>
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
>
>         'DoCmd.Quit
>
>
>
>     Else
>
>        ' You chose No or pressed Enter.
>
>     End If
>
>
>
> End Function
>
> --
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
>
>
>
>

________________________________________________
Message sent using UebiMiau 2.7.2