Searching a recordset starting from the last record to the first

[betmck]

First I will open a form and choose a salesman. Then based on the salesman selected I want to open a recordset and start searching for the last record for the salesman selected. When the record is found some of the information from that record is pulled on the form.

 

[pbaldy]

I would use an SQL statement for the recordset that only pulled the one relevant record. Think TOP predicate and the proper ORDER BY clause.

 

[boblarson]

First I will open a form and choose a salesman. Then based on the salesman selected I want to open a recordset and start searching for the last record for the salesman selected. When the record is found some of the information from that record is pulled on the form.

Just so you know - unless you provide some means for Access to know what order records were added, you can't tell what was the last record added just by opening a recordset. The records, in Access tables, are not stored in any particular order and therefore what you may see one time is not necessarily what you will see the next, unless you provide something to actually give order to the records (like a date/timestamp).

 

[betmck]

Thanks guys:

I am really trying with Access codes. I just want to give you a bit more information about my scenario. For example : I have a table with 5 records ( as shown below). When I open my blank scoresheet form to add new scores, I will select the name Sue. On this form I have a field call Lastscore. Based on the information in the table the lastscore for Sue is 19. How do I search the table for the lastscore and have it appear on the new form?

Id Name Score
1 Sam 12
2 Tim 20
3 Sue 19
4 Sam 10
5 Tim 4

 

[pbaldy]

Try something like:

  Dim strSQL        As String
  Dim db            As DAO.Database
  Dim rs            As DAO.Recordset

  Set db = CurrentDb()

  strSQL = "SELECT TOP 1 Score " _
         & "FROM TableName " _
         & "WHERE Name = '" & Forms!FormName.ControlName _
         & "' ORDER BY ID Desc"
         
  Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

  If Not rs.EOF Then
    Me.ControlName = rs!Score
  End If

  Set rs = Nothing
  Set db = Nothing

 

[betmck]

Hi Pbaldy:

Thanks. I tried your code. I am getting a Run-Time error '3061':Too few parameters. Expected 1.

The error is on this line : Set rs = db.OpenRecordset(strsql, dbOpenDynaset)

Below is the code I am using

reconid in the SQL statement is the primary key for the table 'tblrecon'


Dim strsql As String
Dim db As DAO.Database
Dim rs As DAO.Recordset


Set db = CurrentDb()

strsql = "SELECT TOP 1 Score " _
& "FROM tblrecon " _
& "WHERE salesman = '" & Forms!frmaddhairounrecon.salesman _
& "'ORDER BY reconid"


Set rs = db.OpenRecordset(strsql, dbOpenDynaset)

If Not rs.EOF Then
Me.Ctl1000stock = rs![1000ret]

End If

Set rs = Nothing
Set db = Nothing

 

[pbaldy]

For starters, you missed the space between the single quote and ORDER. Also, you probably want Desc in the order by. If it still errors, make sure all the field names are spelled correctly, the form and control names are correct, and that the form is open. I assume salesman is a text field?

 

[betmck]

Hi Pbaldy:

Thanks again. I do appreciate your quick response.

I had a field spelt incorrectly. So the error is gone. However, It seems as though TOP 1 is giving me the field from the first record in the table for that salesman. What I want is the field from the last record in the table for that salesman.

Also suppose I need 3 fields instead of 1 field from the last record, how can I get them?

Thanks

 

[betmck]

Hi Pbaldy:

I have been able to sort my coding out. I now have the query working just fine. Thanks guys

 

[pbaldy]

Sorry, I was in a meeting. Glad you got it sorted out.