Record selection losing focus on Subform when button click on main form

[thayes5150]

I have an access database that uses a subform to display records, one column of which is charge data (currency). My users need to verify the charges against the paper form they are scanned from, so I would like to allow them to select a subset of these charges (shift click) and sum them into a message box to verify page by page that the records are correct, or find incorrect records. I found the following vba code and added it to the db

On the subform to set the record set

Private Sub Form_Click()
 
CurrentSelectionTop = Me.SelTop
CurrentSelectionHeight = Me.SelHeight
 
End Sub

On the main form to total the selected records and return the sum to a msgbox(error handling commented out for dev purposes):

Private Sub btnRecordSum_Click()
'On Error GoTo Err_btnRecordSum_Click
 
Dim curTotal As Currency
Dim lngFirstRec As Long
Dim lngNRecs As Long
 
With Me![SutaList].Form
'Forms![frmMaster]![frmCar]
 
lngFirstRec = CurrentSelectionTop
lngNRecs = CurrentSelectionHeight
 
If lngNRecs > 0 Then
 
With .RecordsetClone
 
.AbsolutePosition = lngFirstRec - 1
 
While lngNRecs > 0
curTotal = curTotal + Nz(!Amount, 0)
lngNRecs = lngNRecs - 1
If lngNRecs > 0 Then
.MoveNext
End If
Wend
 
End With
 
End If
 
End With
'Exit_btnRecordSum_Click:
   ' Exit Sub
 
'Err_btnRecordSum_Click:
  '  MsgBox Err.Description
   ' Resume Exit_btnRecordSum_Click
 
MsgBox "The total is " & curTotal
End Sub

My problem is that when I click the btnRecordSum, the selected rows become unselected, and the msgbox returns an amount of 0 which is correct for no selection. Am I missing something obvious here? How do I retain the selection after the subform loses focus?

[thayes5150]

Sorry, Access 2003, Datasheet form

[ADezii]

@thayes5150
I don't think that you can maintain the Records Selection once you leave the Sub-Form, but what you can do is:

1. Include a Field named [Sum], [Yes/No displayed as Check Box], in the Record Source for your Sub-Form.
2. Include the Field in the Sub-Form.
3. Have the User select as many Fields as he/she wants on the Sub-Form to be totalled.
4. Run code similar to the following (Order Details Extended Query of Northwind) to generate a Total for the Records selected.
   

   MsgBox "Total Price for all selected Items: " & _
           Format$(Nz(DSum("[ExtendedPrice]", "Order Details Extended", "[Sum]=True")), "Currency")

5. Reset all [Sum] Fields to False.
6. This theory has been tested and does work.

[thayes5150]

Unfortunately the data in question is sometimes 20 -30 pages with 50 records on each page and I don't know of a way to mass select the check boxes (as you can drag select records in a data sheet) This means they would need to check 50 boxes, then sum, then check 50 more etc for all 20 -30 pages. Is it possible to add a keypress event, maybe on the S key so when they select the records and press S it pops the sum message?

[ADezii]

@thayes5150
This theoretically may work, and initial tests indicates that it may.

1. Set the Key Preview Property of the 'Main' Form to No.
2. Set the Key Preview Property of the 'Sub-Form' to Yes.
3. Write code in the KeyDown() Event of the Sub-Form to Capture the ALT+S Keystroke combination (Not S). The code is as follows:
   

   Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
   If (Shift And acAltMask) > 0 And (KeyCode = vbKeyS) Then
     '...ALT+S pressed, Msgbox and code processing here
   End If
   End Sub

4. Have the User select the Records, then press the ALT+S Key combination in order to execute the code contained within.
5. Let me know how you make out.

[thayes5150]

First I want to thank you for all of your help so far, The code you provided seems to work fine, and fires the btnRecordSum_Click code I included before, but the total still comes up as zero, despite the selected records. If I uncomment the error code the message box does not pop, which makes me think there is a problem in the code that sums the records, but it is also not popping the Error message box, so I don't know where the issue lies. I think we are very close on this. Any ideas?

[ADezii]

@thayes5150
I've actually got the code working quite well. I based my Demo on the Orders Subform in the Orders Table of the Northwind Sample Database. It will Total the [ExtendedPrice] Field for all those Records selected in the Subform. I'll post the code and also Attach the Demo Database in case you have any doubts. Let me know how you make out on this.

Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
If (Shift And acAltMask) > 0 And (KeyCode = vbKeyS) Then
  Dim curTotal As Currency
  Dim lngFirstRec As Long
  Dim lngNRecs As Long
 
  lngFirstRec = Me.SelTop
  lngNRecs = Me.SelHeight
 
  If lngNRecs > 0 Then
    Me.RecordsetClone.AbsolutePosition = lngFirstRec - 1
    With Me.RecordsetClone
      .AbsolutePosition = lngFirstRec - 1
 
        While lngNRecs > 0
          curTotal = curTotal + Nz(![ExtendedPrice], 0)
          lngNRecs = lngNRecs - 1
            If lngNRecs > 0 Then
              .MoveNext
            End If
        Wend
    End With
  End If
 
  MsgBox "Total Extended Price: " & Format$(curTotal, "Currency")
End If
End Sub

[thayes5150]

I actually just figured it out as I received your e-mail. Stupid copy/paste error on my part - when I brought the line curTotal = curTotal + Nz(!Amount, 0) into my code I forgot to change the field name from Amount to TtlChg, updated that and it works like a champ. Thanks again for all of the help.

[ADezii]

@thayes5150
You are quite welcome.

[NeoPa]

The deselection problem is due to moving away from your subform (I'm pretty sure). A simple way to get around this might be to trigger the code from this subform (rather than from the main form as originally). The keystroke approach does this of course, but is possibly more complicated than you need.

If you had a Command Button on the subform (in a Header or Footer section) then I expect your original concept should work fine.

Of course, if you particularly like the keystroke method, or even just like knowing how that can be implemented, well then ADezii's response gives you that too :)

[thayes5150]

Thanks again everybody for all the help - The subform is really just a Datasheet view of the underlying table and so Buttons on the subform are not an option. Also I kind of like the Alt -S option. It's kind of elegant.