Results 1 to 4 of 4
  1. #1
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 7 64bit Access 2003
    Join Date
    Feb 2011
    Posts
    1,919

    A2003 - Me.RecordsetClone.MoveLast not working

    In the segment of code shown below, I'm wondering if the statement Me.RecordsetClone.MoveLast is valid in A2003. There are no compiler errors, yet when I trace the code in Debug it is clear that the current record IS NOT the last record in the RecordSource following the "MoveLast". My intent in this code is to either process with parameters taken from the last reocrd in the RecordSource OR capture a couple of pieces of data from the last record, create a new record and process based on the newly created data.

    The code is dependent on the use of Me.RecordsetClone.MoveLast and Me.RecordsetClone.MovePrevious. Obviously, if Me.RecordsetClone.MoveLast doesn't work, then nothing else will be correct either. I can use other approaches, but if Me.RecordsetClone.MoveLast is supposed to work in A2003 I need to somehow resolve that question.

    The form's property sheet specifies RecordSource as a simple query with the sort order ascending on what is essentially an autonumber.



    Code:
    Option Compare Database
    Option Explicit
    
    Dim intCodeCount As Integer
    Dim PCDate As Date
    Dim PCPrevRptNo As Integer
    Dim BaseDate As Date
    Dim intNewRptNo As Integer
    
    Private Sub Form_Open(Cancel As Integer)
    '*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
    ' This form is simply a preliminary view of the Per Capita report wherein the opportunity
    ' is provided to add information that IS NOT inherently found in the current database.
    ' Information that is entered here is retained in the Per Capita table (tblpercap) on a
    ' serialization (Report Number) and date basis.  Once the user issues the "Submit" command,
    ' the Per Capita table entry for the current report is marked (PCSub) True.
    '*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
    
    Me.RecordsetClone.MoveLast                    'Move to last registered report
    
    'Has it already been submitted?
        If Me.PerCapSub = True Then
            'Yes. Register a new report
            BaseDate = Me.PerCapReportDate        ' Date of last submitted CP report is new base
            intNewRptNo = Me.PerCapRptNo + 1      ' Increment report number (We'll need to test for 1st "THIS YEAR")
            CurrentDb.Execute "INSERT INTO tblPerCap (PerCapRptNo, PerCapReportDate) VALUES(" & intNewRptNo & ",#" & Date & "#)"
            ' Okay, new record is now the current record
        Else
            'No. We're resuming a suspended report.  Get the base date from previous PC report.
            Me.RecordsetClone.MovePrevious        ' Back up one record
            BaseDate = Me.PerCapReportDate        ' We're reporting based on that date
            Me.RecordsetClone.MoveLast            ' Okay, now back to whence we came....... the last record in the set.
            Me.tbReNewAdj = Me.AdjRenew
            Me.tbNewAdj = Me.AdjNew
        End If
        
    'Set the text boxes with appropriate report number and report date.
    Me.tbRptNo = intNewRptNo
    Me.tbDOR = Me.PerCapReportDate
    
    Call PopulateControls("1/1/" & Year(Date), "YTD")
    Call PopulateControls(BaseDate, "")
    Call UpTheGrands
    
    End Sub

  2. #2
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    For starters, the open event is too soon to work with data. Try the load event. I'm more likely to work with data directly from the table, rather than rely on moving around a form's records.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    What you should be doing is moving last on a record set based on the clone, not the clone itself:
    Set rs = Me.RecordsetClone
    rs.MoveLast
    However, a clone is only in memory, and without using the bookmark property to keep the clone and the form in sync, you run the risk of acting on records that are not being displayed or at least being the active record.
    'Okay, new record is now the current record
    I'm missing the point as to why you have to work with the clone instead of the form's recordset.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 7 64bit Access 2003
    Join Date
    Feb 2011
    Posts
    1,919
    The valid use of the statement "Me.RecordsetClone.MoveLast" in Access A2003 notwithstanding, the whole approach in making a hasty design decision in retrofitting a somewhat major functional addition to an existing app is/was inadequate at best. The modifications required are far more global to the app than first thought. Sorry to have wasted your time, Bill

Please reply to this thread with any new information or opinions.

Similar Threads

  1. A2003 mde with conditional formatting
    By GraeagleBill in forum Forms
    Replies: 4
    Last Post: 03-13-2016, 11:46 PM
  2. Recordsetclone NOT picking up records from linked table
    By CementCarver in forum Programming
    Replies: 1
    Last Post: 10-15-2013, 10:01 AM
  3. Movelast not working as expected
    By GraeagleBill in forum Programming
    Replies: 28
    Last Post: 08-04-2013, 01:37 PM
  4. Null values being returned when use movelast
    By ssalem in forum Programming
    Replies: 6
    Last Post: 03-15-2013, 10:25 AM
  5. doubt with MoveLast
    By fabiobarreto10 in forum Forms
    Replies: 8
    Last Post: 04-11-2012, 12:41 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums