Create rs by selection (A2K)

Topic

New Reply

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.

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

Reply | Quote

Replies

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?

Reply | Quote

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.

Reply | Quote