moving first moving last in code  : code revision

Fordraiders
Fordraiders used Ask the Experts™
on
moving first moving last in code  : code revision
In the code below, since i'm getting the max VALUE in the sql.
Is  is necessary for me to use,
r.MoveLast
r.MoveFirst

and i'm not using the tp  = r.recordcount


StrSqlC = "SELECT Max(dbo_t_nsc_trackcode_assigned_DataEntry.NSC_Id) AS MaxOfNSC_Id " & _
"FROM dbo_assigned_DataEntry" & _
" WHERE (((dbo_assigned_DataEntry.ID_Racfid)=  '" & str & "' ));"
             Set r = CurrentDb.OpenRecordset(StrSqlC, dbOpenDynaset, dbSeeChanges)
r.MoveLast
r.MoveFirst
tp = r.RecordCount
r.MoveLast
MaxId_csp = r.Fields(0)
r.Close
Set r = Nothing

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
No.   No move first, last, etc required.

 Just:

  If not r.eof then
    MaxID_cap = r.Fields(0)
  End If
You only need movelast and movefirst to fully populate the recordset, in cases where you need an accurate recordcount.

For simple lookups like this, I would just use the Dmax function. Less lines of code :)
Software & Systems Engineer
Commented:
Easy to check...just compare the Value from Max and Recordcount...if they are the same you are OK...proceed and delete the "extra" code"...If NOT....

Author

Commented:
Thanks for replying.
All
Mark EdwardsChief Technology Officer

Commented:
Your query always gives you 1 record, even if all records are filtered out, in which case the value of Field(0) is null.
As mentioned before by others, no need to move anywhere or test for the presence of a record, just make sure you aren't at EOF or BOF of the recordset.
Just make sure your code is prepared to handle a null Field(0) value.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial