Hollis,Virginia
HollisVJ at pgdp.usec.com
Thu Sep 2 10:47:38 CDT 2004
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