invoice number

C

cjgav

Hi
My problem is I donot know how to generate a number without using autonum
which is unsuitable .
 
C

cjgav

Hi Steve
Thanks for that not sure were to put the Dmax formula so it updates the
invoice number when I print also this number would need to be stored in the
invoice table and not change .
 
C

cjgav

Hi,
That reference was already checked .
I've checked the table name and all the field names and I still receive a
syntax error in this line:
rst.Open "select inrLastUsed from tblInvoiceNumbers where inrType = """ & _
strType & """ and inrYear = " & Year(Date), cnn, adOpenKeyset,
Regards
 
S

Steve

Look again at Fred's response. Just as he says, you don't want to create the
invoice number in the print event. Look again at my response. I suggest you
create the invoice number in the AfterUpdate event of the InvoiceDate field.

Steve
 
C

cjgav

Hi Steve

I think i'm been a bit stupid here

To test this I've setup a table (invoices) with 1 field (Invoicenumber)
I've entered the formula DMax("[InvoiceNumber]","TblInvoice") + 1 in an
event procedure on a form when I run this event I get a syntax error.
What am I doing wrong?

regards
 
C

cjgav

sorry table Invoice

Steve said:
Look again at Fred's response. Just as he says, you don't want to create the
invoice number in the print event. Look again at my response. I suggest you
create the invoice number in the AfterUpdate event of the InvoiceDate field.

Steve
 
N

Noëlla Gabriël

Hi,

don't forget the last argument adLockPessimistic after the ",". In fact the
whole statement "rst.open" till "adLockPessimistic" is one statement. The
"_ " sign makes it possible to continue on the next line. The last argument
probably is put on a next line by the text editor of the newsgroup, but
should continue on the same line.
Tip: while typing the code, in a lot of cases, the VBA editor shows the
possible arguments.
 
S

Steve

Notice in the formula that the table name is TblInvoice. Your table name
"Invoices" does not match. You need to change one or the other so both are
the same.

You also put the formula in the wrong place. Read my previous response
again. Add another field named "InvoiceDate" to your table. Build a form
with two fields based on TblInvoice. Select the InvoiceDate field, open
properties, go to the event tab, go to the AfterUpdate event and create a
subroutine. Put the formula in the subroutine.

Steve


cjgav said:
Hi Steve

I think i'm been a bit stupid here

To test this I've setup a table (invoices) with 1 field (Invoicenumber)
I've entered the formula DMax("[InvoiceNumber]","TblInvoice") + 1 in an
event procedure on a form when I run this event I get a syntax error.
What am I doing wrong?

regards

Steve said:
Look again at Fred's response. Just as he says, you don't want to create
the
invoice number in the print event. Look again at my response. I suggest
you
create the invoice number in the AfterUpdate event of the InvoiceDate
field.

Steve
 
C

cjgav

Hi
I've tried to do this when I type in the formula I get compile error :
expected =, and the + highlighted.
I know I'm making this hard but can you please help

regards


Steve said:
Notice in the formula that the table name is TblInvoice. Your table name
"Invoices" does not match. You need to change one or the other so both are
the same.

You also put the formula in the wrong place. Read my previous response
again. Add another field named "InvoiceDate" to your table. Build a form
with two fields based on TblInvoice. Select the InvoiceDate field, open
properties, go to the event tab, go to the AfterUpdate event and create a
subroutine. Put the formula in the subroutine.

Steve


cjgav said:
Hi Steve

I think i'm been a bit stupid here

To test this I've setup a table (invoices) with 1 field (Invoicenumber)
I've entered the formula DMax("[InvoiceNumber]","TblInvoice") + 1 in an
event procedure on a form when I run this event I get a syntax error.
What am I doing wrong?

regards

Steve said:
Look again at Fred's response. Just as he says, you don't want to create
the
invoice number in the print event. Look again at my response. I suggest
you
create the invoice number in the AfterUpdate event of the InvoiceDate
field.

Steve


Hi Steve
Thanks for that not sure were to put the Dmax formula so it updates the
invoice number when I print also this number would need to be stored in
the
invoice table and not change .

:

Did you read my suggestion above?

Steve



Hi
My problem is I donot know how to generate a number without using
autonum
which is unsuitable .

:

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.
 
C

cjgav

Hi
sorry about this when i try to run this i'm getting compile error invalid
character, _ is highlited

Regards
 
J

John W. Vinson

sorry about this when i try to run this i'm getting compile error invalid
character, _ is highlited

Make sure there's a blank in front of it. The continuation signal is a blank
followed by an underscore followed immediately by a carriagereturn-linefeed.
 
C

cjgav

Hi Steve
I've set this up as far as I can see how you have advised.
When I update the date no number is generated in the table TblInovice if I
open vb the formula DMax("[InvoiceNumber]","TblInvoice") + 1
Is highlighted in red when I run compile I get a syntax error can you help?

regards
Colin

Steve said:
Notice in the formula that the table name is TblInvoice. Your table name
"Invoices" does not match. You need to change one or the other so both are
the same.

You also put the formula in the wrong place. Read my previous response
again. Add another field named "InvoiceDate" to your table. Build a form
with two fields based on TblInvoice. Select the InvoiceDate field, open
properties, go to the event tab, go to the AfterUpdate event and create a
subroutine. Put the formula in the subroutine.

Steve


cjgav said:
Hi Steve

I think i'm been a bit stupid here

To test this I've setup a table (invoices) with 1 field (Invoicenumber)
I've entered the formula DMax("[InvoiceNumber]","TblInvoice") + 1 in an
event procedure on a form when I run this event I get a syntax error.
What am I doing wrong?

regards

Steve said:
Look again at Fred's response. Just as he says, you don't want to create
the
invoice number in the print event. Look again at my response. I suggest
you
create the invoice number in the AfterUpdate event of the InvoiceDate
field.

Steve


Hi Steve
Thanks for that not sure were to put the Dmax formula so it updates the
invoice number when I print also this number would need to be stored in
the
invoice table and not change .

:

Did you read my suggestion above?

Steve



Hi
My problem is I donot know how to generate a number without using
autonum
which is unsuitable .

:

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.
 
J

John W. Vinson

Hi Steve
I've set this up as far as I can see how you have advised.
When I update the date no number is generated in the table TblInovice if I
open vb the formula DMax("[InvoiceNumber]","TblInvoice") + 1
Is highlighted in red when I run compile I get a syntax error can you help?

Please copy and paste (don't retype) the entire VBA procedure to a message
here.
 
C

cjgav

Hi john
This is it:
Private Sub DateCompleted_AfterUpdate()
DMax("[InvoiceNumber]","TblInvoice") + 1

End Sub
Regards
colin

John W. Vinson said:
Hi Steve
I've set this up as far as I can see how you have advised.
When I update the date no number is generated in the table TblInovice if I
open vb the formula DMax("[InvoiceNumber]","TblInvoice") + 1
Is highlighted in red when I run compile I get a syntax error can you help?

Please copy and paste (don't retype) the entire VBA procedure to a message
here.
 
J

John W. Vinson

Hi john
This is it:
Private Sub DateCompleted_AfterUpdate()
DMax("[InvoiceNumber]","TblInvoice") + 1

That's just part of it: try

Private Sub DateCompleted_AfterUpdate()
Me![InvoiceNumber] = DMax("[InvoiceNumber]", "tblInvoice") + 1
End Sub

will calculate the next number *and actually do something with it*.
 
C

cjgav

Hi John
Thanks very much that works fine.

Regards
Colin


John W. Vinson said:
Hi john
This is it:
Private Sub DateCompleted_AfterUpdate()
DMax("[InvoiceNumber]","TblInvoice") + 1

That's just part of it: try

Private Sub DateCompleted_AfterUpdate()
Me![InvoiceNumber] = DMax("[InvoiceNumber]", "tblInvoice") + 1
End Sub

will calculate the next number *and actually do something with it*.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top