0

Objective: Set a recordset using a query string, then perform FindFirst = number to find a specific ID, and therefore record, in the recordset.

Problem: The recordset is generated correctly but FindFirst consistently, incorrectly matches IDs below a certain value.

Description: When I create a recordset inner (or left or right) joining Task and Task_Entry matching specific CycleID and TaskDataID values I get a list of records (in my recordset, I ensure TaskID isn't ambiguous). If I FindFirst any value below 1469, FindFirst returns the 1469 record, every time. See example records and structure below.

Original Query (put into a CurrentDB.OpenRecordset()):
"SELECT Datetime, TaskDataID, SigID, Task_Entry.* FROM Task INNER JOIN Task_Entry ON Task_Entry.TaskID = Task.TaskID WHERE Task.CycleID = " & inputCycleID & " AND Task.TaskDataID = " & inputTaskDataID & " ORDER BY Task.TaskID"

Table Structure:

  • Two tables bound via autonumber ID field:
  • Task contains general task information (date time, signature ID, etc)
  • Task_Entry contains a subset of Entry specific information
  • Not all records in Task are related to Task_Entry

Example records:
Task: TaskID, Datetime, TaskDataID, CycleID, SigId
1447 03/09/16 15 7 1495 (the TaskDataID was different)
... other records
1469 03/15/16 15 8 1518
... other records

Task_Entry: TaskID, Data1, Data2
1447 929 930.5
1469 919 922

Troubleshooting:

  • Originally performed FindFirst in VBA using: rst.FindFirst "TaskID = " & inputTaskID where inputTaskID was a variant.
    • The datatype did not matter. Forced datatype to Long and still matched 1447 to 1469 somehow.
  • Changed recordset to only return TaskID = inputTaskID.
    • "SELECT Datetime, TaskDataID, SigID, Task_Entry.* FROM Task INNER JOIN Task_Entry ON Task_Entry.TaskID = Task.TaskID WHERE Task.TaskID = " & inputTaskID
    • This worked; returns one record matching inputTaskID.
    • I have use cases for returning the entire set the matches CycleID and TaskDataID, so I would still like to know why this doesn't work.
Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
usncahill
  • 469
  • 6
  • 16
  • Do you see the same behavior with a simpler "select distinct TaskId from Task" query? – Tim Williams Jul 08 '16 at 18:50
  • Yes. Just ran the above 'original query' adding DISTINCT and removing the ORDER BY (because it threw an error about a conflict). Same mismatch 1469 = 1447. – usncahill Jul 08 '16 at 18:53
  • What's the value of `rst.NoMatch` when that happens ? Does the value of the Field change from before performing the Findfirst? – Tim Williams Jul 08 '16 at 18:54
  • Aaaa! True?!?! Why is it true?! And why would it return a wrong number. Ugh! – usncahill Jul 08 '16 at 18:56
  • 2
    It's true because there is no match (so the current record has not changed). As for why there's no match - if you print out all of the values is the one you're looking for in the output? – Tim Williams Jul 08 '16 at 18:57
  • @TimWilliams You're right. It's not in there; constraining the data too much with the original query. That's why the second query works; I'm removing the TaskDataID constraint which changed for earlier entries (my fake data posted above is wrong). So it returns (or just remains on) the first record of reoordset? – usncahill Jul 08 '16 at 19:00
  • Are you specifying what type of recordset you want when you execute the query? I think FindFirst only works with certain types (Dynaset, Snapshot) – Tim Williams Jul 08 '16 at 19:00
  • @AndrewMorton you're right. I was sloppy modifying the code to post here. It is correct in the database. I will update above. – usncahill Jul 08 '16 at 19:03
  • Problem solved. @TimWilliams care to post an answer or should I summarize? – usncahill Jul 08 '16 at 19:04
  • Feel free to post an answer... – Tim Williams Jul 08 '16 at 19:08

1 Answers1

0

When FindFirst is used on a recordset and results in NoMatch being true, the recordset remains on the current record.

In this specific case, the original query over-constrained the records on the TaskDataID parameter returning a set of records not containing the ID being searched for. Performing FindFirst left the recordset on the first record (which in this case was the one with TaskID = 1469).

usncahill
  • 469
  • 6
  • 16