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