Results 1 to 13 of 13
  1. #1
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Selected records in many Subform (XP/2K)

    I have a subform that has the payments made for a purchase on the main form. I need to be able to now write a receipt for those payments. If I write one receipt for all the payments that is easy. Sometimes, I need to write different receipts for the different payments. I would like to select some of the records in the subform, and use only those to write a receipt. How would I get an array or recordset of only the selected records?
    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Selected records in many Subform (XP/2K)

    Is this for a multi-user situation?

  3. #3
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Selected records in many Subform (XP/2K)

    (Edited by HansV to make URL clickable - see <!help=19>Help 19<!/help>)

    Yes, just to be safe. I just saw http://support.microsoft.com/?kbid=208502 which discussed using SelTop to do this, and the workaround. I did not think of the multi user aspect.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Selected records in many Subform (XP/2K)

    If it's ok to you if the user can only select a contiguous range of records, you can use SelTop and SelHeight. You could put a Yes/No field in a table with just one record to keep track of whether anyone is processing receipts this way. Check the status of this field, and get out if it is already True. If it is False, set it to True, process the selected records, then set it to False again for the next time/next user.

  5. #5
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Selected records in many Subform (XP/2K)

    Can I set a Public recordset variable, step through the records and save them into that variable, and set that new recordset as the source for a report?
    Thanks

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Selected records in many Subform (XP/2K)

    I don't think it will work that way, but perhaps somebody else knows how.

    I would do the following. You need a unique identifier for the records, for example the primary key. Say that records with ID 37, 52, 73, 75 and 82 have been selected. You could open a report and pass "[ID] In (37, 52, 73, 75, 82)" as Where-condition to DoCmd.OpenReport.

    Here is a function inspired by the MSKB article. You must substitute the names you are using.

    <img src=/w3timages/blueline.gif width=33% height=2>

    Function GetFilter()
    Dim i As Long
    Dim F As Form
    Dim RS As DAO.Recordset
    Dim strWhere As String

    ' Get the form and its recordset.
    Set F = Forms![Customers1]
    Set RS = F.RecordsetClone

    ' Move to the first record in the recordset.
    RS.MoveFirst

    ' Move to the first selected record.
    RS.Move F.SelTop - 1

    ' Loop through selected records
    For i = 1 To F.SelHeight
    strWhere = strWhere & ", " & RS![ID]
    RS.MoveNext
    Next i

    GetFilter = "[ID] In (" & Mid(strWhere, 3) & ")"
    End Function

    <img src=/w3timages/blueline.gif width=33% height=2>

    If the ID field is a text field, you must use

    strWhere = strWhere & ", " & Chr(34) & RS![ID] & Chr(34)

    Open the report as follows:

    DoCmd.OpenReport ReportName:="rptMyReport", View:=acViewPreview, WhereCondition:=GetFilter

  7. #7
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Selected records in many Subform (XP/2K)

    brilliant!

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Selected records in many Subform (XP/2K)

    You can build an ADO recordset that way, but I would recommend using Hans's suggestion of the WhereCondition instead. The "recordset on the fly" method is far more complicated.
    Charlotte

  9. #9
    New Lounger
    Join Date
    Mar 2004
    Location
    London, Gtr London, England
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selected records in many Subform (XP/2K)

    Hi all,

    I have been a good boy and found this previous thread, which looks very much like the solution to my problem, which is running through a multiple selection on a datasheet after a button has been clicked.

    I have put something very similar to this code http://www.wopr.com/cgi-bin/w3t/showthread...vc=1#Post333120 into a module and it is working, but only up to a point. The problem is that I can pickup the F.SelTop property, but the F.SelHeight remains as 0.

    I have had a look at the microsoft knoweldgebase http://support.microsoft.com/?kbid=208502 and they seem to be saying that it is much more complex. Do I need to go to the lengths described by Microsoft or have you found a better way?

    Thanks for your help.

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Selected records in many Subform (XP/2K)

    As indicated in the MSKB article, the moment you click a command button, the selection loses focus, and SelHeight becomes 0. One way to get around it is to use a keyboard shortcut or a custom toolbar button to initiate whatever action you want to take (process the records, open a report based on the selection, ...) These don't take the focus away from the selection. If you want to use a command button, you need the more elaborate workaround described in the second part of the MSKB article.

  11. #11
    New Lounger
    Join Date
    Mar 2004
    Location
    London, Gtr London, England
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selected records in many Subform (XP/2K)

    Right, I'll just have to get my head around that then.

    Thanks Hans

  12. #12
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    Sacramento, CA
    Posts
    193
    Thanks
    3
    Thanked 1 Time in 1 Post

    Re: Selected records in many Subform (XP/2K)

    Is there another approach to be able to provide non-contiguous records? A List Box won't work for me in my current situation as the underlying recordset is too large. The subform/seltop-selheight works really well, but non-contiguous selection would be a very handy additional feature?

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Selected records in many Subform (XP/2K)

    If the recordset is too large for a list box, how do you envision the user making a selection anyway? You could use one or more combo boxes to let the user reduce the set of records displayed in the list box to something managable (select some kind of category, then perhaps a sub-category etc,, and display only the records from that categorry or sub-category in the list box).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •