How to perform operations on selected records of a datasheet

One can simply show a form into datasheet view and can insert and delete rows. But some hinderence occurs when some one wants to perform any kind of operations only on selected rows of datasheet.

Here this article provides solution to this problem by an example, so that one can perfom operation on Selected rows of the "Datasheet." And one can do this using VBA Code.

Here's the example which guides you towards the solution of this problem. Fig:- 1.1 shows the main table having a unique coulmn.

To perform operations on selected records of a datasheet Fig:1.1


A temporary table is created to hold the value of the unique field, shown in Fig:- 1.2.

To perform operations on selected records of a datasheet Fig:1.2


The fig:-1.3 shows the main form. Users have to select the records first and then can perform operation (whatever specified in the code).

To perform operations on selected records of a datasheet Fig:1.3


As shown in Fig:- 1.4 user will select a record in the datasheet and when user will click onto the button, then the selected row will be marked as "Checked".

To perform operations on selected records of a datasheet Fig:1.4


VBA code for the above procedure is given here for "On Click" event of "Select record button":-

Option Compare Database
Dim F As Form
Dim rs As Recordset
Dim MySelTop As Long
Dim MySelHeight As Long
Dim MySelForm As Form
Dim fMouseDown As Integer
'------------------------Code for click event on buton------------------------
Private Sub Selectrecords_Click()
Dim X
X = UpdateSelectedRecords()
End Sub
Public Sub SelRestore()
MySelForm.SelTop = MySelTop
MySelForm.SelHeight = MySelHeight
End Sub
Private Function UpdateSelectedRecords()
Dim rstUpdateCase As Recordset
Set F = ForMS("MainForm").Controls("Subfrm").Form
Set rs = CurrentDb.OpenRecordset(F.RecordSource, dbOpenDynaset, dbSeeChanges)
rs.Move F.SelTop - 1
If (F.SelHeight = 0) Then
MSgBox "Please Select AtLeast One Record"
Exit Function
End If
For recordcounter = 1 To F.SelHeight
CurrentDb.Execute "INSERT INTO tempTBL_First (Emp_ID) VALUES (" & rs!Emp_ID & ")"
Next recordcounter
Set rstUpdateCase = CurrentDb.OpenRecordset("SELECT Emp_ID FROM tempTBL_First")
If rstUpdateCase.BOF Then
Exit Function
End If
CurrentDb.Execute "UPDATE TBL_First SET Active = False "
While Not rstUpdateCase.EOF
CurrentDb.Execute "UPDATE TBL_First SET Active = True WHERE Emp_ID = " & rstUpdateCase!Emp_ID
CurrentDb.Execute "DELETE * FROM tempTBL_First"
End Function
Public Function SelRecord(F As Form, MouseEvent As String)
Select Case MouseEvent
Case "Move"
If fMouseDown = True Then Exit Function
Set MySelForm = F
MySelTop = F.SelTop
MySelHeight = F.SelHeight
Case "Down"
fMouseDown = True
Case "Up"
fMouseDown = False
End Select
End Function

Also build the following expressions on different events of the "Select Record" button :-

On Mouse Down:- =SelRecord([Subfrm].[Form],"Down")
On Mouse Up:- =SelRecord([Subfrm].[Form],"Up")
On Mouse Move:- =SelRecord([Subfrm].[Form],"Move")


It is advised that the information provided in the article should not be used for any kind formal or production programming purposes as content of the article may not be complete or well tested. Access Guru will not be responsible for any kind of damage (monetary, time, personal or any other type) which may take place because of the usage of the content in the article.