Results 1 to 12 of 12
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Portland, Maine, USA
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Open form to the record that was viewed last (2002/SR-1)

    I created an application tips form that opens at startup. The user can click next to show the next tip. Say the user has viewed tips 1-4. How can I display the next tip (#5), the next time the user starts up the database?
    Thanks,
    Don

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open form to the record that was viewed last (2002/SR-1)

    Create a table with one numeric field, Long (in the code sample tblLastViewed, field Position)
    In the code window, select the menu Tools | References and check Microsoft DAO 3.6 Object Library.
    Put the following code in the On Unload of the form.
    <pre>Private Sub Form_Unload(Cancel As Integer)
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Set db = CurrentDb
    Set rst = db.OpenRecordset("tblLastViewed", dbOpenDynaset)
    If rst.RecordCount = 0 Then
    rst.AddNew
    Else
    rst.MoveFirst
    rst.Edit
    End If
    rst!Position = Me.Recordset.AbsolutePosition
    rst.Update
    Set rst = Nothing
    Set db = Nothing
    End Sub</pre>

    Put the following code in the On Open event of the form:
    <pre>Private Sub Form_Open(Cancel As Integer)
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim rstForm As DAO.Recordset
    Set db = CurrentDb
    Set rst = db.OpenRecordset("tblLastViewed", dbOpenSnapshot)
    If rst.RecordCount = 0 Then
    Exit Sub
    End If
    rst.MoveFirst
    If rst!Position <= Me.Recordset.RecordCount Then
    DoCmd.GoToRecord acDataForm, Me.Name, , rst!Position + 1
    End If
    Set rst = Nothing
    Set db = Nothing
    End Sub</pre>

    This will work if there are no records deleted (before the current record) between the two use of the forms and get the last viewed. I prefere the last viewed instead of the following in case the use close the form on the last record.
    I have tried to work with the find method, but this seems not to work in the on open event.
    Francois

  3. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Portland, Maine, USA
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open form to the record that was viewed last (2002/SR-1)

    Can I use ADO or should I add the reference to DAO. I have been trying to just use ADO but if there wouldn't be a problem having a reference to both then I'll gladly to it. How would the ADO code look?
    Thanks for your help Francois!
    Don

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open form to the record that was viewed last (2002/SR-1)

    I don't know very much about ADO, but I'll give it a try and if I found it I'll post it.
    Francois

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open form to the record that was viewed last (2002/SR-1)

    This should be working. Let me know, it's my first real ADO code
    <pre>Private Sub Form_Open(Cancel As Integer)
    Dim rst As New ADODB.Recordset
    rst.Open "tblLastViewed", CurrentProject.Connection
    If rst.RecordCount = 0 Then
    Exit Sub
    End If
    DoCmd.GoToRecord acDataForm, Me.Name, , rst!Position + 1
    rst.Close
    End Sub

    Private Sub Form_Unload(Cancel As Integer)
    Dim rst As New ADODB.Recordset
    rst.Open "tblLastViewed", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
    If rst.RecordCount = 0 Then
    rst.AddNew
    Else
    rst.MoveFirst
    End If
    rst!Position = Me.Recordset.AbsolutePosition
    rst.Update
    rst.Close
    End Sub</pre>

    Francois

  6. #6
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Portland, Maine, USA
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open form to the record that was viewed last (2002/SR-1)

    Works great Francois. The only thing I had to change was rst!Position + 1. I changed it to +2 in order for it to take me to the next record. Now I'm working on fixing it so that when I'm at the last tip it will start over at the beginning.
    Thanks Francois,
    Don

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Open form to the record that was viewed last (2002/SR-1)

    Why not just execute an update on the table instead of using a recordset to update it? You can do it using an ADO command object like this:

    <pre>Public Function ExecuteAction(strSQL As String)
    'Created by Charlotte Foust 3/25/2002
    Dim ADOCmd As ADODB.Command
    Dim lngRecsAffected As Long

    Set ADOCmd = New ADODB.Command
    With ADOCmd
    .ActiveConnection = CurrentProject.Connection
    .CommandText = strSQL
    .CommandType = adCmdText
    End With
    ADOCmd.Execute lngRecsAffected

    Set ADOCmd = Nothing
    End Function</pre>

    Charlotte

  8. #8
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Portland, Maine, USA
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open form to the record that was viewed last (2002/SR-1)

    I may be able to follow this suggestion after a "Very" good nights rest! <img src=/S/newbrain.gif border=0 alt=newbrain width=21 height=22>
    I'll see what I can do (or not do) with it in the morning.
    Thanks Charlotte!
    Don

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

    Re: Open form to the record that was viewed last (2002/SR-1)

    Hi Francois

    Greate code

    I have a similar need.

    I want to open the recordset to the last record changed or created.

    I can see how your code could be used to trigger my requirement.

    However, can I accomplish my request with a query?

    My record contains both an Update date and Create date fields.

    How would a recordset query be created that would load the last record changed/created?

    Thanks, John

  10. #10
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open form to the record that was viewed last (2002/SR-1)

    Hi John,

    I suppose you have already the code to update the Update date and Create Date fields.
    You only need the code for the On Open Event of the form.
    The code get the greatest Update date and the Greatest Create date, then compare the two and find the record with the greatest of the two.
    <pre>Private Sub Form_Open(Cancel As Integer)
    Me.tblContact_subform.Form.OrderBy = "txtcontactnaam"
    Me.tblContact_subform.Form.OrderByOn = True
    DoCmd.Maximize
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim dtmUpdated As Date
    Dim dtmCreated As Date
    Dim strSQL As String
    Dim strCriteria As String
    Set db = CurrentDb
    strSQL = "SELECT MAX([UpdateDate]) as Updated FROM " & Me.RecordSource
    Set rst = db.OpenRecordset(strSQL)
    If rst.RecordCount <> 0 Then
    rst.MoveFirst
    If Not IsNull(rst!Updated) Then
    dtmUpdated = rst!Updated
    End If
    End If
    strSQL = "SELECT MAX([CreateDate]) as Created FROM " & Me.RecordSource
    Set rst = db.OpenRecordset(strSQL)
    If rst.RecordCount <> 0 Then
    rst.MoveFirst
    If Not IsNull(rst!created) Then
    dtmCreated = rst!created
    End If
    End If
    If dtmUpdated >= dtmCreated Then
    strCriteria = "[UpdateDate] = #" & dtmUpdated & "#"
    Else
    strCriteria = "[CreateDate] = #" & dtmCreated & "#"
    End If
    Set rst = Me.RecordsetClone
    rst.FindFirst strCriteria
    Me.Bookmark = rst.Bookmark
    Set rst = Nothing
    Set db = Nothing
    End Sub</pre>

    Francois

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

    Re: Open form to the record that was viewed last (2002/SR-1)

    Hi Francois

    Thanks for the code; this is a learning experience for me.

    Some observations:

    On your original post 127479

    1. Will this work if your dealing with empty record sets that Charlotte talks about?

    2. Why wouldn

  12. #12
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open form to the record that was viewed last (2002/SR-1)

    John,

    1. I think what's Charlotte is speaking about is to store a value with an Update Sql statement when you use ADO. Like you see, I still have a lot to learn about ADO. This was my first attempt to use it.

    2. To be honest, I use the AbsolutePosition in my first code, because I didn't find a way to get the find function to work at that moment and I use the docmd.gotorecord to get result and I warned Don about deleted records . By rewriting the code for you, I found the right way to do it. Some times it's good to have a second look at your own code. If Don is following the thread, he can adapt his code.
    Francois

Posting Permissions

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