A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

invoice number



 
 
Thread Tools Display Modes
  #11  
Old June 8th, 2009, 06:14 PM posted to microsoft.public.access.tablesdbdesign
bhicks11 via AccessMonster.com
external usenet poster
 
Posts: 529
Default invoice number

Noella explained how to do it. You make another table with 1 field. In the
field you put the first invoice number. When you print the invoice, set the
invoice number to the value of that table.field. Then you run an update
query to add one to the value in the field so that the next invoice will have
the next number.

Bonnie
http://www.dataplus-svc.com

cjgav wrote:
hi
I want to keep the same number .
I enter the info in to the database to create a estimate for customer when
the estimate is excepted and the job is completed I want a table with invoice
date in which I have used an append query to update but I cannot create an
invoice number without using autonum which will not produce the numbers I
want .

And what happens when you print the same invoice again later? Do you want a
different invoice number to be created?

[quoted text clipped - 8 lines]
when
the invoice is printed can anyone explain how to do this please


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/200906/1

  #12  
Old June 8th, 2009, 07:20 PM posted to microsoft.public.access.tablesdbdesign
Noëlla Gabriël
external usenet poster
 
Posts: 79
Default invoice number

Hi,

The invoice numbers table can be designed as (example for INV = invoice and
CN = credit note):

tblInvoiceNumbers
-----------------------------
inrID inrType inrYear inrLastUsed
------ ---------- ---------- ------------------
1 INV 2008 256
2 CN 2008 36
3 INV 2009 106
4 CN 2009 12


The following function gets the invoice nr. and augments the field with 1
(please add your proper error handling)

Public Function GetNextInvoiceNr(strType As String) As Long

Dim rst As New ADODB.Recordset
Dim cnn As ADODB.Connection
Dim lngNr As Long

