Hi I'm new to programming and need help.  I have an invoice (report) that 

I want to assign autogenerated alphanumeric values to.  I can not make 

this happen can anyone help? My report is based on a query and I need to 

poulate just this one field with an invoice number

Hi,



You have two things to consider here.



1. What number do you generate

2. Where do you store that number to ensure you do not generate the same 

number twice.



Once you have solved both the above, you can interrupt the printing (to 

screen or Printer) of a report at the On Page Event, and write the 

contents of a variable into a text box control on the Report at run time. 

You will need to store the generated ID number in the variable beforehand.

One thing to remember though. If you generate ANY errors between storing 

the number and placing it on the Report, the variable is wiped. Only 

Constants remain after an error generation. 



HTH



Tony









> Hi I'm new to programming and need help.  I have an invoice (report) 

that 

> I want to assign autogenerated alphanumeric values to.  I can not make 

> this happen can anyone help? My report is based on a query and I need to 

> poulate just this one field with an invoice number

Laney,



This is a response I posted originally on 03/12/2001 and it should work for

you.  (Subject: [access] RE: Consecutive invoice autonumber for new record)





The following code will create the invoice number based on the Julian Date.



First, create a module and cut and paste the following code:



Option Compare Database

Option Explicit



Function CDate2Julian(MyDate As Date) As String

' Taken from Microsoft Access 97 Knowledgebase, Article ID: Q162745



    CDate2Julian = Format(MyDate - DateSerial(Year(MyDate) - 1, 12, 31),

"000")



End Function



Next, on the  form's  cmdNewRxNumb Click event, cut and paste the following

code:





Private Sub cmdNewRxNumb_Click()

' Invoice Number based on Julian Date.

' The Julian Date is the last two digits of the current year

' and the current day number.

' The day number is determined by adding 1 to each day of the year.

' For example, day 1 is January 1, day 2 is January 2,

' day 10 is January 10, day 30 is January 30,

' and day 365 is December 31,

'(unless this is a leap year, then day 366 is December 31)

'

' The format for the Invoice No is yyddd00001



    Dim rstTemp As Recordset

    Dim strSQL As String

    Dim strLastJulian As String

    Dim lngLastIncrement As Long

    Dim strCurrentJulian As String



    ' Goto a new record

    DoCmd.GoToRecord , , acNewRec



    ' Find the last Invoice Number

    strSQL = "SELECT Max([Product Receiving].Invoice_No) AS Last_Invoice_No

" & _

"FROM [Product Receiving]"



    ' Open a recordset based on the strSQL statement

    Set rstTemp = CurrentDb.OpenRecordset(strSQL)



    ' If there is no Last Invoice Number, set the lngLastIncrement to 0

    If IsNull(rstTemp!Last_Invoice_No) Then

        lngLastIncrement = 0

    Else

        ' Find the current Julian Date

  ' (the first 5 characters of the Last Invoice No)

        strLastJulian = Left(rstTemp!Last_Invoice_No, 5)

        ' Find the last increment number

  ' (the last 4 characters of the Last Invoice No)

        lngLastIncrement = Right(rstTemp!Last_Invoice_No, 4)

    End If



    ' Determine today's Julian Date

    strCurrentJulian = Right(DatePart("yyyy", Date), 2) & CDate2Julian(Date)



    ' If today's Julian Date is the Same as the

    ' Last Invoice No's Julian date then add 1 to

    ' the Last Invoice No

    If strCurrentJulian = strLastJulian Then

        txtDRPRxNumb = strCurrentJulian + Format(lngLastIncrement + 1,

"0000")

    Else

        ' Otherwise, this is the first number of today's Julian Date

        txtDRPRxNumb = strCurrentJulian + "0001"

    End If



    ' Close the recordset and release resources

    rstTemp.Close

    Set rstTemp = Nothing



End Sub



This should serve your purpose just fine.  The same basic code can be used

to create a different Invoice format.





John Ruff - The Eternal Optimist :)





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

From:	laney@i... [mailto:laney@i...]

Sent:	Wednesday, July 11, 2001 7:52 PM

To:	Access

Subject:	[access] Help with Invoice number



Hi I'm new to programming and need help.  I have an invoice (report) that

I want to assign autogenerated alphanumeric values to.  I can not make

this happen can anyone help? My report is based on a query and I need to

poulate just this one field with an invoice number