470,620 Members | 1,524 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,620 developers. It's quick & easy.

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

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

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Click()
  2.  
  3. CurrentSelectionTop = Me.SelTop
  4. CurrentSelectionHeight = Me.SelHeight
  5.  
  6. 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):


Expand|Select|Wrap|Line Numbers
  1. Private Sub btnRecordSum_Click()
  2. 'On Error GoTo Err_btnRecordSum_Click
  3.  
  4. Dim curTotal As Currency
  5. Dim lngFirstRec As Long
  6. Dim lngNRecs As Long
  7.  
  8. With Me![SutaList].Form
  9. 'Forms![frmMaster]![frmCar]
  10.  
  11. lngFirstRec = CurrentSelectionTop
  12. lngNRecs = CurrentSelectionHeight
  13.  
  14. If lngNRecs > 0 Then
  15.  
  16. With .RecordsetClone
  17.  
  18. .AbsolutePosition = lngFirstRec - 1
  19.  
  20. While lngNRecs > 0
  21. curTotal = curTotal + Nz(!Amount, 0)
  22. lngNRecs = lngNRecs - 1
  23. If lngNRecs > 0 Then
  24. .MoveNext
  25. End If
  26. Wend
  27.  
  28. End With
  29.  
  30. End If
  31.  
  32. End With
  33. 'Exit_btnRecordSum_Click:
  34.    ' Exit Sub
  35.  
  36. 'Err_btnRecordSum_Click:
  37.   '  MsgBox Err.Description
  38.    ' Resume Exit_btnRecordSum_Click
  39.  
  40. MsgBox "The total is " & curTotal
  41. 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?
Nov 11 '09 #1

✓ answered by 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:
    Expand|Select|Wrap|Line Numbers
    1. Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
    2. If (Shift And acAltMask) > 0 And (KeyCode = vbKeyS) Then
    3.   '...ALT+S pressed, Msgbox and code processing here
    4. End If
    5. 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.

10 6581
Sorry, Access 2003, Datasheet form
Nov 11 '09 #2
ADezii
8,804 Expert 8TB
@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.
    Expand|Select|Wrap|Line Numbers
    1. MsgBox "Total Price for all selected Items: " & _
    2.         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.
Nov 12 '09 #3
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?
Nov 12 '09 #4
ADezii
8,804 Expert 8TB
@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:
    Expand|Select|Wrap|Line Numbers
    1. Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
    2. If (Shift And acAltMask) > 0 And (KeyCode = vbKeyS) Then
    3.   '...ALT+S pressed, Msgbox and code processing here
    4. End If
    5. 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.
Nov 12 '09 #5
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?
Nov 12 '09 #6
ADezii
8,804 Expert 8TB
@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.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
  2. If (Shift And acAltMask) > 0 And (KeyCode = vbKeyS) Then
  3.   Dim curTotal As Currency
  4.   Dim lngFirstRec As Long
  5.   Dim lngNRecs As Long
  6.  
  7.   lngFirstRec = Me.SelTop
  8.   lngNRecs = Me.SelHeight
  9.  
  10.   If lngNRecs > 0 Then
  11.     Me.RecordsetClone.AbsolutePosition = lngFirstRec - 1
  12.     With Me.RecordsetClone
  13.       .AbsolutePosition = lngFirstRec - 1
  14.  
  15.         While lngNRecs > 0
  16.           curTotal = curTotal + Nz(![ExtendedPrice], 0)
  17.           lngNRecs = lngNRecs - 1
  18.             If lngNRecs > 0 Then
  19.               .MoveNext
  20.             End If
  21.         Wend
  22.     End With
  23.   End If
  24.  
  25.   MsgBox "Total Extended Price: " & Format$(curTotal, "Currency")
  26. End If
  27. End Sub
Attached Files
File Type: zip Subform Totals.zip (151.2 KB, 171 views)
Nov 12 '09 #7
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.
Nov 12 '09 #8
ADezii
8,804 Expert 8TB
@thayes5150
You are quite welcome.
Nov 12 '09 #9
NeoPa
32,301 Expert Mod 16PB
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 :)
Nov 19 '09 #10
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.
Nov 19 '09 #11

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

7 posts views Thread by damjanu | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.