Solved Disable Multiple Record Selection On Subform (1 Viewer)

dalski

Member
Local time
Today, 23:57
Joined
Jan 5, 2025
Messages
218
How does one protect against multiple records being selected on a subform?
  • Current Event - if multiple records are selected it will not recognize
  • Record Selectors Property - would be handy if it had a collection of the currently selected records but does not seem to have
Situation
The subform is a datasheet, based on a temporary table built from a Union Query to allow editing of multiple record sources; works great. BUT if the user selects multiple rows to delete/ add records it breaks the application because the records are deleted first in the local temporary table. So it is unable to call the handling functions assigned to handle the deletion in the applicable table in a 3126 runtime error. So I need to disable multiple record selection somehow.
I know not to select multiple records but the user will not. I'm trying to avoid disabling record manipulation on the form because for single operations it's much faster for the user to use native Access' single record deletions... rather than having to click through a command bar; drastically slowing them down.

I also have a lot going on in the Mouse Events as I'm implementing Drag & Drop so trying not to interfere with these if possible.

Aware how to handle a multi delete (put in checkbox of selected items), which I'll put a Command Bar in there to handle it but I just need to stop the user falling into this trap.
 
Last edited:
Change it to a form that looks like a data sheet, then disable the record selector and add a delete cmd button to the end of the record?
That avoids the multi select problem and gives a single click to delete the current record.
 
Thanks Minty, if possible I'm trying to avoid a form because it is nice for the user to be able to rearrange/ hide columns in what order they prefer; there is a lot of columns. That is helpful input though so thank you.
 
Last edited:
Whilst datasheet forms have the functionality to hide / freeze / move / reorder columns already built-in, the same features can all be added to continuous forms. See the various links in my summary article:

 
Thanks IslaDogs, that's a very good link & certainly will delve into it. Correct me if I'm wrong but I'd need to add another listbox to manipulate the order/ showing/ hiding columns. I'm tight on real-estate with the form & don't really have space a listbox/ another fair-sized control atm.

I'm hoping there is some sort of coding pattern I can implement with a datasheet as it seems quite a trivial issue & a datasheet seems the most space-efficient control for what I need.

Certainly appreciate the input. Running a bit short on time & trying to stick with my datasheet route as this issue affects multiple forms & quite a bit of code behind all of them o_O. Again very helpful input though & I hadn't found them links on your site before so looking forward to studying them.
 
add Timer Event to your datasheet form:

TimerInterval: 100
Code:
Private Sub Form_Timer()
If Me.SelHeight > 1 Then
    Me.SelHeight = 1
End If
End Sub
 
Thanks IslaDogs, that's a very good link & certainly will delve into it. Correct me if I'm wrong but I'd need to add another listbox to manipulate the order/ showing/ hiding columns. I'm tight on real-estate with the form & don't really have space a listbox/ another fair-sized control atm.

I'm hoping there is some sort of coding pattern I can implement with a datasheet as it seems quite a trivial issue & a datasheet seems the most space-efficient control for what I need.

Certainly appreciate the input. Running a bit short on time & trying to stick with my datasheet route as this issue affects multiple forms & quite a bit of code behind all of them o_O. Again very helpful input though & I hadn't found them links on your site before so looking forward to studying them.

I used a listbox for the move/resize columns example but as its not multiselect, that could also be done using a combobox just as well.
I already use a combobox for restoring hidden columns as that takes up less space. Alternatively it could be done using a custom right click context menu.

In fact, in the near future, I will be publishing another example app which does almost everything from context menus so no additional space will be required. For example:

1761231663089.png
 
add Timer Event to your datasheet form:
That's more what I'm after thanks Arnel. Sorry been slow responding; I ran into another issue which prevents me from testing. I'm using a timer also with a mouse event but I'll just set it less than the other so pretty sure this will fix it. The sel.Height property is definitely what I'm after so big thanks.
 

Users who are viewing this thread

Back
Top Bottom