Strange Empty Recordset.

[Royce]

I have a form on a subform on a subform. The Master and Child Links are null. Filter On Load is off, as is the filter, and the filter is blank.

The code below sets the recordsource property to a SQL string.

I can copy the contents of strSQL to a query and one record is displayed. The DCount always returns the expected count of records. But the form recordset appears to be empty. (The function that calls FilterDuplicates use .bof, .eof and .RecordCount .bof and .eof are always true, .RecordCount is zero. I've tried code that has worked a 1000 places before for the testing.)

What am I missing?

Private Function FilterDuplicates(eMatchBy As enuMatchBy) As Long
    Dim strFilter As String
    Dim strSQL As String
 
    Dim lngID As Long
    If HasParent(Me) Then
        lngID = Nz(Parent.ImportContactMatchingID, 0)
        strFilter = MatchFilter(eMatchBy, lngID) ' Build the filter based. eMatchBy is an enum that defines a set of matching options.
       ' strFilter is typically something like "FedTaxID ='xxx-xx-xxxx'" at this point. 

        strFilter = strFilter & " AND ImportContactMatchingID <> " & CStr(lngID) ' Filters out the record we are testing for match/duplicate.
        strSQL = "SELECT * FROM ImportContactMatching WHERE " & strFilter
     
        Me.RecordSource = strSQL
        Me.Requery ' Shouldn't need this, as setting the Record Source will force a requery, but I'm trying everything I can think of.
    '    Me.Filter = vbNullString ' Originally I just set the filter, but I had the same problem. So I tried setting the recordsource. (Previous line)
    '    Me.FilterOn = False
        Debug.Print Me.Name, Me.Recordset.BOF, Me.Recordset.EOF, Me.RecordSource, DCount("ImportContactMatchingID", "ImportContactMatching", strFilter)
        FilterDuplicates = DCount("ImportContactMatchingID", "ImportContactMatching", strFilter)
    Else
        FilterDuplicates = 0
    End If
End Function

 

[The_Doc_Man]

When do you trigger this call? I.e. in/from which event? I would concur that changing a .RecordSource should trigger a .Requery for the changed object. I see that you have a Debug.Print that includes Me.RecordSource ... so when you run this, what gets printed in the immediate window? Probably wouldn't hurt to add "Me.strFilter" and "Me.strSQL" to that list to assure that nothing gets dropped.

 

[Royce]

When do you trigger this call? I.e. in/from which event? I would concur that changing a .RecordSource should trigger a .Requery for the changed object. I see that you have a Debug.Print that includes Me.RecordSource ... so when you run this, what gets printed in the immediate window? Probably wouldn't hurt to add "Me.strFilter" and "Me.strSQL" to that list to assure that nothing gets dropped.

It gets called from cboLookup_AfterUpdate().
debug.print prints this:
fsubImportContactMatchPotentialDuplicates True True SELECT * FROM ImportContactMatching WHERE FedTaxIDorSSN = '167-44-6727' AND ImportContactMatchingID <> 65643 1

I have modified the code slightly (hard code lngID) and opened it as a standalone form. Called it from a double click, and it will then show the expected records.

If I open the subform directly (effectively making it the main form) the problem shows up.

I've looked at strFilter, strSQL in the immediate window and they look good. (I can copy and paste strSQL into a query in SQL view and it will return the expected records.)

I've also tried the /decompile startup option.

It reminds me of the Access bug that can leave garbage in the value list of a combo that does not show up when you view the properties. I keep wondering if there is hidden garbage in the Link Master and Link Child fields.

 

[Royce]

It is a Microsoft Bug!!!! This explains more: https://stackoverflow.com/questions...s-being-automatically-set-when-changing-recor

Adding the reset of the Links, even though they appear to be blank, after setting the RecordSource fixed the problem.

Me.RecordSource = strSQL
    Parent.fsubImportContactMatchPotentialDuplicates.LinkChildFields = vbNullString
    Parent.fsubImportContactMatchPotentialDuplicates.LinkMasterFields = vbNullString
[/CODE}