Deleting records in a subform from a form.

  • Thread starter Thread starter Alberto Uttranadhie
  • Start date Start date
A

Alberto Uttranadhie

Hi, everybody,

I have a friend who asked how it is possible to delete selected records in a
subform and by clicking a command button in a form execute such deletion. I
could not find a solution, only in case of having an independent check box
but this fails because it is not possible to set a control source, and when
picking on one record, the rest are picked as well.

Should I create such check box from a SQL parameter, and set subform's
record source from a SQL statement? In that case, how is it possible to set
an independent value to the parameter for each record? I was searching this
possibility because a For...Next method would be used.

As always, thanks a lot in advance.

Best regards,

Alberto Uttranadhie (Spain)
 
Dirk Goldgar said:
There are a couple of problems involved in doing this with a subform.
The smaller one is that the selected records will become unselected when
the focus moves back to the main form. The bigger one is that you can't
select records that aren't contiguous. If it didn't have to be a
subform, you could use a multiselect list box instead; then it would be
easy to loop through the selected items and delete the corresponding
records in the list box's rowsource table.

You can use the check-box solution by having a yes/no field in the
subform's recordsource table that is dedicated to selecting sets of
records. You initialize this field for all the records with an update
query, and then let the user select individual records by cliking on
check boxes bound to the field. To delete the selected records, you
just execute a delete query that uses that field as a criterion.
Click to expand...


You can also do this:

Have an unbound checkbox in the subform. Store the values for that checkbox,
in an array. Bind the checkbox to a function. Have the function synchronize
the subform's recordsetclone (via its bookmark), then use absoluteposition
to index into the array. By this means, you can display a different value of
the "unbound" checkbox, for each row.

Then, you can make the checkbox updatable, by trapping the relevant
keystroke &/or mouse events, updating the relevant array entry, and
..Recalc'ing the checkbox to show the effect of the change!

Voila: updatable controls that are not bound to a database column, but also
have a seperate, persistent value for each row.

It sounds ugly, but it is actually fairly simple for a checkbox. It is a bit
harder for a textbox. But the method looks >fine< for either control, from
an end-user viewpoint.

TC
 
Dirk Goldgar said:
Cute, though I guess you'd have to allocate/reallocate the array as
records are added to the subform.
Indeed!


Here's *another* way: have a work table with two fields: a key field
that can be joined to the primary key of the subform's recordsource, and
a boolean field IsSelected. Base the subform on a LEFT JOIN of the
original table with the work table, and bind the check box on the
subform to the IsSelected field. Empty the work table in the subform's
Current event. Now when the user clicks the check box in the subform
detail, a record will be created in the work table. When it's time to
delete the selected records, run a delete query based on an inner join
of the original table with the work table, using the IsSelected field as
a criterion.
Click to expand...

