UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Extract User Selected/highlighted Record(s) From Split Form Datasheet, Access 2016    
 
   
Sean-AUS-WA
post Feb 11 2019, 11:06 PM
Post#1



Posts: 49
Joined: 21-April 16



Greetings Experts/Pro:

I am attempting to do something that is slightly beyond my current scope and have googled far and wide without much success. I did find a few properties/functions that might work but most of them fall short!!! Of the whole chain of automation, this is the only missing piece left and has now caused me quite a lot of frustration over the last 24-48hrs. crazy.gif pullhair.gif

Scenario:
I need a method to identity the records selected by users on the datasheet within a Access split form and append the primary key of those records to a local table in the background. The whole purpose is for users to be able to select & update multiple records at the same time (else they would have to select a drop down 50 times which will lead to RSI). As our system architecture is a split database with an SQL server back end and Access as front, once those records are within the local table I can then run an SQL SP (stored procedure) to loop through the recordset of the local table and update those records a lot quicker than executing the update at access level.

Some of my Research findings came up with the following functions that may work (but I haven't been successful in utilizing them):
dataGridView1.SelectedRows (unsure if this is even a access function)
SelTop, SelHeight (apparently the uses of these are limited as they lose their identity once you lose focus of the data sheet)

It would be stupendous if anyone is able to point me to an article or a function that I may be able to use to perform this task! Thanks much in advance!! thanks.gif
This post has been edited by Sean-AUS-WA: Feb 11 2019, 11:07 PM

--------------------
~~ Impossibility Only Exists in the lack of Creativity, Innovation and Perseverance ~~
Go to the top of the page
 
theDBguy
post Feb 11 2019, 11:18 PM
Post#2


Access Wiki and Forums Moderator
Posts: 74,497
Joined: 19-June 07
From: SunnySandyEggo


Hi. SelTop and SelHeight work in Datasheet View. Haven't tried it in a Split Form, but I suspect it could work as well. Can you post a sample database, so we can help you experiment?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
Sean-AUS-WA
post Feb 12 2019, 12:11 AM
Post#3



Posts: 49
Joined: 21-April 16



Hi DBGuy,

Thanks for coming to the rescue yet again!!!

I've attached a copy of the test access db file with some basic VBA code embedded in the form open event etc. After opening, I select a couple of records in the datasheet, the first 2 VBA code that seems to fire correctly but for some reason the Me.SelHeight line always return a 0 pullhair.gif This seems to be the common frustration with a lot of people that tries to use this code... I'm still reading up on why that is happening.

additionally I am borrowing VBA from Dev Ashish

IF I am able to solve this before your reply I shall post again.. if not then most likely I may have past out due to nicotine overdose from 'vaping' giveup.gif
Attached File(s)
Attached File  IdentifySelectedRecordsTest.zip ( 28.13K )Number of downloads: 1
 

--------------------
~~ Impossibility Only Exists in the lack of Creativity, Innovation and Perseverance ~~
Go to the top of the page
 
MadPiet
post Feb 12 2019, 01:23 AM
Post#4



Posts: 2,828
Joined: 27-February 09



I don't get it. If all you want users to be able to do is select multiple records, why not use a multi-select listbox? Or are you trying to show all those in a form and let the user select the records he wants?
Go to the top of the page
 
Sean-AUS-WA
post Feb 12 2019, 01:49 AM
Post#5



Posts: 49
Joined: 21-April 16



Hi MadPiet,

That is correct... multi select listbox may work but in this scenario but might not be very user friendly (they will still need to click 100 plus times?). I want to be able to let the users select a list of records (click scroll down and shift click to select 10? 20? records) from all the records in the form, press "Add Selection" button and have all the primary key appended to a local table in the back. Once they have selected 100s+ plus records (or however many they want), they can then update all those records in the back table at the same time via a pass through query to the SQL server. ** Also this method looks more elegant **
This post has been edited by Sean-AUS-WA: Feb 12 2019, 01:51 AM

--------------------
~~ Impossibility Only Exists in the lack of Creativity, Innovation and Perseverance ~~
Go to the top of the page
 
MadPiet
post Feb 12 2019, 02:05 AM
Post#6



Posts: 2,828
Joined: 27-February 09



If you set the Multi-Select property of the listbox to Extended, you can click on the first item and then CTRL-SHIFT-CLICK you can select everything between the first item selected and the last. You might use that and just teach the users how to use it. I'm just collecting the values and stuffing them into a string, but you could do anything you want. (like have the primary key of the table next to the text values, and grab the PK values... and do data modification or whatever you want.

then you can loop through the ItemsSelected collection

CODE
Private Sub cmdShowSelected_Click()
    Dim varItem As Variant
    Dim strList As String
    
    For Each varItem In Me.List2.ItemsSelected
        strList = strList & ", " & Me.List2.ItemData(varItem)
    Next varItem
    
    strList = Right$(strList, Len(strList) - 2)
    MsgBox strList, vbOKOnly + vbInformation
    
End Sub

This post has been edited by MadPiet: Feb 12 2019, 02:05 AM
Go to the top of the page
 
Sean-AUS-WA
post Feb 12 2019, 02:28 AM
Post#7



Posts: 49
Joined: 21-April 16



Hi MatPiet,

Thanks a million, will definitely trial that code out and keep it for future references if I can't find the code I need for the current automation to work. However I think I might have almost found the answer to the way I have imagined it working!..

There are still some missing pieces but I'll be able to fill in the blanks fairly quickly.

The newest example db is attached... very basic but kinda does what I want it to do in terms of remembering the number of selected records!
Attached File(s)
Attached File  IdentifySelectedRecordsTestFinal.zip ( 32.95K )Number of downloads: 0
 

--------------------
~~ Impossibility Only Exists in the lack of Creativity, Innovation and Perseverance ~~
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    22nd February 2019 - 09:20 AM