Hi, Amit.
Is there a way to accomplish this while still maintaining the current set-up
of form-subform?
Most people would use a list box in order to be able to easily select items
and be able to determine which items were selected. However, if you want to
use a subform, then you have a couple of problems to overcome:
1.) Determine which records were selected.
2.) Pass that info from the subform control to the main form after the
subform loses focus.
When selecting multiple records in a datasheet or continuous form, the
form's SelHeight Property can be used to determine how many records were
selected. However, there's no direct way to tell what any of the fields'
values are in anything but the very first record selected, because it's the
"current record" in the datasheet or continuous form. If there's a primary
key, then that record can be identified, but not the other records since they
aren't the current record.
One solution to this first problem is to walk through the selected records
one by one and grab the values from the fields (namely the primary key) in
the current record, then move to the next record and get the values from the
fields in that record, and so on.
The second problem is that the main form can't read the subform control's
SelHeight Property as soon as focus is lost from the subform control. This
property becomes zero, so there's no way to walk through zero records to grab
the primary key any record beyond the first of these multiple records
selected. One solution to this second problem is to save the SelHeight
Property as a public form property that can be read by external objects.
This may be done in the form's OnClick( ) event.
The first thing to do is to copy and paste the following code into your
subform's code module:
' * * * * Start code * * * *
Private m_nHt As Long
Public Property Let DSSelHeight(nHt As Long)
m_nHt = nHt
End Property
Public Property Get DSSelHeight() As Long
DSSelHeight = m_nHt
End Property
Private Sub Form_Click()
On Error GoTo ErrHandler
m_nHt = Me.SelHeight
Exit Sub
ErrHandler:
MsgBox "Error in Form_Click( ) in" & vbCrLf & _
Me.Name & " form." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear
End Sub
' * * * * End code * * * *
Here's an example of how to read the selected records on the subform from
the main form (requires setting a reference to the DAO library):
' * * * * Start code * * * *
Private Sub SelRecsBtn_Click()
On Error GoTo ErrHandler
Dim recSet As DAO.Recordset
Dim sList As String
Dim numRecs As Long
Dim fOpenedRecSet As Boolean
Dim idx As Long
numRecs = Me!subFrmCtrl.Form.DSSelHeight
Set recSet = Me!subFrmCtrl.Form.RecordsetClone
fOpenedRecSet = True
'--------------------------------------------------------------------
' Walk through each selected record to retrieve
' the primary key.
'--------------------------------------------------------------------
For idx = 1 To numRecs
sList = sList & Me!subFrmCtrl.Form.txtID.Value & vbCrLf
recSet.Bookmark = Me!subFrmCtrl.Form.Bookmark
recSet.MoveNext
If (Not (recSet.EOF)) Then
Me!subFrmCtrl.Form.Bookmark = recSet.Bookmark
End If
Next idx
MsgBox sList
CleanUp:
If (fOpenedRecSet) Then
recSet.Close
fOpenedRecSet = False
End If
Set recSet = Nothing
Exit Sub
ErrHandler:
MsgBox "Error in SelRecsBtn_Click( ) in" & vbCrLf & _
Me.Name & " form." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear
GoTo CleanUp
End Sub ' SelRecsBtn_Click( )
' * * * * End code * * * *
.. . . where subFrmCtrl is the name of the subform control, and txtID is the
name of the text box on the subform that displays the primary key of the
current record. Please note that because the Recordset is walked through
with "MoveNext" and not "MovePrevious," if the user selects the records from
the bottom to the top, then the first "current record" will be the bottom row
selected, not the top row selected, and the next record after the current
record will be the very first record _after_ the actual group of records
selected -- which will result in an erroroneous list of records selected.
The bottom line is that the user _must_ select from the top to the bottom of
the records, not from the bottom to the top. If you want to accommodate this
quirk, then I leave it to you to modify the algorithm above to determine when
the user has selected from the bottom to the top of the records, and
accommodate this with recSet.MovePrevious.
I'd like to select one or more records on the subform and perform some
action (eg. send email, print out etc.) on the selected records.
Once you have the primary keys of each of these records, you may use this
primary key to send E-mail for each of these records, or print these records
in a report, et cetera.
HTH.
Gunny
See
http://www.QBuilt.com for all your database needs.
See
http://www.Access.QBuilt.com for Microsoft Access tips.
(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.
