Find Last record in table by information in a textbox on a form

[SDaxx]

I am still learning access, vba and sql and am in need of some assistance. I have a table (tblCommercial) with the following these fields: ReferenceNumber(autonumber) AccountNumber, CompanyName, ServiceAddress, DumpsterSize, Frequency. I have a form(frmCommercialChange). The AccountNumber textbox on the form has an AfterUpdate control that populates the CompanyName, ServiceAddress, DumpsterSize and Frequency from the table. The form also has two additional textboxes for New Dumpster Size and New Frequency and when data is entered into these textboxes the information goes into the DumpsterSize and Frequency fields on the table. What I want it to do is to pull the last record with the same account number from the table. Here is the code I have

Private Sub AccountNumber_AfterUpdate()
Dim SQL As String
Dim db As Database
Dim rs As DAO.Recordset
SQL = "SELECT * from tblCommercial WHERE AccountNumber = " & Forms![frmCommercialChangeService]![AccountNumber] & ";"
Set db = CurrentDb
Set rs = db.OpenRecordset(SQL)
If Not IsNull(Me.AccountNumber) Then
    With Me.RecordsetClone
    .FindLast "[AccountNumber]=" & Me.AccountNumber
    End With
Me.txtCompany.Value = rs!NameofCompany
Me.txtServiceAddress.Value = rs!ServiceAddress
Me.txtDumpsterSize.Value = rs!DumpsterSize
Me.txtFrequency.Value = rs!FrequencyofService
End If
End Sub

I hope this make sense and any assistance would be great. Thank you.

[zmbd]

You may want to rethink this... for the most part you shouldn't need to use vba to find related records; however, that's sometimes what it takes.

You should be able to filter or use a subform see if any of the following fit the bill (don't worry if these seem to be a tad heavy reading, if you don't follow something let us know (^.^)).
-filtering-

• Microsoft Access / VBA Insights Sitemap
• How to select Records in an Access Subform from a Combo Box
  
• Cascaded Form Filtering
• Multiple Combobox Filter with Clear Option
• Example Filtering on a Form.

The last entry may be the closest to what you are currently attempting to do.

[ADezii]

You could retrieve these Values using the DLast() Statement, as in:

If IsNull(Me![AccountNumber]) Then Exit Sub
 
With Me
  .txtCompany = Nz(DLast("[NameofCompany]", "tblCommercial", "[AccountNumber] = " & _
                ![AccountNumber]), "N/A")
  .txtServiceAddress = Nz(DLast("[ServiceAddress]", "tblCommercial", "[AccountNumber] = " & _
                       ![AccountNumber]), "N/A")
  .txtDumpsterSize = Nz(DLast("[DumpsterSize]", "tblCommercial", "[AccountNumber] = " & _
                     ![AccountNumber]), "N/A")
  .txtFrequency = Nz(DLast("[FrequencyofService]", "tblCommercial", "[AccountNumber] = " & _
                  ![AccountNumber]), "N/A")
End With

[NeoPa]

I had a reply all set out then I realised you were probably not trying to do what the words in your question led me to believe. Your code references three recordsets and two controls with account numbers. What are you trying to do here?