Autonumber invoices when invoiced

[jwr]

I have an OrderEntry database, access 2003, Windows XP.

Once an order is entered, I have the option to preview the invoice and print
the invoice. How do I create a field that will insert an invoice number
which selects the next number automatically - only when the ship date is
entered indicating that the order is filled?

I currently have an invoice that is using Order ID as the invoice number --
like the Northwind sample does.

Thank you

 

[Marshall Barton]

I have an OrderEntry database, access 2003, Windows XP.

Once an order is entered, I have the option to preview the invoice and print
the invoice. How do I create a field that will insert an invoice number
which selects the next number automatically - only when the ship date is
entered indicating that the order is filled?

I am not sure I followed the sequence, but maybe this air
code will get you started.

Code in the ShipDate text box's AfterUpdate event procedure:

If Not IsNull(Me.txtShipDate) Then
If IsNull(Me.txtInvoiceNum) Then
Me.txtInvoiceNum = Nz(DMax("InvoiceNum", "Orders"),0)+1
End If
End If

 

[jwr]

Thank you for your reply. Below is the code that I entered on my Order by
Customer Subform. I do not get any errors, but I also do not get an invoice
number in the invoice field either. What did I do wrong?

Private Sub ShipDate_AfterUpdate()
If NotIsNull(Me.txtShipDate) Then
If IsNull(Me.txtInvoiceNum) Then
Me.txtInvoiceNum = Nz(DMax("InvoiceNum", "Orders by Customer subform"), 0) +
1
End If
End If

End Sub

You have followed my insanity. My InvoiceNum field on the table is a number
field. I am needing the system to automatically input the invoice number
once the order has a ship date.

Again, thank you and look forward to your next reply.

 

[Marshall Barton]

I see that you did not change the names I used for your text
boxes to the names you are using. Obviously, you need to
change txtShipDate to ShipDate, but I don't know the names
you are using for the invoice number text box or the field
in the table. (Note that I used txt as a prefix to indicate
a text box name.

You did change my guess at the name of the orders **table**
to something that looks a lot like the name of a form. Be
sure to use the name of the table that the form is bound to
via the form's RecordSource property.

Next time you post code in a message, use Copy/Paste so we
don't have to try to separate real problems from your typing
mistakes.

 

[jwr]

I took out txt -- my ShipDate and InvioceNum are the names of the text box
and Orders is the table. When I entered the ship date into the orders
screen, I got the error

Compile Error: sub or function not defined.

If I have no ship date entered in the orders screen, there is no error.

JR


Private Sub ShipDate_AfterUpdate()
If NotIsNull(Me.ShipDate) Then
If IsNull(Me.InvoiceNum) Then
Me.InvoiceNum = Nz(DMax("InvoiceNum", "Orders"), 0) + 1
End If
End If

End Sub

I see that you did not change the names I used for your text
boxes to the names you are using. Obviously, you need to
change txtShipDate to ShipDate, but I don't know the names
you are using for the invoice number text box or the field
in the table. (Note that I used txt as a prefix to indicate
a text box name.

You did change my guess at the name of the orders **table**
to something that looks a lot like the name of a form. Be
sure to use the name of the table that the form is bound to
via the form's RecordSource property.

Next time you post code in a message, use Copy/Paste so we
don't have to try to separate real problems from your typing
mistakes.
--
Marsh
MVP [MS Access]

Thank you for your reply. Below is the code that I entered on my Order by
Customer Subform. I do not get any errors, but I also do not get an invoice
number in the invoice field either. What did I do wrong?

Private Sub ShipDate_AfterUpdate()
If NotIsNull(Me.txtShipDate) Then
If IsNull(Me.txtInvoiceNum) Then
Me.txtInvoiceNum = Nz(DMax("InvoiceNum", "Orders by Customer subform"), 0) +
1
End If
End If

End Sub

You have followed my insanity. My InvoiceNum field on the table is a number
field. I am needing the system to automatically input the invoice number
once the order has a ship date.

 

[Marshall Barton]

That's the reason that I thought you had retyped the code
into your message. There is a missing space:
If Not IsNull(Me.ShipDate) Then

Apparently, you are not precompiling your project before
testing it. I strongly recommend that you do this (Debug -
Compile menu item) as soon as you think you have typed in a
complete procedure. This way, you get a better indication
of what's wrong at the time you are making the changes
instead of some mysterious message while you are testing,