Enumerate Key Values within Subform where duplicates may exist.

[ItsMe]

I have a subform that displays records to the user. The subform's recordset has a key that I need to append to a junction table. There will be instances where the recordset will contain duplicate values of this particular key. However, I do not wish to include duplicates in the append process. I need to enumerate the values contained within the key column without duplicating any of them.

I was thinking about using a temp table. With the temp table I could employ queries. As I consider the temp table approach, it seems to be no less cumbersome than using a VBA collection object. In my pursuit of using a collection, I came up with the following code and it works.

I try to stay away from GROUP BY in any queries. If I were to go the route of using a temp table and queries, I do not believe the dataset would be large enough for GROUP BY to be a performance issue. Does anybody have any thoughts or opinions? Maybe there is a more simple approach.

Dim lngPO_Key As Long
Dim colPO_Key As New Collection
Dim intCount As Integer
Dim bolNeedPO As Boolean

'rs is clone of subform
rs.MoveFirst

While rs.EOF = False

lngPO_Key = rs![PO_Key]

'Append the PO keys into a collection
intCount = colPO_Key.Count
bolNeedPO = True
    
    'Determine if the PO Key Value is included within the
    'collection and add it if it is not.
    If intCount > 0 Then
        intCount = colPO_Key.Count
        For i = 1 To intCount   '1 based index
            
            If colPO_Key.Item(i) = lngPO_Key Then
                bolNeedPO = False
            End If  'colPO_Key.Item(i)
        
        Next i
    End If  'intCount

If bolNeedPO = True Then
    colPO_Key.Add lngPO_Key
End If

rs.MoveNext
Wend

For i = 1 To colPO_Key.Count   '1 based index
'TODO Append records to junction table here.
Debug.Print colPO_Key.Item(i)
Next i

Exit Sub

[June7]

Where do you declare and set rs?

Consider:

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT DISTINCT [key field] FROM (" & Replace(Forms![form name].RecordSource, ";", "") & ") AS Q1;")

or

CurrentDb.Execute "INSERT INTO tablename([field name]) SELECT DISTINCT [key field] FROM (" & Replace(Forms![form name].RecordSource, ";", "") & ") AS Q1;"

I tested only the first.

Might need some filter criteria.

[ItsMe]

Where do you declare and set rs?...

There are other procedures that use the same recordset clone. So rs is declared and instantiated elsewhere. Other procedures share the same loop. There are several FK columns within the subform that I need to go after.


I think the following is proper.

Set rs = CurrentDb.OpenRecordset("SELECT DISTINCT [key field] FROM (" & Replace(Forms![form name].RecordSource, ";", "") & ") AS Q1;")

I could do something like this as a standalone process. In other words, the existing rs loop can remain and serve its purpose for the other key columns. I can create a second recordset, just like I was doing when I created the collection. So, no need to create the collection, create a recordset that references the form object. I had not seen that before. Interesting how you use the alias.

Is the alias totally necessary?

[ssanfu]

I would open a recordset in code with the DISTINCT keyword, then a loop to append to the junction table.

But I tend to thing in code.....


EDIT: I just saw your 2nd post. This probably wouldn't work if you wanted to get all of the FK fields at the same time.

[ItsMe]

I would open a recordset in code with the DISTINCT keyword, then a loop to append to the junction table.

But I tend to thing in code.....

I was trying to imagine a way to do that but could not think of a way to extract the recordset from the subform without a temp table. The method June pointed out seems like a good idea, though.

[ssanfu]

I wouldn't use the temp table. I would convert the subform record source to SQL in code and add the DISTINCT keyword. But I don't know what else is involved in what you are trying to do. (multiple FK fields)

[ItsMe]

...I would convert the subform record source to SQL in code...

EDIT: Nevermind, I know what you are saying, now.

Yes, that is an alternative and I started down that road. Because I was already looping through it, I deviated from using the original recordsource. That may still be a viable solution. But, I don't need every column in the subform. /EDIT

I don't think I know how to do that. Can you explain how?

[ssanfu]

I don't think I know how to do that. Can you explain how?

If I understood your question correctly:

What is the SQL of the subform?
Copy that and add the criteria that links the sub form to the main form.

Steps:......
If the SQL of the subform record source is

SELECT SubFormID_PK, MainFormID_FK, Field1, Field2, lngPO_Key, AnotherField_FK
FROM tblTable1

to change to VBA, use

  ssql = "SELECT SubFormID_PK, MainFormID_FK, Field1, Field2, lngPO_Key, AnotherField_FK
  ssql = ssql & " FROM tblTable1

Next add the criteria to link the main form to the sub form records (PK to FK)

   ssql = "SELECT SubFormID_PK, MainFormID_FK, Field1, Field2, lngPO_Key, AnotherField_FK
   ssql = ssql & " FROM tblTable1
   ssql = ssql & " WHERE MainFormID_FK = " & Me.MainFormID_PK

Now the records should match the sub form records.
So getting just the [PO_Key], change the SQL to

   ssql = "SELECT DISTINCT lngPO_Key
   ssql = ssql & " FROM tblTable1
   ssql = ssql & " WHERE MainFormID_FK = " & Me.MainFormID_PK

Opening a record set on the above SQL would result in a domain of unique lngPO_Key values.

Sub SomeCode()
    dim d as dao.database
    dim r as dao.recordset
    dim sSQL as string

    set d = currentdb

    ssql = "SELECT DISTINCT lngPO_Key
    ssql = ssql & " FROM sfSource"
    ssql = ssql & " WHERE MainFormID_FK = " & Me.MainFormID_PK

    set r = d.openrecordset(ssql)

  If not (r.bof and r.eof) then
      r.MoveFirst
      Do WHILE NOT r.EOF

         ' create append query here
   
         r.MoveNext
      Loop
   End If

   r.Close
   Set r = Nothing
   Set d = Nothing

End Sub

[ItsMe]

As I build this form I have been considering performance. I don't want to take extra trips to the data. This is why my example in post #1 uses a recordsetclone. I am avoiding taking an extra trip to the data but still get a list of the values I need. The only downside I see to the code in post #1 is that it is complex. If I am to revisit the code in a couple of years, it would take a while for me to understand what it does. It will use some extra memory, but it avoids taking a trip to the data.

If I am to take that extra trip to the data, I can use the example June posted. This will work when a subform has a recordsource that is SQL. If the recordsource is an object, you could simply use the name of the object in a SELECT DISTINCT statement. If the subform is employing the Filter property, you can append that SQL statement to your SELECT DISTINCT statement.
Something Like ...

Dim strWhere as string
strWhere = Me.Filter

What I am thinking to do now is bind the subform to a temp table. The purpose of the subform is to display changes to the user as the user makes the changes. So I think I will display the changes in the subform as a preview, of sorts. This way I can avoid complex queries and append records to the temp table in a non-normalized way. Then, the user can commit the changes when they close the form.

At the time the user closes the form, I can execute my procedures. I am still on the fence regarding the Recordsource of the subform vs. RecordsetClone of the subform. However, retrieving a single indexed field from a temp table does not sound very expensive. Plus, code using the Recordsource looks a lot cleaner.

[June7]

Put a lengthy set of comments in code to explain what it does.