Results 1 to 3 of 3
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How do I Display Last Record Used? (A2k (9.0.3821) SR-1)

    I have a form that displays all records in the record set.

    Each record has an lngID auto number.

    When the form closes I save the lngID of the displayed record (last record used) on the form in a tblLastUsed table.

    When I open the form for the next session, I want to open the form with the full record set and display the last record used that was displayed from previous session.

    When I open the form in the Form_Open event I have the following code:

    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim lngID As Long
    Set db = CurrentDb

    Set rst = db.OpenRecordset("tblLastUsed", dbOpenSnapshot)
    If rst.RecordCount = 0 Then
    Exit Sub
    End If
    rst.MoveFirst
    lngID = rst!LastUsedRecordSSR

    I need the code to position the record set to the auto number in lngID

    Thanks, John

  2. #2
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: How do I Display Last Record Used? (A2k (9.0.3821) SR-1)

    Here's how I would do this. For form's Unload event, use code like this:
    <pre> Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim strSQL As String
    Dim lngRecordID As Long

    lngRecordID = Me.RecordID

    Set db = CurrentDb
    strSQL = "SELECT RecordID FROM tblLastUsed WHERE FormName ='" & Me.Name & "';"
    Set rst = db.OpenRecordset(strSQL)

    With rst
    .Edit
    !RecordID = lngRecordID
    .Update
    .Close
    End With</pre>

    For form's Open event use code like this:
    <pre> Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim strSQL As String
    Dim lngRecordID As Long

    Set db = CurrentDb
    strSQL = "SELECT RecordID FROM tblLastUsed WHERE FormName ='" & Me.Name & "';"
    Set rst = db.OpenRecordset(strSQL)

    If rst.RecordCount > 0 Then
    lngRecordID = rst!RecordID
    rst.Close
    Set rst = Me.RecordsetClone
    With rst
    .FindFirst "RecordID = " & lngRecordID
    Me.Bookmark = .Bookmark
    End With
    Else 'do nothing
    rst.Close
    End If</pre>

    Note the code is similar for both. The table I used to store record number has 2 fields, one for name of form (FormName - text field) and one for RecordID (Long Integer field). This allows you to use this technique for any number of forms in database (assuming RecordID is always a number). You can modify this to work with your actual field names (lngID or whatever).

    HTH

  3. #3
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How do I Display Last Record Used? (A2k (9.0.3821) SR-1)

    Hi Mark

    Your code worked great for my full record set situation!

    I mostly deal with empty record sets (thanks to Charlotte) and use the following code to return to the last record used, this may be helpful to others, if you have any improvements on this code let me know.

    My tblLastUsed table has one record and each field represents a record type that holds the lngID, ex LastUsedRecordDR for Dentist record, LastUsedRecordVR for Vendor record, etc.

    Thanks for your help

    John

    =======================================
    Private Sub Form_Unload(Cancel As Integer)

    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Set db = CurrentDb
    Set rst = db.OpenRecordset("tblLastUsed", dbOpenDynaset)
    If rst.RecordCount = 0 Then
    rst.AddNew
    Else
    rst.MoveFirst
    rst.Edit
    End If
    rst!LastUsedRecordDR = Me!autDentistKey
    rst.Update
    Set rst = Nothing
    Set db = Nothing

    End Sub

    ======================================

    Private Sub Form_Open(Cancel As Integer)

    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim rstForm As DAO.Recordset
    Dim strSQL As String
    Dim lngID As Long
    Set db = CurrentDb

    Set rst = db.OpenRecordset("tblLastUsed", dbOpenSnapshot)
    If rst.RecordCount = 0 Then
    Exit Sub
    End If
    rst.MoveFirst
    lngID = rst!LastUsedRecordDR
    strSQL = "SELECT * FROM tblDentists WHERE autDentistKey = " & lngID
    Me.RecordSource = strSQL
    If Me.Recordset.RecordCount <> 1 Then ' must be zero
    strSQL = "SELECT MAX([autDentistKey]) as ID FROM tblDentists"
    Set rst = db.OpenRecordset(strSQL)
    If rst.RecordCount <> 0 Then
    rst.MoveFirst
    If Not IsNull(rst!ID) Then
    lngID = rst!ID
    End If
    End If
    strSQL = "SELECT * FROM tblDentists WHERE autDentistKey = " & lngID
    Me.RecordSource = strSQL
    End If
    Set rst = Nothing
    Set db = Nothing

    End Sub

    =====================================

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •