Mark A Matte
markamatte at hotmail.com
Thu Sep 2 12:58:05 CDT 2004
Just a thought...but could you code the db to import the excel file 'as is'
each time...and then prompt your user to fill the nulls locally?
Thanks,
Mark A. Matte
>From: "Charlotte Foust" <cfoust at infostatsystems.com>
>Reply-To: Access Developers discussion and problem
>solving<accessd at databaseadvisors.com>
>To: "Access Developers discussion and problem
>solving"<accessd at databaseadvisors.com>
>Subject: RE: [AccessD] Required Field in Function
>Date: Thu, 2 Sep 2004 08:57:07 -0700
>
>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
>--
>_______________________________________________
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com