[AccessD] Record deleted by user

In fact it is not an issue in my app.  I create an outer join between the
two tables such that the main record is always there, but the "select" table
doesn't have a record until the user clicks on a combo bound to a field in
that Select table.  At that instant Access does correctly create the table.

Good so far.

The issue only really comes up if:

1) The form stays open after it "knows about" a record in the Select table.
2) With the form open the select record is deleted somehow.

>From that point on the form can no longer use the combo box bound to the
Select flag FOR THE DELETED RECORD ONLY.

If the form is closed and re-opened, no problem.
If the form is requeried, no problem.

It was an artificial environment, since I was going into the Select table
and deleting these records manually in the course of testing the form.
However it is possible that a developer could inadvertently perform a delete
on this table via code or some other method.  It was really for that reason
that I even wrote the message to the group.  I was not looking for a
"solution" since I don't really believe there is one per se, rather I was
just informing the group that this behavior happens and to think about it if
you ever find yourself unable to place data into a control on a form with a
recordset like this.  It took me awhile to figure out what was going on,
why, and how to work around it (don't delete the records from the outer join
table, close / reopen the form / requery the form).

John W. Colby
www.colbyconsulting.com

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Gustav Brock
Sent: Friday, September 12, 2003 8:34 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Record deleted by user


Hi John

Just curious - did you consider the suggestion of mine:

<quote>

You could create the records as needed - as you do now - but instead
of deleting them when finished, update Selected to False - this means,
of course, that a selected record must have an entry in your select
table and the field Selected in this must be True.

Then, from time to time, delete those records in the select table
where Selected is False. Or did you overwrite the FE every morning?
Or, as suggested by Jim, use a temp table which you recreate at every
launch.

</quote>

The reason I ask is that I may be facing a similar request and, of
course, am interested in experiences ...

/gustav


> No.  AFAIK there is no solution, it's a "feature" of access.

> John W. Colby
> www.colbyconsulting.com

> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Gustav Brock
> Sent: Friday, September 12, 2003 4:51 AM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Record deleted by user


> Hi John

> Did you find a solution to this?

> /gustav


>>> I am trying to implement a "selected flag" system for a particular form.
>>> I create a table local to the FE with two fields:

>>> SelectedID Long
>>> Selected Boolean

>>> I modify the form's query to do an outer join between the pk of the
>>> original table and SelectedID.
>>> I add a check box to the form bound to the Selected field.

>>> Whenever the user clicks the check box a record is created in the new
>>> table,
>>> the SelectedID is set to the PK of the original table and the Selected
>>> field
>>> is set true.  All fine so far.  Printing works fine - filter on records
>>> with a join to this table and selected = true.

>>> The odd behavior comes if I delete the records in the new Selected
table.
>>> If I build a query that displays the original table joined to the
>>> Selected
>>> table, when I delete any records in the Selected table, the fields in
the
>>> query from the Selected table display the words "Deleted" and I can no
>>> longer enter any data in that field because I get a JET error "record
>>> deleted by user".  I have to requery the query and then I can again
>>> create new records.

>>> This is an issue because the FORM's recordset works until I delete a
>>> record
>>> in the selected table, then the checkbox FOR THAT RECORD ceases to work
>>> for
>>> no apparent reason.  The reason of course is this problem discussed
>>> above.

_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com