ACCESS FEED: Re: [MS_AccessPros] #deleted in a field value

John,

That's simple enough to mark one as inactive. I always figured (maybe incorrectly) that an archive record would be moved to a different table, maybe called archive.

Russ

--- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@...> wrote:
>
> Russ-
>
> The basic technique is to add a column called "Deleted" or "Obsolete" to
> every table for which you want to do this. When a user requests to delete a
> record, your code simply sets that field to True and saves the record. For
> all your active forms, use a Record Source that includes criteria WHERE
> Deleted = False so that you display only active records. Oh, and when a
> user requests a deletion, you Requery the form after setting the flag so the
> user no longer sees the record.
>
> John Viescas, Author
> Microsoft Access 2010 Inside Out
> Microsoft Access 2007 Inside Out
> Microsoft Access 2003 Inside Out
> Building Microsoft Access Applications
> SQL Queries for Mere Mortals
> http://www.viescas.com/
> (Paris, France)
>
>
>
> -----Original Message-----
> From: MS_Access_Professionals@yahoogroups.com
> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of ghsclass65
> Sent: Wednesday, May 01, 2013 2:08 AM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: Re: [MS_AccessPros] #deleted in a field value
>
> Bill
>
> Actually a pretty good idea. I would really like to mark or move a
> "customer" to an Archive file, but as of today am not quite sure how to do
> it.
>
> Russ
>
> --- In MS_Access_Professionals@yahoogroups.com, "Bill Mosca" <wrmosca@>
> wrote:
> >
> > Russ
> >
> > You could also write a routine that would mark the customer as inactive
> and set up your forms and queries to ignore all inactive customers. That's
> how I do it. My users never delete parent records.
> >
> > Bill
> >
> > --- In MS_Access_Professionals@yahoogroups.com, "ghsclass65" <dyspoz2@>
> wrote:
> > >
> > > John and Bill,
> > >
> > > The databases I've built do not have thousands of records for each
> "customer". In addition, when a delete function occurs a warning is always
> given in the form. I simply cannot be there to babysit the upkeep of a db
> and every deletion, but build in enough precautions to avoid mistakes that
> might be made. If cascading updates and deletions are not allowed, I would
> have to look at these things weekly or possibly daily, and I just want to
> build one that can be manageable by the user and not require my frequent
> attention.
> > >
> > > Russ
> > >
> > >
> > >
> > > --- In MS_Access_Professionals@yahoogroups.com, "Bill Mosca" <wrmosca@>
> wrote:
> > > >
> > > > John-
> > > > Exactly what I was getting at. It's a rare case when I want to allow
> deletion of a parent when child records exist. I'd rather force parent and
> child records to be marked as inactive.
> > > >
> > > > Bill
> > > >
> > > > --- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@>
> wrote:
> > > > >
> > > > > Russ-
> > > > >
> > > > > I think Bill is suggesting that code in forms should to the
> > > > > checking and deleting after prompting the user. Consider the
> > > > > relationship Customer -> Order -> OrderDetails. If a user
> > > > > deletes a customer in a database with Cascade Delete turned on,
> > > > > he or she may not realize that perhaps thousands of rows will
> > > > > also disappear. I personally use Cascade Delete often, but then
> > > > > I also write code to check and inform the user in the Delete
> > > > > event of every form. Something like: "This action will
> > > > > permanently delete customer XXXX along with 540 Orders and 1,659
> Order Detail records. Are you SURE you want to proceed?"
> > > > >
> > > > > John Viescas, Author
> > > > > Microsoft Access 2010 Inside Out Microsoft Access 2007 Inside
> > > > > Out Microsoft Access 2003 Inside Out Building Microsoft Access
> > > > > Applications SQL Queries for Mere Mortals
> > > > > http://www.viescas.com/ (Paris, France)
> > > > >
> > > > >
> > > > >
> > > > > -----Original Message-----
> > > > > From: MS_Access_Professionals@yahoogroups.com
> > > > > [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of
> > > > > ghsclass65
> > > > > Sent: Monday, April 29, 2013 4:43 PM
> > > > > To: MS_Access_Professionals@yahoogroups.com
> > > > > Subject: Re: [MS_AccessPros] #deleted in a field value
> > > > >
> > > > > Bill,
> > > > >
> > > > > I do not understand your statement that relationships should not
> > > > > have cascading updates/deletes turned on. I build all my db
> > > > > that way and if I do not turn that feature on, then when someone
> > > > > deletes a record, it's related records in the connected tables
> > > > > are not deleted resulting in records that are in limbo with no
> corresponding records in other tables.
> > > > >
> > > > > Russ
> > > > >
> > > > >
> > > > >
> > > > > --- In MS_Access_Professionals@yahoogroups.com, "Bill Mosca"
> > > > > <wrmosca@>
> > > > > wrote:
> > > > > >
> > > > > > Ray
> > > > > >
> > > > > >
> > > > > >
> > > > > > The user who did not delete a record would be the one who sees
> > > > > > the #deleted. It wouldn't cause chaos. User1 would just have
> > > > > > to refresh the
> > > > > form.
> > > > > >
> > > > > >
> > > > > >
> > > > > > Tampering with data directly in a table can be dangerous.
> > > > > > Users should have no way of opening tables. And relationships
> > > > > > should not have cascading updates/deletes turned on. Cascading
> > > > > > stuff should only be done by the DB administrator should it ever
> be necessary.
> > > > > >
> > > > > >
> > > > > >
> > > > > > When I mentioned the problem with network connectivity I was
> > > > > > saying the record itself could be corrected. By backing out of
> > > > > > the form, compacting the db and then opening the form again
> > > > > > to re-enter the data usually cleans up the corruption.
> > > > > >
> > > > > >
> > > > > >
> > > > > > Regards,
> > > > > >
> > > > > > Bill
> > > > > >
> > > > > >
> > > > > >
> > > > > > From: MS_Access_Professionals@yahoogroups.com
> > > > > > [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of
> > > > > > Ray
> > > > > > Sent: Saturday, April 27, 2013 9:54 AM
> > > > > > To: MS_Access_Professionals@yahoogroups.com
> > > > > > Subject: Re: [MS_AccessPros] #deleted in a field value
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > > Hi Bill
> > > > > >
> > > > > > Thanks for that. The part of the database that is having these
> > > > > > errors has the structure tblContacts has many tblContactNotes.
> > > > > > If User1 was using the form correctly (form for Contact,
> > > > > > subform for Notes) whilst
> > > > > > User2 went directly to the tblContactNotes and deleted a
> > > > > > record - would that cause chaos? Perhaps even #deleted?
> > > > > >
> > > > > > If I can come up with a plausible reason as to why some
> > > > > > records are being corrupted (data lost and replaced with
> > > > > > #deleted) I'm sure that I can change this culture of tampering
> > > > > > with the tables and lock the database
> > > > > down.
> > > > > >
> > > > > > Finally, you said that network connectivity could be easily
> > > > > > remedied - is this an Access thing or a network server thing?
> > > > > > Where should I start to
> > > > > look?
> > > > > >
> > > > > > Thanks for your help with this.
> > > > > > Ray
> > > > > >
> > > > > > --- In MS_Access_Professionals@yahoogroups.com
> > > > > > <mailto:MS_Access_Professionals%40yahoogroups.com> , "Bill Mosca"
> > > > > > <wrmosca@>
> > > > > > wrote:
> > > > > > >
> > > > > > > Ray
> > > > > > >
> > > > > > > The first person to open the database is not creating a lock
> file.
> > > > > > > It is a
> > > > > > locking file. This file is used by the JET/ACE engine to lock
> > > > > > records that are being edited, nothing more as long as the
> > > > > > form in use has it's record locking set to Edited Record. That
> > > > > > keeps more than one person at a time from changing a record.
> > > > > > >
> > > > > > > This is not the same as opening the database exclusively. If
> > > > > > > the first person
> > > > > > opens it exclusively or opens an object in design mode
> > > > > > everyone else is locked out. Exclusive mode actually locks out
> > > > > > all others from the
> > > > > entire db file.
> > > > > > Splitting the database and making sure each user is using his
> > > > > > own copy of the front end will still protect the records, but
> > > > > > will not lock others out of the back end. That is because the
> > > > > > back end is never opened
> > > > > directly by a user.
> > > > > > >
> > > > > > > Your multi-user db would benefit from splitting. It's very
> > > > > > > rare that a back
> > > > > > end table gets corrupt. Sometimes a record will corrupt due to
> > > > > > loss of network connectivity, but that sort of thing is easily
> remedied.
> > > > > > >
> > > > > > > Bill
> > > > > > >
> > > > > > > --- In MS_Access_Professionals@yahoogroups.com
> > > > > > <mailto:MS_Access_Professionals%40yahoogroups.com> , "Ray"
> > > > > > <rayfrew@>
> > > > > wrote:
> > > > > > > >
> > > > > > > > Hi Bill
> > > > > > > >
> > > > > > > > The sad situation is that the database is not split, it's
> > > > > > > > just one file on a
> > > > > > server. Two people use it during the day by using Access to
> > > > > > open the mdb
> > > > > file.
> > > > > > The first person to open the database will create the lock
> > > > > > file - I'm not sure what happens when the next person opens
> > > > > > the database (whilst the first person is still using it).
> > > > > > Another aspect is that one person in India connects to the server
> overnight and does some data input.
> > > > > > > >
> > > > > > > > I've suggested that the database be split but so far have
> > > > > > > > been met with
> > > > > > "Why? Everything's working and not causing a problem" - well
> > > > > > now, perhaps
> > > > > it is.
> > > > > > Especially as one of the errors says that an update cannot be
> > > > > > done because someone else is updating - yet there is only one
> > > > > > person using the
> > > > > database.
> > > > > > > >
> > > > > > > > If I split the database for them (data on server, forms
> > > > > > > > etc on
> > > > > > > > clients) will
> > > > > > the first person to open the database still create a lock file
> > > > > > and be the "owner" or does it work differently? I know that
> > > > > > splitting a database is good practice but can you give me any
> > > > > > pointers to the types of problems splitting solves?
> > > > > > > >
> > > > > > > > Many thanks
> > > > > > > > Ray
> > > > > > > >
> > > > > > > > --- In MS_Access_Professionals@yahoogroups.com
> > > > > > <mailto:MS_Access_Professionals%40yahoogroups.com> , "Bill Mosca"
> > > > > > <wrmosca@>
> > > > > > wrote:
> > > > > > > > >
> > > > > > > > > Ray
> > > > > > > > > >I don't think that an index was removed in this case
> > > > > > > > > >because the users wouldn't know how to. However, it is
> > > > > > > > > >more likely that there was a network problem -
> > > > > > > > > >apparently that happens occasionally.
> > > > > > > > >
> > > > > > > > > I agree. Do you have the database in a split
> > > > > > > > > configuration with the tables
> > > > > > on a server and the forms, reports, queries etc on the users'
> > > > > > local drive? That would immensely reduce future
> > > > > > corruption...at least as far as the forms and queries are
> concerned .
> > > > > > > > >
> > > > > > > > > Bill
> > > > > > > > >
> > > > > > > > >
> > > > > > > > > --- In MS_Access_Professionals@yahoogroups.com
> > > > > > <mailto:MS_Access_Professionals%40yahoogroups.com> , "Ray"
> > > > > > <rayfrew@>
> > > > > wrote:
> > > > > > > > > >
> > > > > > > > > > Hi Roberto
> > > > > > > > > >
> > > > > > > > > > I don't think that an index was removed in this case
> > > > > > > > > > because the users
> > > > > > wouldn't know how to. However, it is more likely that there
> > > > > > was a network problem - apparently that happens occasionally.
> > > > > > > > > >
> > > > > > > > > > Things have moved forward now, because the database
> > > > > > > > > > has been recovered
> > > > > > from a backup, data is being rekeyed and another backup is
> > > > > > about to be
> > > > > taken.
> > > > > > Probably best to take more than one eh?
> > > > > > > > > >
> > > > > > > > > > Thanks for your help.
> > > > > > > > > > Ray
> > > > > > > > > >
> > > > > > > > > > --- In MS_Access_Professionals@yahoogroups.com
> > > > > > <mailto:MS_Access_Professionals%40yahoogroups.com> , "RobertoARV"
> > > > > > <robertoarv@>
> > > > > > wrote:
> > > > > > > > > > >
> > > > > > > > > > > Sometimes when it happens , an index is removed,
> > > > > > > > > > > then you need to
> > > > > > delete
> > > > > > > > > > > records showing deleted# mark , run a compact
> > > > > > > > > > > database , recreate the
> > > > > > index
> > > > > > > > > > > and then create a blank database and import all
> > > > > > > > > > > objects from fixed database.
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > > > From: MS_Access_Professionals@yahoogroups.com
> > > > > > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > > > > > > > > > [mailto:MS_Access_Professionals@yahoogroups.com
> > > > > > <mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf
> > > > > > Of RobertoARV
> > > > > > > > > > > Sent: Thursday, April 25, 2013 11:28 AM
> > > > > > > > > > > To: MS_Access_Professionals@yahoogroups.com
> > > > > > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > > > > > > > > > Subject: RE: [MS_AccessPros] #deleted in a field
> > > > > > > > > > > value
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > > > I have seen this for years with this database ,
> > > > > > > > > > > often happens when you migrate same database to
> > > > > > > > > > > another upper version, you need to install
> > > > > > all the
> > > > > > > > > > > Microsoft jet service patches. it also occurs when
> > > > > > > > > > > you edit same
> > > > > > record in 2
> > > > > > > > > > > computers , it shows deleted in other computer but
> > > > > > > > > > > it get fix when you
> > > > > > run a
> > > > > > > > > > > requery in form or simply close form and open it
> > > > > > > > > > > again. it happens too
> > > > > > when
> > > > > > > > > > > a network disconnect occurs when you are editing a
> record.
> > > > > > > > > > >
> > > > > > > > > > > From: MS_Access_Professionals@yahoogroups.com
> > > > > > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > > > > > > > > > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > > > > > > > > > [mailto:MS_Access_Professionals@yahoogroups.com
> > > > > > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > > > > > > > > > <mailto:MS_Access_Professionals%40yahoogroups.com> ]
> > > > > > > > > > > On Behalf Of Bill
> > > > > > Mosca
> > > > > > > > > > > Sent: Thursday, April 25, 2013 9:39 AM
> > > > > > > > > > > To: MS_Access_Professionals@yahoogroups.com
> > > > > > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > > > > > > > > > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > > > > > > > > > Subject: RE: [MS_AccessPros] #deleted in a field
> > > > > > > > > > > value
> > > > > > > > > > >
> > > > > > > > > > > Ray
> > > > > > > > > > >
> > > > > > > > > > > The most likely cause is a corrupted index. It might
> > > > > > > > > > > have been saved
> > > > > > if you
> > > > > > > > > > > imported it into a blank database (which sometimes
> > > > > > > > > > > fixes bad
> > > > > indices).
> > > > > > I'm
> > > > > > > > > > > glad
> > > > > > > > > > > there was a backup, though. That was good thinking
> > > > > > > > > > > on your friend's
> > > > > > side.
> > > > > > > > > > >
> > > > > > > > > > > Regards,
> > > > > > > > > > > Bill Mosca,
> > > > > > > > > > > Founder, MS_Access_Professionals That'll do IT
> > > > > > > > > > > <http://thatlldoit.com/> http://thatlldoit.com MS
> > > > > > > > > > > Access MVP
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > <https://mvp.support.microsoft.com/profile=C4D9F5E7-BB03-4291-
> > > > > > B816-642
> > > > > > 707308
> > > > > > > > > > > 81E>
> > > > > > > > > > >
> > > > > > https://mvp.support.microsoft.com/profile=C4D9F5E7-BB03-4291-B
> > > > > > 816-6427
> > > > > > 073088
> > > > > > > > > > > 1E
> > > > > > > > > > >
> > > > > > > > > > > My Nothing-to-do-with Access blog
> > > > > > > > > > >
> > > > > > > > > > > <http://wrmosca.wordpress.com>
> > > > > > > > > > > http://wrmosca.wordpress.com
> > > > > > > > > > >
> > > > > > > > > > > From: MS_Access_Professionals@yahoogroups.com
> > > > > > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > > > > > > > > > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > > > > > > > > > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > > > > > > > > > [mailto:MS_Access_Professionals@yahoogroups.com
> > > > > > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > > > > > > > > > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > > > > > > > > > <mailto:MS_Access_Professionals%40yahoogroups.com> ]
> > > > > > > > > > > On Behalf Of Ray
> > > > > > > > > > > Sent: Thursday, April 25, 2013 5:39 AM
> > > > > > > > > > > To: MS_Access_Professionals@yahoogroups.com
> > > > > > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > > > > > > > > > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > > > > > > > > > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > > > > > > > > > Subject: [MS_AccessPros] #deleted in a field value
> > > > > > > > > > >
> > > > > > > > > > > Can you shed some light on this mystery? A friend
> > > > > > > > > > > has an Access 2003
> > > > > > db and
> > > > > > > > > > > one
> > > > > > > > > > > table has some rows with #deleted in each field
> > > > > > > > > > > rather than the data
> > > > > > that
> > > > > > > > > > > used
> > > > > > > > > > > to be there. I suspect that they have used the
> > > > > > > > > > > "delete record" button
> > > > > > from
> > > > > > > > > > > the
> > > > > > > > > > > toolbar to delete a few rows (I know, not a good
> > > > > > > > > > > idea) and that
> > > > > > something
> > > > > > > > > > > went
> > > > > > > > > > > wrong. When a query runs on this table it returns an
> > > > > > > > > > > error saying that
> > > > > > a
> > > > > > > > > > > record
> > > > > > > > > > > has been deleted and then stops dead.
> > > > > > > > > > >
> > > > > > > > > > > We recovered the database from a backup and life has
> moved on.
> > > > > > However, I've
> > > > > > > > > > > never seen this before, have you?
> > > > > > > > > > >
> > > > > > > > > > > Many thanks
> > > > > > > > > > > Ray
> > > > > > > > > > >
> > > > > > > > > > > [Non-text portions of this message have been
> > > > > > > > > > > removed]
> > > > > > > > > > >
> > > > > > > > > > > [Non-text portions of this message have been
> > > > > > > > > > > removed]
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > > > [Non-text portions of this message have been
> > > > > > > > > > > removed]
> > > > > > > > > > >
> > > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > > [Non-text portions of this message have been removed]
> > > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > > ------------------------------------
> > > > >
> > > > > Yahoo! Groups Links
> > > > >
> > > >
> > >
> >
>
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>