Stumped: Query Results in VBA differ from Access UI query

[tjduhon]

I have a simple SELECT query defined in code that is resulting with one record; should be ten.

When I create a query with the same SQL in the Access database window, I get the full set of ten records.

I have no compile issues. I use the two parameters as 'interactive text' rather than true parameters. I do not get the Error 3061 b.s.

SampQry = "SELECT LabSample.ClientID, LabSample.TcgProjectID, LabSample.TcgSdgID, LabSample.Fraction, LabSample.LabSampleID FROM LabSample "
SampQry = SampQry & "WHERE (((LabSample.TcgSdgID)='" & [Forms]![SDG_BuildAnalyteList]![ActiveSDG] & "') AND ((LabSample.Fraction)='" & [Forms]![SDG_BuildAnalyteList]![PikdFrac] & "'));"
 
Set NewSampSet = wkbase.OpenRecordset(SampQry, dbOpenDynaset)
 

This is the SQL view of the query defined in the database:

SELECT LabSample.ClientID, LabSample.TcgProjectID, LabSample.TcgSdgID, LabSample.Fraction, LabSample.LabSampleID
FROM LabSample
WHERE (((LabSample.TcgSdgID)=[Forms]![SDG_BuildAnalyteList]![ActiveSDG]) AND ((LabSample.Fraction)=[Forms]![SDG_BuildAnalyteList]![PikdFrac]));
this query gives me the 10 records I seek

In the code, right after the NewSampSet, I have a second recordset defined by a similar query string using the same "text" parameters (w/ 25 records resulting) and have no issue with that one.

I'm stumped. Any help is appreciated. HELP!! :0)

[tjduhon]

I found the solution: (Haven't had to do this trick in a long time) After defining the recordset, I had to use the .MoveLast method to 'account' all of the records.
This may have only been necessary to get the correct .RecordCount

I found a logic error in my inside loop: I needed to .MoveFirst (reset) the pointer in my 2nd recordset. By not doing this, the 2nd pointer was at .EOF, so there was nothing available to repeat the actions of the 1st iteration of the 1st recordset.

[Killer42]

Yes, this has been a constant nuisance to me over the years. When you first execute the query, the record count you get back is either 0 (no matches) or 1 (something matched). The matching records are only counted as they're accessed, so if you need the count up front you need to do something like

With NewSampSet
  If .RecordCount > 0 Then
    .MoveLast
    .MoveFirst
  End If
End With