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.
A temporary table is created to hold the value of the unique field, shown in 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).
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".
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()
X = UpdateSelectedRecords()
Public Sub SelRestore()
MySelForm.SelTop = MySelTop
MySelForm.SelHeight = MySelHeight
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"
For recordcounter = 1 To F.SelHeight
CurrentDb.Execute "INSERT INTO tempTBL_First (Emp_ID) VALUES (" & rs!Emp_ID & ")"
Set rstUpdateCase = CurrentDb.OpenRecordset("SELECT Emp_ID FROM tempTBL_First")
If rstUpdateCase.BOF Then
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"
Public Function SelRecord(F As Form, MouseEvent As String)
Select Case MouseEvent
If fMouseDown = True Then Exit Function
Set MySelForm = F
MySelTop = F.SelTop
MySelHeight = F.SelHeight
fMouseDown = True
fMouseDown = False
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.
Copyright © 2019 ERP Makers