|
 |
access thread: Consecutive invoice autonumber for new record
Message #1 by lyle.mulcaster@t... on Mon, 12 Mar 2001 21:14:18
|
|
Hi,
Wondering if anyone can help me with a VB programming problem for an
Access Application I'm working on. or point me to in the right direction
to find the answer.
Or if you can come up with another way of accomplishing the task.
Need to code a command button on a form called "Second Product Receiving
Query" to generate a unique consecutive invoice number for textbox
labelled "DRP Internal Rx#".
This number can be picked from a list, lookup table that shows only one
number in the combobox, array or created by a number generator. It can
even initiate a macro procedure to stuff the number in the textbox.
As long as the next number in the sequence is available to use when the
button is pushed the next time. So the pointer must advance to the next
available number that is in the list or add 1 to the number that was used
the last time the button was used.
After this consecutive number is inserted in the form the rest of the
fields are filled in either by manual entry or from lookup tables to fill
in company info and products returned e.g. quantity return on work order,
account number, address, city, street, etc.
A second command button is used to copy or duplicate the current record if
the work order has multiple items on the return order. The only problem
with this button was it locked up the table or didn't close or save
properly on occasion. Something funny there!
Database name is "DRP Returns Tracking"
Table is "Product Receiving"
Query "Second DRP Returns Query"
Form "frmSecondDRPReturnsQuery"
Textbox is "txtDRPRxNumb.text
Command Button is "cmdNewRxNumb."
Can't use the Autonumber property for a field because Access uses it for
the Primary Key to provide the unique index. (No duplicates}
Anyway, if you can come up with any sugguestions for coding or
accomplishing the task I'd appreciate it.
Message #2 by "John Ruff" <papparuff@c...> on Mon, 12 Mar 2001 16:36:04 -0800
|
|
Kyle,
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.
I hope this helps.
John Ruff - The Eternal Optimist :)
-----Original Message-----
From: lyle.mulcaster@t...
[mailto:lyle.mulcaster@t...]
Sent: Monday, March 12, 2001 9:14 PM
To: Access
Subject: [access] Consecutive invoice autonumber for new record
Hi,
Wondering if anyone can help me with a VB programming problem for an
Access Application I'm working on. or point me to in the right direction
to find the answer.
Or if you can come up with another way of accomplishing the task.
Need to code a command button on a form called "Second Product Receiving
Query" to generate a unique consecutive invoice number for textbox
labelled "DRP Internal Rx#".
This number can be picked from a list, lookup table that shows only one
number in the combobox, array or created by a number generator. It can
even initiate a macro procedure to stuff the number in the textbox.
As long as the next number in the sequence is available to use when the
button is pushed the next time. So the pointer must advance to the next
available number that is in the list or add 1 to the number that was used
the last time the button was used.
After this consecutive number is inserted in the form the rest of the
fields are filled in either by manual entry or from lookup tables to fill
in company info and products returned e.g. quantity return on work order,
account number, address, city, street, etc.
A second command button is used to copy or duplicate the current record if
the work order has multiple items on the return order. The only problem
with this button was it locked up the table or didn't close or save
properly on occasion. Something funny there!
Database name is "DRP Returns Tracking"
Table is "Product Receiving"
Query "Second DRP Returns Query"
Form "frmSecondDRPReturnsQuery"
Textbox is "txtDRPRxNumb.text
Command Button is "cmdNewRxNumb."
Can't use the Autonumber property for a field because Access uses it for
the Primary Key to provide the unique index. (No duplicates}
Anyway, if you can come up with any sugguestions for coding or
accomplishing the task I'd appreciate it.
Message #3 by "Lyle Mulcaster" <lyle.mulcaster@t...> on Mon, 12 Mar 2001 21:09:45 -0500
|
|
Thanks John,
Appreciate your Optimism tonight
Regards,
( Kyle )
Lyle Mulcaster
----- Original Message -----
From: "John Ruff" <papparuff@c...>
To: "Access" <access@p...>
Sent: Monday, March 12, 2001 7:36 PM
Subject: [access] RE: Consecutive invoice autonumber for new record
> Kyle,
>
> 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.
>
> I hope this helps.
>
>
> John Ruff - The Eternal Optimist :)
> -----Original Message-----
> From: lyle.mulcaster@t...
> [mailto:lyle.mulcaster@t...]
> Sent: Monday, March 12, 2001 9:14 PM
> To: Access
> Subject: [access] Consecutive invoice autonumber for new record
>
> Hi,
> Wondering if anyone can help me with a VB programming problem for an
> Access Application I'm working on. or point me to in the right direction
> to find the answer.
> Or if you can come up with another way of accomplishing the task.
> Need to code a command button on a form called "Second Product Receiving
> Query" to generate a unique consecutive invoice number for textbox
> labelled "DRP Internal Rx#".
> This number can be picked from a list, lookup table that shows only one
> number in the combobox, array or created by a number generator. It can
> even initiate a macro procedure to stuff the number in the textbox.
> As long as the next number in the sequence is available to use when the
> button is pushed the next time. So the pointer must advance to the next
> available number that is in the list or add 1 to the number that was used
> the last time the button was used.
> After this consecutive number is inserted in the form the rest of the
> fields are filled in either by manual entry or from lookup tables to fill
> in company info and products returned e.g. quantity return on work order,
> account number, address, city, street, etc.
> A second command button is used to copy or duplicate the current record if
> the work order has multiple items on the return order. The only problem
> with this button was it locked up the table or didn't close or save
> properly on occasion. Something funny there!
>
> Database name is "DRP Returns Tracking"
> Table is "Product Receiving"
> Query "Second DRP Returns Query"
> Form "frmSecondDRPReturnsQuery"
> Textbox is "txtDRPRxNumb.text
> Command Button is "cmdNewRxNumb."
>
> Can't use the Autonumber property for a field because Access uses it for
> the Primary Key to provide the unique index. (No duplicates}
>
> Anyway, if you can come up with any sugguestions for coding or
> accomplishing the task I'd appreciate it.
>
>
Message #4 by Brian Skelton <brian_skelton@o...> on Tue, 13 Mar 2001 09:25:19 GMT
|
|
Lyle
You should be able to use the auto number field you've got
set up for your invoice number.
I think the problem is in your table design. You need to
split your table into at least two seperate ones, linked by
the autogenerated invoice number. The main table would
contain all the data that remains constant when you're adding
multiple items to a work order. The second table would
contain the 'multiple items'. You would then create a 'one
to many' relationship between the two tables to ensure data
integrity.
You could then display your main data in one form, and your
work orders details in a linked sub-form.
If you can get hold of a book on relational database design,
look up 'Normalisation'. This will give you the theory, and
you should be able to apply it to your tables.
BDS
Message #5 by "Lyle Mulcaster" <lyle.mulcaster@t...> on Wed, 14 Mar 2001 07:46:03 -0500
|
|
Thanks Brian,
The problem that I was having with the invoice autonumbers was that a user
would
delete a record and throw the consecutive number order out of sequence.
The consecutive number invoicing is crucial to the business when dealing
with international export/import issues. Inspectors don't like holes in the
order.
Another problem was that on different session startups the capability to
determine the highest invoice number last used has to be present to
automatically stuff the invoice_num textbox with a new number to start on a
new record for user data entry.
MAX is probably the way to go on that issue.
LyleM
----- Original Message -----
From: "Brian Skelton" <brian_skelton@o...>
To: "Access" <access@p...>
Sent: Tuesday, March 13, 2001 4:25 AM
Subject: [access] RE: Consecutive invoice autonumber for new record
> Lyle
>
> You should be able to use the auto number field you've got
> set up for your invoice number.
>
> I think the problem is in your table design. You need to
> split your table into at least two seperate ones, linked by
> the autogenerated invoice number. The main table would
> contain all the data that remains constant when you're adding
> multiple items to a work order. The second table would
> contain the 'multiple items'. You would then create a 'one
> to many' relationship between the two tables to ensure data
> integrity.
>
> You could then display your main data in one form, and your
> work orders details in a linked sub-form.
>
> If you can get hold of a book on relational database design,
> look up 'Normalisation'. This will give you the theory, and
> you should be able to apply it to your tables.
>
> BDS
>
>
>
|
|
|
View Cart
