Charlotte Foust
cfoust at infostatsystems.com
Thu Sep 2 10:57:07 CDT 2004
Try testing for a zero length as well.
Charlotte Foust
-----Original Message-----
From: Hollis,Virginia [mailto:HollisVJ at pgdp.usec.com]
Sent: Thursday, September 02, 2004 8:48 AM
To: nlytle at swales.com; accessd at databaseadvisors.com
Subject: RE: [AccessD] Required Field in Function
The strangest thing is happening.
I created a query with the linked Excel table xlsBPS and set the
criteria to show AssetID = IsNull. I added the DCount to the function
and ran it.
What is strange, when I first created the query and ran the query, it
showed which record contained the Null AssetID. After running the append
code, it does not show this record - the query is blank. I don't
understand why it isn't picking up the Null AssetID record anymore. If I
look on the spreadsheet or open the linked table, that field is Null.
Since the query does not show any records containing Null AssetIDs
anymore (even though there is a null record) it runs the append code.
I also tried having the query open so the user can enter the missing
data. I found out if you enter data into the linked Excel table, it
corrupts the spreadsheet file and gives an error message about not being
in a correct file format.
-----Original Message-----
From: Nancy Lytle [mailto:nlytle at swales.com]
Sent: Thursday, September 02, 2004 9:59 AM
To: Hollis,Virginia
Subject: RE: [AccessD] Required Field in Function
I've done similiar things. Anything to control what the user can and
can't do. If the query is being run from a form and you don't want user
in the actual tables then run the IsNull query then open a form you
created based on that query, once thta have completed that you can have
them close or update the recordset and rerun the IsNull and if it
returns 0 records the run the append query.
Heres some pseudo code
If DCount("*", "qryIsNull) >0 then
Open frmDataEntry
Else
Run AppendQuery
End if
On Open of frmDataEntry
frmDataEntry.recordsourcetype = "table/query" frmDataEntry.recordsource
= "qryIsNull"
On Close frmDataEntry
Refresh the form to catch all newly entered data
If DCount("*", "qryIsNull) >0 then
"there are still items to correct"
Return them to finish entering
Else
Run AppendQuery
End if
Nancy
-----Original Message-----
From: Hollis,Virginia [mailto:HollisVJ at pgdp.usec.com]
Sent: Thursday, September 02, 2004 10:45 AM
To: Nancy Lytle
Subject: RE: [AccessD] Required Field in Function
That is fine replying directly, thanks.
That sounds like a good idea; it would show the user exactly what is
wrong too. I will try to play around with that idea. Have you ever tried
it?
Va.
-----Original Message-----
From: Nancy Lytle [mailto:nlytle at swales.com]
Sent: Thursday, September 02, 2004 9:41 AM
To: HollisVJ at pgdp.usec.com
Subject: Re: [AccessD] Required Field in Function
I couldn't reply on the list so here goes:
I may be off base here but why not have an IsNotNull Criteria in the
append, then a second query to pop up a form for all those where the
field is null? Or do the reverse, have it pop up with all the null
fields, require them to be filled in before the append query can run.
----- Original Message -----
From: "Hollis,Virginia" <HollisVJ at pgdp.usec.com>
To: <accessd at databaseadvisors.com>
Sent: Thursday, September 02, 2004 9:28 AM
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
--
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com