Update a read-only recordset

[jacksonmacd]

Main form contains a subform (datasheet view) that is based on a
read-only recordset. A new requirement has added another field to the
recordset that must be updated by the user. The field values will
always be 0, 1, 2, or 3. I was hoping to allow the user to select an
arbitrary number of records in the subform to be updated, then perform
some action (click a button, select from a combo box, select a radio
button, etc) that would update the underlying table via VBA. But I
can't figure out how to do that...

Any suggestions? Using A2003.

 

[Chris O'C via AccessMonster.com]

Add a combo box to the main form with the range 0 to 3 for your user to
select. Then the user selects the record or continuous set of records shown
in the subform's datasheet. Your code will use the subform's (datasheet's)
SelHeight property and a RecordsetClone to determine which records were
selected and get their primary keys. Once that's done use an update query on
those records in the table to change the added field according to the 0 to 3
combo box setting. Requery the form to show the changes.

Chris
Microsoft MVP

 

[jacksonmacd]

Thanks, Chris. I was unaware of the SelHeight property, so that's a
step in the right direction. I am still stuck, though, because I don't
understand is how to fire an event from a control and still have the
selection active.

Found this article:
http://support.microsoft.com/kb/294202
that seems to deal with the problem of retaining a reference to the
selected rows, but haven't yet got it running properly. It seems
rather complex to be able to refer to the selected records in a
subform.

Am I overlooking something simple?

 

[Chris O'C via AccessMonster.com]

I should have mentioned there's a button to fire off your code after the user
selects the records.

It's not simple but here's a fairly close example of how to do what you want
to do with a datasheet in a subform. This example copies records in the
subform and appends them to the same table via SQL (for additional changes by
the user). You can use your update query instead of the append query. It
also jumps to the new record in the main form. Skip that part in your code.

http://groups.google.com/group/microsoft.public.access.formscoding/msg/9a68ea933e442655?hl=en


Chris
Microsoft MVP

 

[jacksonmacd]

Excellent! I kinda figured there was a way to do it by using a custom
property of the form, and using the form's Click event to set the
property value is simpler than the example that I found. Thanks very
much!

 

[jacksonmacd]

Chris - one more detail. The Click event fires when the subform's
record-selector is clicked. Select a range of records, and the
DSSelHeight property is set accordingly. Works exactly as needed.

However, if the user then clicks in a record, thus removing the
selection, the Click event does not fire and the DSSelHeight property
contains an incorrect value. I played around with using the Current
event to set the DSSelHeight Property to zero under those conditions,
but it introduced its own set of problems.

Do you know of a way to erase the DSSelHeight property (ie, set it to
zero) when the user deliberately "unselects" all the records?

 

[Chris O'C via AccessMonster.com]

Jack, I've played with it some this afternoon and am having troubles with the
current event, too. I'll have to get back to this later tonight. One
solution I thought of that would work (but it's not a good one) is to have
the user push a button to reset the selected rows in the datasheet as
"deselected" to set the DSSelHeight property back to zero manually. Never
rely on the user (that's why it's not a good solution).

Chris
Microsoft MVP

 

[jacksonmacd]

Thanks, Chris. But don't spend a lot of time on it -- I can live with
it as-is. As you said, creating a button to clear the item is really a
non-starter...

Also tried the MouseUp event. Thought it might fire when the user
clicks in a datasheet cell, but it also fires only when the record
selector is clicked.