VBA - Accessing a collection of values

Question

I have two access forms. frm_Main and frm_Sub which has data conditionally displayed depending on the selections of the main form. I need to write a select all function for items displayed in frm_Sub. In VBA is there a way that I can get a list of the id's currently being displayed in frm_Sub?

for example, if I do this

me.controls("Svc_Tag_Dim_Tag_Num").value

I get the value for one of rows in the frm_Sub, is there a way to get all of the values?

Maybe I can ask a different way. I have a form that is displayed as a listview, in VBA, is there a way to get all the values from a specific column?

Answer 1

If I understand your question you should be able to access the ID value in the control using Column(x) where x indicates the control's row source column starting from 0. For example, if ID is in column 0 with width 0 in. it will be hidden from view but VBA can "see" it as me.controls.["Svc_Tag_Dim_Tag_Num"].column(0).

To get at the sub-form's record source directly from outside the form's class module you could create a function something like:

Public Function test_get_sub_form_record_set() As String 
    Dim dbs As Database
    Dim rst As Recordset
    Dim xcat As String
    Set dbs = CurrentDb()
    Set rst = [Forms]![Your Main Form Name]![Your Sub-form Name].[Form].RecordsetClone
    If rst.RecordCount > 0 Then
        rst.MoveFirst
        xcat = rst!ID
        rst.MoveNext
        Do While Not rst.EOF
            xcat = xcat & ", " & rst!ID
        Loop
    Else
        xcat = ""
    End If
    test_get_sub_form_record_set = xcat
    rst.Close
    Set dbs = Nothing
End Function

This would be included in a separate module and when called would return a concatenated, comma separated list of ID's.

Answer 2

Another option is to write a function that concatenates the PKs of the recordset that the subform displays. Say your main form is Company and your subform lists Employees. You'd have a LinkMasterFields and LinkChildFields properties of the subform control would be CompanyID (PK of the Company table, FK of the Employees table).

Thus, to get the same set of records as is displayed in the subform when the parent is on a particular Company record:

  Dim db As DAO.Database
  Dim strSQL As String
  Dim rst As DAO.Recordset
  Dim strEmployeeIDList As String 

  Set db = CurrentDB
  strSQL = "SELECT Employees.* FROM Employees WHERE CompanyID="
  strSQL = strSQL & Me!CompanyID & ";"
  Set rs = db.OpenRecordset(strSQL)
  If rs.RecordCount > 0 Then
     With rs
       .MoveFirst
       Do Until .EOF
         strEmployeeIDList = strEmployeeIDList & ", " & !CompanyID
         .MoveNext
       Loop
     End With
     strEmployeeIDList = Mid(strEmployeeIDList, 3)
  End If
  rs.Close
  Set rs = Nothing
  Set db = Nothing

Now, why would you do this instead of walking through an already-opened recordset (i.e., the subform's RecordsetClone)? I don't know -- it's just that there may be cases where you don't want your lookup to be tied to a particular form/subform. You could fix that by making your function that concatenates accept a recordset, and pass it a recordset declared as I did above, or pass it the subform's RecordsetClone. In that case, you could use the concatenation function either way, without being tied to the form/subform.