FindFirst then select most recent record (1 Viewer)

JahJr

Andy
Local time
Today, 01:35
Joined
Dec 3, 2008
Messages
93
I have a table, tblFees, that has the following columns:
ID = AutoNumber
AccountNumber = 8 AlphaNumeric digits
FeeChargedEffectiveDate = Short Date
DateEntered = Date and Time

I'm using the following to count how many times an account number chosen from a user form appears in the table, tblFees. If it appears only once then that record should be selected to populate fields on the user form. If the account number appears more than once then the record with the most recent FeeChargedEffectiveDate should be selected.

In the outside chance that there are multiple records with the same FeeChargedEffectiveDate then it should select the record with the most recent DateEntered.

Code:
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim AccountCount As Long
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("Select * from tblFees Where [AccountNumber] ='" & Me.cboAccountNumber & "'", dbOpenDynaset)
    rst.MoveLast
    AccountCount = rst.RecordCount
    Debug.Print AccountCount
    
    rst.Close
    Set rst = Nothing
    Set dbs = Nothing

I need help working with the dates, can't figure out what is the best way to handle this.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 23:35
Joined
Oct 29, 2018
Messages
21,358
Hi. Just a thought but the way I might approach this is create a query that will return the correct record you might need for the form and simply use DLookup() to get the information.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 14:35
Joined
May 7, 2009
Messages
19,169
Code:
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim AccountCount As Long
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("Select * from tblFees Where [AccountNumber] ='" & Me.cboAccountNumber & "' [COLOR="blue"]Order By FeeChargedEffectiveDate Desc, DateEntered Desc[/COLOR]", dbOpenDynaset)
    rst.MoveLast
    rst.MoveFirst
    AccountCount = rst.RecordCount
    Debug.Print AccountCount

[COLOR="Blue"]     'we have the latest FeeChargedEffectiveDate and DateEntered here
    'DoCmd.OpenForm "xxxx"
    'Forms("xxxx")!TextField=rst!FieldName
[/COLOR]    rst.Close
    Set rst = Nothing
    Set dbs = Nothing
 

JahJr

Andy
Local time
Today, 01:35
Joined
Dec 3, 2008
Messages
93
Thank you for your help. Not sure how I missed such a simple solution.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 14:35
Joined
May 7, 2009
Messages
19,169
in chess, they call it blunder.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 23:35
Joined
Oct 29, 2018
Messages
21,358
Thank you for your help. Not sure how I missed such a simple solution.
Hi. Glad to hear you got it sorted out. Good luck with your project.
 

Users who are viewing this thread

Top Bottom