Returning selected records in subform.

[peryan77]

I have an unbound main form and a subform bound to a table. I want to return the selected records in the subform so I can run a query against them. I have the record selecter set to true.

I followed the directions of this microsoft article, note at the end it tells you how to call the function with a subform. However, it does not tell you how to modify the function DisplaySelectedCompanyNames () to use from a SUbform. Every time I tried to get recordset of subform I get an error. I used every possible combination.

I will be greatly impressed if someone can get this. I know I could you an activeX control like ListView but it is much slower.

http://support.microsoft.com/?kbid=208502

 

[raylitalo]

The way I've handled this kind of problem in the past is to build a temporary table from the subform. Set the subform's "Modal" and "Popup" Properties are True, and open the subform modally, so execution of your code waits for the form to close. In the following example, I am using DAO as my database programming interface:

Private Sub cmdOpenSubForm_Click()
    On Error GoTo Err_cmdOpenSubForm_Click
    
    Dim rs As DAO.Recordset
    Dim strSQL As String
    
    DoCmd.OpenForm "frmSubForm", acNormal, , , , acDialog
    
    strSQL = "SELECT * FROM tmpSelectedRecords WHERE bPaid = TRUE;"
    Set rs = CurrentDb.OpenRecordset(strSQL)
    
Exit_cmdOpenSubForm_Click:
    Exit Sub

Err_cmdOpenSubForm_Click:
    MsgBox Err.Description
    Resume Exit_cmdOpenSubForm_Click
    
End Sub

 

[batteam]

Here's my two cents:
I have built a few databases with a subform included in a main user form. I have a query that gives results (a recordset) as the recordsource for my subform (bound to it). Then, whenever the user selects the criteria that determines which records to show on the subform, I simply requery the subform (which runs the recordsource query) via VB code at the appropriate place behind my main form.

An example: one of my users selects an employee. To see all the states that the chosen employee holds licenses in(insurance licenses, for example), I simply send that employee's id over to my parameter query, which is my record source for my subform, then requery it (the subform), like:

mysubformname.Requery

Those records (in my example, states,) will then populate my subform. Ensure your subform's child and link properties are correctly labeled/named correctly (in the subform's property box).

Hope this helps in some little way. Good luck.

 

[raylitalo]

I like your idea batteam, and have even used a similar approach myself in a popup subform that was supposed to help find people in the bound main form. In the subform I collected details about the person(s) being saught, and built an SQL "Where" clause and applied that to the filter of the main form:
(note that I have already applied my SQL Filter to the subform's recordset when the "OK" button is clicked on my subform, and that my subform begins with essentially the same recordset as my main form.)

Private Sub cmdOK_Click()

    On Error GoTo Err_cmdOK_Click
    Dim rs As DAO.Recordset
    Dim strBookmark As String
    Dim frm As Form
    
    Set rs = Me.RecordsetClone
    If rs.BOF And rs.EOF Then
        DoCmd.Close
    End If
    
    Set frm = Forms!frmPeople.Form
    Set rs = frm.RecordsetClone
    
    rs.FindFirst "autPersonID = " & Me.autPersonID
    
    If Not rs.NoMatch Then
        strBookmark = rs.Bookmark
        frm.Bookmark = strBookmark
    End If

    DoCmd.Close acForm, Me.Name

Exit_cmdOK_Click:
    Exit Sub

Err_cmdOK_Click:
    Dim e As New CErr
    e.Msg Me.Name, "cmdOK_Click"
    Resume Exit_cmdOK_Click

End Sub

 

[peryan77]

Your solutions will not work because my main form is unbound. It includes combo's to filter records for the subform. However, after the filter is applied I want a user to be able to select any records in a subform datasheet and I want them to be able to query against that. Unfornately, the recordselector is a pointless function if you can't use it to return selected records like you can with a listbox.

Maybe I should just use the listview control, but it loads slow when I have 16,000 records.

 

[batteam]

In my response, my main form was unbound. In my example, if I select a value from a combo box, it will filter records for my subform, just like, I think, you want to do. My subform is bound, bound to a query that runs from the choice the user makes in the combo box in the main form. My subform is then requeried to show the appropriate records.

What do you mean when you say you want your users to be able to select any records in the subform and 'run a query against them'? Do you want them to click on a field in the subform then have a query run and pop up in grid view showing some results? Because you can easily send a parameter over to one of these queries from a chosen value/textbox/combobox on your subform, then have that query run - maybe on the lost_focus event of the subform control they click, using the DoCmd.OpenQuery "yourqueryname" method.

Keep replying and maybe we can still figure something out.

 

[raylitalo]

I wonder if you could just build a filter from within your popup form, and apply your filter against the control on your main form?

(Just roughing my idea in here...)

Sub cmdCloseForm_OnClick()
   Dim strSQL As String
   strSQL = BuildFilterFromControls()
   If Len(strSQL) > 0 Then
       Forms!frmMain.Controls!ctlDisplayRecords.ControlSource = strSQL
   End If
End Sub