Set cnn = CurrentProject.Connection
rst.Open "select inrLastUsed from tblInvoiceNumbers where inrType = """ & _
strType & """ and inrYear = " & Year(Date), cnn, adOpenKeyset,
adLockPessimistic
With rst
If Not (.BOF And .EOF) Then
.MoveFirst
lngNr = !inrLastUsed
!inrLastUsed = !inrLastUsed + 1
.Update
End If
.Close
End With

GetNextInvoiceNr = lngNr + 1


End Function


--
Kind regards
Noëlla


"cjgav" wrote:

Hi
Thank you for your help . I'm not sure about this if I 'm not using autonum
to generate the numbers how do I do it?


  #14  
Old June 9th, 2009, 12:18 AM posted to microsoft.public.access.tablesdbdesign
cjgav
external usenet poster
 
Posts: 54
Default invoice number


Hi
Thank you for your help this looks very much like it.
I do not understand vb very well at all do i need to edit function as it
stands there seems to be a compile error on line rst.open


"Noëlla Gabriël" wrote:

Hi,

The invoice numbers table can be designed as (example for INV = invoice and
CN = credit note):

tblInvoiceNumbers
-----------------------------
inrID inrType inrYear inrLastUsed
------ ---------- ---------- ------------------
1 INV 2008 256
2 CN 2008 36
3 INV 2009 106
4 CN 2009 12


The following function gets the invoice nr. and augments the field with 1
(please add your proper error handling)

Public Function GetNextInvoiceNr(strType As String) As Long

Dim rst As New ADODB.Recordset
Dim cnn As ADODB.Connection
Dim lngNr As Long

Set cnn = CurrentProject.Connection
rst.Open "select inrLastUsed from tblInvoiceNumbers where inrType = """ & _
strType & """ and inrYear = " & Year(Date), cnn, adOpenKeyset,
adLockPessimistic
With rst
If Not (.BOF And .EOF) Then
.MoveFirst
lngNr = !inrLastUsed
!inrLastUsed = !inrLastUsed + 1
.Update
End If
.Close
End With

GetNextInvoiceNr = lngNr + 1


End Function


--
Kind regards
Noëlla


"cjgav" wrote:

Hi
Thank you for your help . I'm not sure about this if I 'm not using autonum
to generate the numbers how do I do it?


  #15  
Old June 9th, 2009, 12:46 PM posted to microsoft.public.access.tablesdbdesign
Noëlla Gabriël
external usenet poster
 
Posts: 79
Default invoice number

Hi,

You need to have a reference to the ADODB library in your project. Open a
module (any module) , click tools - references and check the ADODB reference
lib. I don't have my own computer now, so can't have a quick look now, but
you'll find it :-)
--
Kind regards
Noëlla


"cjgav" wrote:


Hi
Thank you for your help this looks very much like it.
I do not understand vb very well at all do i need to edit function as it
stands there seems to be a compile error on line rst.open


"Noëlla Gabriël" wrote:

Hi,

The invoice numbers table can be designed as (example for INV = invoice and
CN = credit note):

tblInvoiceNumbers
-----------------------------
inrID inrType inrYear inrLastUsed
------ ---------- ---------- ------------------
1 INV 2008 256
2 CN 2008 36
3 INV 2009 106
4 CN 2009 12


The following function gets the invoice nr. and augments the field with 1
(please add your proper error handling)

Public Function GetNextInvoiceNr(strType As String) As Long

Dim rst As New ADODB.Recordset
Dim cnn As ADODB.Connection
Dim lngNr As Long

Set cnn = CurrentProject.Connection
rst.Open "select inrLastUsed from tblInvoiceNumbers where inrType = """ & _
strType & """ and inrYear = " & Year(Date), cnn, adOpenKeyset,
adLockPessimistic
With rst
If Not (.BOF And .EOF) Then
.MoveFirst
lngNr = !inrLastUsed
!inrLastUsed = !inrLastUsed + 1
.Update
End If
.Close
End With

GetNextInvoiceNr = lngNr + 1


End Function


--
Kind regards
Noëlla


"cjgav" wrote:

Hi
Thank you for your help . I'm not sure about this if I 'm not using autonum
to generate the numbers how do I do it?


  #16  
Old June 9th, 2009, 02:08 PM posted to microsoft.public.access.tablesdbdesign
Fred
external usenet poster
 
Posts: 1,451
Default invoice number

In my humble opinion, invoicing is a (one time) event (which typically
occurs the first time that it is printed) = a data entity which should be
handled / stored as a data entity. IMHO you are running into a lot of
challenges which are arising because your structure is not treating it as
such.

  #17  
Old June 9th, 2009, 03:37 PM posted to microsoft.public.access.tablesdbdesign
cjgav
external usenet poster
 
Posts: 54
Default invoice number

Hi Fred
That’s exactly what I am trying to do but I cannot generate an invoice number


"Fred" wrote:

In my humble opinion, invoicing is a (one time) event (which typically
occurs the first time that it is printed) = a data entity which should be
handled / stored as a data entity. IMHO you are running into a lot of
challenges which are arising because your structure is not treating it as
such.

  #18  
Old June 9th, 2009, 03:39 PM posted to microsoft.public.access.tablesdbdesign
cjgav
external usenet poster
 
Posts: 54
Default invoice number

Hi
I've tried to do this but there is no ADODB LIBARY listed !

"Noëlla Gabriël" wrote:

Hi,

You need to have a reference to the ADODB library in your project. Open a
module (any module) , click tools - references and check the ADODB reference
lib. I don't have my own computer now, so can't have a quick look now, but
you'll find it :-)
--
Kind regards
Noëlla


"cjgav" wrote:


Hi
Thank you for your help this looks very much like it.
I do not understand vb very well at all do i need to edit function as it
stands there seems to be a compile error on line rst.open


"Noëlla Gabriël" wrote:

Hi,

The invoice numbers table can be designed as (example for INV = invoice and
CN = credit note):

tblInvoiceNumbers
-----------------------------
inrID inrType inrYear inrLastUsed
------ ---------- ---------- ------------------
1 INV 2008 256
2 CN 2008 36
3 INV 2009 106
4 CN 2009 12


The following function gets the invoice nr. and augments the field with 1
(please add your proper error handling)

Public Function GetNextInvoiceNr(strType As String) As Long

Dim rst As New ADODB.Recordset
Dim cnn As ADODB.Connection
Dim lngNr As Long

Set cnn = CurrentProject.Connection
rst.Open "select inrLastUsed from tblInvoiceNumbers where inrType = """ & _
strType & """ and inrYear = " & Year(Date), cnn, adOpenKeyset,
adLockPessimistic
With rst
If Not (.BOF And .EOF) Then
.MoveFirst
lngNr = !inrLastUsed
!inrLastUsed = !inrLastUsed + 1
.Update
End If
.Close
End With

GetNextInvoiceNr = lngNr + 1


End Function


--
Kind regards
Noëlla


"cjgav" wrote:

Hi
Thank you for your help . I'm not sure about this if I 'm not using autonum
to generate the numbers how do I do it?


  #19  
Old June 9th, 2009, 04:45 PM posted to microsoft.public.access.tablesdbdesign
Noëlla Gabriël
external usenet poster
 
Posts: 79
Default invoice number

Hi,

the reference is called:
Microsoft ActiveX Data Objects x.x library

if that's installed and you still get errors, check the tablenames and
fieldnames in the expression. They should match the existing table in your
database.
--
Kind regards
Noëlla


"cjgav" wrote:

Hi
I've tried to do this but there is no ADODB LIBARY listed !

"Noëlla Gabriël" wrote:

Hi,

You need to have a reference to the ADODB library in your project. Open a
module (any module) , click tools - references and check the ADODB reference
lib. I don't have my own computer now, so can't have a quick look now, but
you'll find it :-)
--
Kind regards
Noëlla


"cjgav" wrote:


Hi
Thank you for your help this looks very much like it.
I do not understand vb very well at all do i need to edit function as it
stands there seems to be a compile error on line rst.open


  #20  
Old June 10th, 2009, 03:05 PM posted to microsoft.public.access.tablesdbdesign
Fred
external usenet poster
 
Posts: 1,451
Default invoice number

Under my described thought process, you would create the record in the
invoice table and create an invoice number at the time that the invoice event
occurred. The first printing would happen a few seconds AFTER that process.
And later you could print extra copies of that invoice if needed.
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:18 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.