|
Determine Which Subform Records Have Been Selected
By The Gunny
List boxes aren't the only method to allow users to select multiple items at the same time.
(Thousand Oaks, CA -- Nov. 5, 2005) -- Access database developers generally use a list box in order to allow users to easily select items in a list and then programmatically determine which items the user selected. However, there are times when a list box isn't practical, especially when addition of the list box control requires the user to scroll through the form to make all of the controls visible, or when the database developer must add a tab control to display controls on the form that wouldn't otherwise be visible without scrolling.
And when a subform displays records in Datasheet View or Continuous Forms View which are related to the main form's records, adding a list box on the main form for the user to select these same items is particularly redundant. Yet many developers find themselves doing precisely this because they can't overcome two major problems when using a subform where the user selects multiple records. These two major problems are:
1.) Determine exactly which records were selected.
2.) Pass that information from the subform control to the main form after the subform control loses focus.
When the main form has the focus, it can read the values for each of the fields in only the current record of the subform control. The main form can't read the values of the fields in any other record selected by the user. If there's a primary key field in the subform control, then this "current record" can be identified, but not the other selected records since they aren't the current record.
One solution to this first problem is to take advantage of the the subform's SelHeight Property, which can be used to determine how many records were selected in the subform control. Using the number of records selected, the records displayed in the subform control can be walked through one by one, reading the values from the fields (namely the primary key) in the current record, then moving to the next record, reading the values from the fields in that record, and so on.
The second problem is that the main form must have the focus before any of its events can fire, and as soon as focus is lost from the subform control, the main form can't read the subform control's SelHeight Property. This property becomes zero, so there's no way to walk through zero selected records to grab the primary key of any other record but the first record selected in the subform control. One solution to this second problem is to save the SelHeight Property as a public form property that can be read by external objects, such as the main form. This may be done in the form's OnClick( ) event.
To see how this technique is applied, download the SelSubfrmRecs.ZIP file (78 KB) and view the sfmOrderDetails form's module or copy and paste the following code into your own subform's code module:
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
Please note that this form and subform were selected for this example because it shows a one-to-many relationship between the Orders table and the OrderDetails table. The frmOrders form (the main form) displays the records on the "one" side of this relationship, and the sfmOrderDetails form (the subform) displays records on the "many" side.
For an example of how to read the primary key of all of the selected records on the subform from the main form, look at the SelRecsBtn_Click( ) event code in the main form, frmOrders, or copy and paste the following code into your main form:
(Requires setting a reference to the DAO library.)
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.txtODID.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( )
. . . where SelRecsBtn is the name of the button, subFrmCtrl is the name of the subform control, and txtODID 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 erroneous list given for the 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 in Datasheet View, and accommodate this with recSet.MovePrevious.
To view this technique in action, select any records on the subform control and then select the SelRecsBtn button (or your own button that uses the code above) to display a list of the values for the primary key of every record selected.
What's that you say? Listing the primary keys in a Message Box isn't very useful? Perhaps not, but it gives us a code framework for determining exactly which records were selected by reading the primary key of each of these 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.
For a real world example, how many times have you heard the phrase, "I'll have what he's having, except . . . "? Wouldn't it be easier to copy the original customer's order and make the changes, rather than typing every entry from scratch for the new record? Of course it would be.
To apply this strategy, we can create a "Copy" button on the main form that reads the selected records on the subform (on the "many" side of the relationship), creates a new record on the main form (on the "one" side), then copies the selected (related) records to the subform control for the new record in the main form. We only need to make a few changes to the code above:
Private Sub CopyBtn_Click()
On Error GoTo ErrHandler
Dim recSet As DAO.Recordset
Dim sList As String
Dim sCustID As String
Dim numRecs As Long
Dim nFKey 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.txtODID.Value & ", "
recSet.Bookmark = Me!subFrmCtrl.Form.Bookmark
recSet.MoveNext
If (Not (recSet.EOF)) Then
Me!subFrmCtrl.Form.Bookmark = recSet.Bookmark
End If
Next idx
sList = Mid$(sList, 1, Len(sList) - 2) ' Remove last comma & space.
sCustID = Me!txtCustomerID.Value
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
'--------------------------------------------------------------------
' Must write to any field to create newest AutoNumber.
'--------------------------------------------------------------------
Me!txtCustomerID.Value = sCustID ' Need related customer record.
nFKey = Me!txtOrderID.Value ' Foreign key in related records.
RunCommand acCmdSaveRecord
'--------------------------------------------------------------------
' Copy selected subform records for new main form
' record.
'--------------------------------------------------------------------
CurrentDb().Execute "INSERT INTO OrderDetails " & _
"(ProductID, UnitPrice, Quantity, Discount, OrderID) " & _
"SELECT ProductID, UnitPrice, Quantity, Discount, " & nFKey & _
" FROM OrderDetails " & _
"WHERE (ODID IN (" & sList & "));", dbFailOnError
Me.Requery ' Display subform's new recs.
Set recSet = Me.RecordsetClone ' Use RecordsetClone of main form.
recSet.FindFirst "OrderID = " & nFKey ' Find newest record.
If (Not (recSet.NoMatch)) Then
Me.Bookmark = recSet.Bookmark ' Jump to newest record.
End If
CleanUp:
If (fOpenedRecSet) Then
recSet.Close
fOpenedRecSet = False
End If
Set recSet = Nothing
Exit Sub
ErrHandler:
If (Err.Number = 3021) Then
MsgBox "Please select a record.", vbInformation + vbOKOnly, _
"No Record Selected"
Else
MsgBox "Error in CopyBtn_Click( ) in" & vbCrLf & _
Me.Name & " form." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
End If
Err.Clear
GoTo CleanUp
End Sub ' CopyBtn_Click( )
In this case, we need a comma and space after listing each primary key in the sList string because we'll use this string in the WHERE clause for a query later. We also record the CustomerID so that we can use this later for the new record, because enforced referential integrity requires that there be a related customer record for every order record. (In other words, every order must have a customer to purchase the items and have these items shipped to that customer.) We'll change this customer's CustomerID if the copied records in the subform are for a different customer's order.
After creating the new record, at least one of the fields must be filled in to ensure that a new AutoNumber is assigned to the primary key, which can then be used as the foreign key for the related records (in the subform control) that are about to be copied for this new order. In this case, we conveniently needed a related record in the Customers table, so we used the CustomerID field entry to produce a new AutoNumber in the OrderID field. If the Orders and Customers tables didn't have referential integrity enforced, then using a Default Value Property of =Date( ) for the OrderDate field would have sufficed for generating a new AutoNumber in the OrderID field for the new record.
Once this new record is saved in the Orders table, related records can be created in the OrderDetails table. We can easily achieve this by using an action query to insert these new records. Once the new related records are created, the subform won't reflect the new records unless the table is requeried. However, once the subform requeries the table, the cursor remains on the first record of the Recordset. To return to the newly created record, the form's Bookmark Property is used.
To view this in action, we open the frmOrders form in Form View and move to the third record for a previous order made by Hanari Carnes, then select the second and third records listed in the subform control, then select the "Copy" button to copy both of these products for a new order.
On the main form (frmOrders), we replace the CustomerID on this new record with ERNSH for Ernst Handel's new order, and his customer information immediately fills in the rest of the main form. Other items in the form can be filled in by the user as needed.
So the next time you find yourself adding a multicolumned list box on a form so that the user can view and see the attributes (fields) of the records in the list box, consider using a subform, because you now know how to determine which records were selected in the subform and use this information programmatically.
You can read about your favorite expert contributors.
Copyright © 2005 - 2006 Q-Built Solutions. All rights reserved.
 Top
Visitors since 23 Nov. '05:
|