I've considered that before, but I feel there is a problem in a fe/be
situation. The work table preferably should not go in the fe, because it
would never get compacted (and it would presumably get a lot of
delete/re-add traffic). But it can not go in the be, if the system is an
unsecured multi-user system. (Because then, all users would share the same
table, & there is no apparent "per user" identifier to allow seperate
records for different users. Although: maybe random #s would work?)

Cheers,
TC
(off for the day)
 
Dirk Goldgar said:
Good points. I can think of several ways around the problem of
user-identification in a back-end-based approach, but they seem a bit
fragile to me; besides this strikes me as user-interface functionality
that should properly be implemented in the front-end. Assuming one
doesn't want to rely on the front-end auto-compacting or expect the use
to compact it on a regular basis, a viable solution is to create a
temporary database when the subform opens, just to hold this table.

I have a very simple "WorkDB" class that upon instantiation creates a
Jet database in the user's temp folder. It has a method that creates a
table (from a template argument) in the work .mdb, and creates a linked
table in the current database pointing to it. When the class object is
destroyed, it deletes the work .mdb and all the linked tables pointing
to it.

So ... in the subform's Open event we create a module-level WorkDB
object, and create our work table within it. When the subform closes,
the WorkDB is destroyed and the temporary .mdb file evaporates into thin
air. Voila! No bloating, or very little, in the front-end database.
Click to expand...


Yes! That would be the solution. The class wrapper is a great idea.

Dirk, I have run out of arguments! You have prevailed entirely!! But I shall
be back tomorrow!!!

Cheers,
TC
 
Alberto Uttranadhie said:
Hi, everybody,

I have a friend who asked how it is possible to delete selected
records in a subform and by clicking a command button in a form
execute such deletion. I could not find a solution, only in case of
having an independent check box but this fails because it is not
possible to set a control source, and when picking on one record, the
rest are picked as well.

Should I create such check box from a SQL parameter, and set subform's
record source from a SQL statement? In that case, how is it possible
to set an independent value to the parameter for each record? I was
searching this possibility because a For...Next method would be used.

As always, thanks a lot in advance.

Best regards,

Alberto Uttranadhie (Spain)
Click to expand...

There are a couple of problems involved in doing this with a subform.
The smaller one is that the selected records will become unselected when
the focus moves back to the main form. The bigger one is that you can't
select records that aren't contiguous. If it didn't have to be a
subform, you could use a multiselect list box instead; then it would be
easy to loop through the selected items and delete the corresponding
records in the list box's rowsource table.

You can use the check-box solution by having a yes/no field in the
subform's recordsource table that is dedicated to selecting sets of
records. You initialize this field for all the records with an update
query, and then let the user select individual records by cliking on
check boxes bound to the field. To delete the selected records, you
just execute a delete query that uses that field as a criterion.
 
TC said:
You can also do this:

Have an unbound checkbox in the subform. Store the values for that
checkbox, in an array. Bind the checkbox to a function. Have the
function synchronize the subform's recordsetclone (via its bookmark),
then use absoluteposition to index into the array. By this means, you
can display a different value of the "unbound" checkbox, for each row.

Then, you can make the checkbox updatable, by trapping the relevant
keystroke &/or mouse events, updating the relevant array entry, and
.Recalc'ing the checkbox to show the effect of the change!

Voila: updatable controls that are not bound to a database column,
but also have a seperate, persistent value for each row.

It sounds ugly, but it is actually fairly simple for a checkbox. It
is a bit harder for a textbox. But the method looks >fine< for either
control, from an end-user viewpoint.
Click to expand...

Cute, though I guess you'd have to allocate/reallocate the array as
records are added to the subform.

Here's *another* way: have a work table with two fields: a key field
that can be joined to the primary key of the subform's recordsource, and
a boolean field IsSelected. Base the subform on a LEFT JOIN of the
original table with the work table, and bind the check box on the
subform to the IsSelected field. Empty the work table in the subform's
Current event. Now when the user clicks the check box in the subform
detail, a record will be created in the work table. When it's time to
delete the selected records, run a delete query based on an inner join
of the original table with the work table, using the IsSelected field as
a criterion.
 
TC said:
I've considered that before, but I feel there is a problem in a fe/be
situation. The work table preferably should not go in the fe, because
it would never get compacted (and it would presumably get a lot of
delete/re-add traffic). But it can not go in the be, if the system is
an unsecured multi-user system. (Because then, all users would share
the same table, & there is no apparent "per user" identifier to allow
seperate records for different users. Although: maybe random #s would
work?)
Click to expand...

Good points. I can think of several ways around the problem of
user-identification in a back-end-based approach, but they seem a bit
fragile to me; besides this strikes me as user-interface functionality
that should properly be implemented in the front-end. Assuming one
doesn't want to rely on the front-end auto-compacting or expect the use
to compact it on a regular basis, a viable solution is to create a
temporary database when the subform opens, just to hold this table.

I have a very simple "WorkDB" class that upon instantiation creates a
Jet database in the user's temp folder. It has a method that creates a
table (from a template argument) in the work .mdb, and creates a linked
table in the current database pointing to it. When the class object is
destroyed, it deletes the work .mdb and all the linked tables pointing
to it.

So ... in the subform's Open event we create a module-level WorkDB
object, and create our work table within it. When the subform closes,
the WorkDB is destroyed and the temporary .mdb file evaporates into thin
air. Voila! No bloating, or very little, in the front-end database.
 
Back
Top