Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Create rs by selection (A2K)

    Good Afternoon Everyone,

    I'm certain there is a way to do this, but I just don't know how.

    I have an unbound subform. The recordset to this subform is created on the fly depending on selections made by the user. All works well.

    The recordset(rs) has 8 fields and are:

    DxCode
    Primary_DxDescription--this is the primary diagnosis description field such as Depression
    Member_ID
    Member_Name
    DOB
    Age
    Gender
    RiskScore

    I will be adding a "Sub_DxDescription" field to the rs. This will house the sub categories to the Primary_DxDescription, such as Manic, Phych, etc. It will provide grouping of the Primary_DxDescription data.

    What I want to do is let the user HIGHLIGHT a range of data in the rs based on the Sub_DxDescription and export that data to Excel (see example attachment).

    I already have the code that creates the records source, I have the code that exports the data to Excel, what I don't have is the ability to create a new rs using the RECORDS SELECTED by the user from the original rs. Can this be done, and if so, any ideas on how?

    I thought about changing the subform to a list box and modifying the following code but just can't get a grasp on it.

    <pre>Dim ctl As Control
    Dim varItem As Variant
    Dim sTemp As String

    Set ctl = Me.ListBoxName


    For Each varItem In ctl.ItemsSelected
    Add to a tblDef or create a SQL statement and add it to
    the SQL statement.

    sTemp = sTemp & ctl.ItemData(varItem) & ", "

    Next varItem


    If sTemp = "" Then
    Else
    'Remove the last comma and space
    sTemp = Left$(sTemp, Len(sTemp) - 2)
    End If
    </pre>

    Attached Files Attached Files
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

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

    Re: Create rs by selection (A2K)

    Working with selected records in a continuous form isn't easy because it doesn't necessarily correspond to something you can express in terms of a query or SQL.

    One option would be to add a Yes/No field to ysnSelect the underlying table. It would be displayed as a check box in the subform.
    The user would tick the check box for the records to be selected. You can then add a condition to the SQL of the recordset: ysnSelect=True.

    But why not use a combo box or series of linked combo boxes if you want the user to select a single subcategory?

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create rs by selection (A2K)

    Hans,

    Thanks for the input. I've considered both of your suggestions, I was just needing someone with expertise to tell me what the most effecient method would be. I'm going drill down the data using multiple combo boxes.

    Again, thanks for the input.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

Posting Permissions

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