I am still making improvements to my first program written in Access 97.  I 

have been struggling with a few issues.



One is that I would like to take a group of records and use the Max date 

and add 1 to the new record.  We are recording daily operations and I have 

had them enter the day everytime.  I didn't know if I did this in the 

Default property  or where.  I tired using the Max([Report#])+1, but it 

didn't work.  



Also I have this same issue with the Report Date then enter.  I would like 

it to default to the last date +1.  But these records are grouped by 

another field (AFE#).  



Could someone offer some assistance?



Thanks in advance.

Pam

You can use the DMax() function to return the highest date in a given table

or query.  You can add 1 to it arithmetically, or use the DateAdd() function

to increment that value by a day.



So for instance, if the recordset is defined in a query named qryOrders, and

the date field is called OrderDate you'd put something like:



=DateAdd("d",1, DMax("OrderDate", "qryOrders"))



in the default value prop.



HTH,



-Roy



-----Original Message-----

From: Pam McMillin [mailto:pmcmillin@m...]

Sent: Monday, March 12, 2001 1:19 PM

To: Access

Subject: [access] Using Previous record entry +1 for next record





I am still making improvements to my first program written in Access 97.  I 

have been struggling with a few issues.



One is that I would like to take a group of records and use the Max date 

and add 1 to the new record.  We are recording daily operations and I have 

had them enter the day everytime.  I didn't know if I did this in the 

Default property  or where.  I tired using the Max([Report#])+1, but it 

didn't work.  



Also I have this same issue with the Report Date then enter.  I would like 

it to default to the last date +1.  But these records are grouped by 

another field (AFE#).  



Could someone offer some assistance?



Thanks in advance.

Pam

Pam,



The following code should work for you



Private Sub cmdNewRecord_Click()

' The Record Number is in the following format: mmddyyyy00001

' mm = two digit month, dd = two digit day, yyyy= 4 digit year, 00001, 5

digit increment number



    Dim rstTemp As Recordset

    Dim strSQL As String

    Dim strIncrementNo As String



    ' Go to a new record

    DoCmd.GoToRecord , , acNewRec



    ' Create a SQL string and find the last Record Number

    strSQL = "SELECT Max(tbl_Test.Date_Increment) AS Last_Increment_No FROM

tbl_Test"



    ' Create a recordset from the SQL string

    Set rstTemp = CurrentDb.OpenRecordset(strSQL)



    ' If there are No records or the mmddyy of the rstTemp!Last_Increment_No

is different than today's date

    ' then the new Record Number is today's date + 00001

    If IsNull(rstTemp!Last_Increment_No) Or Left(rstTemp!Last_Increment_No,

8) <> Format(Date, "mmddyyyy") Then

        strIncrementNo = Format(Date, "mmddyyyy") & "00001"

    Else

        ' If there are records and the rstTemp!Last_Increment_No is the same

as today's date, then increment

        ' the Record Number by 1

        strIncrementNo = Left(rstTemp!Last_Increment_No, 8) &

Format(Right(rstTemp!Last_Increment_No, 5) + 1, "00000")

    End If



    ' The textbox (Date_Increment) on the form equals the new Record Number

    Date_Increment = strIncrementNo



    rstTemp.Close

    Set rstTemp = Nothing



End Sub



To find the last Report Date create a SQL statement similar to the one on

the code: strSQL="SELECT Max(tbl_Reports.RptDate) AS Last_Rpt_Date FROM

tbl_Reports"



The code for adding days to a date is DateAdd("d",1,Date).  To subtract a

day, the code is DateAdd("d",-1,Date)



I hope this helps



John Ruff - The Eternal Optimist :)

 -----Original Message-----

From: 	Pam McMillin [mailto:pmcmillin@m...]

Sent:	Monday, March 12, 2001 9:20 PM

To:	Access

Subject:	[access] Using Previous record entry +1 for next record



I am still making improvements to my first program written in Access 97.  I

have been struggling with a few issues.



One is that I would like to take a group of records and use the Max date

and add 1 to the new record.  We are recording daily operations and I have

had them enter the day everytime.  I didn't know if I did this in the

Default property  or where.  I tired using the Max([Report#])+1, but it

didn't work.



Also I have this same issue with the Report Date then enter.  I would like

it to default to the last date +1.  But these records are grouped by

another field (AFE#).



Could someone offer some assistance?



Thanks in advance.

Pam