problem with autonumber

  • Thread starter Thread starter Tony Williams
  • Start date Start date
T

Tony Williams

I have a form PrintInvoices that is based on a table Invoices. The table has
one field invnbr. The form has a control txtinvnbr with the control source
being invnbr and a default value of =DMax("InvNbr","Invoices")+1. The form
also has many other controls which are all calculated. The form is opened
from a command button on a form projects which carries across data about the
project.

PROBLEM: On opening the form the value of invnbr is 1 which is the last
number in the table. But when I close the form the table is not updated and
when I open it a second time the value of invnbr is still 1. Where am I
going wrong?
TIA
Tony
 
Tony Williams said:
I have a form PrintInvoices that is based on a table Invoices. The table has
one field invnbr. The form has a control txtinvnbr with the control source
being invnbr and a default value of =DMax("InvNbr","Invoices")+1. The form also
has many other controls which are all calculated. The form is opened from a
command button on a form projects which carries across data about the project.

PROBLEM: On opening the form the value of invnbr is 1 which is the last number
in the table. But when I close the form the table is not updated and when I
open it a second time the value of invnbr is still 1. Where am I going wrong?
TIA
Click to expand...

Are you entering some data? Simply opening a form with default values does not
create a record.

Also be forewarned that using the DMax()+1 technique in a DefaultValue property
does not work in a multi-user environment or on a continuous form. It is better
to apply the value in the Form's BeforeUpdate event instead.
 
Thanks Rick. I am entering another value apart from the invnbr a control
called clientnbr which is based on a field of the same name in the table
Invoices. However on opening the form the value of clientnbr shows the last
number I entered and and the value of invnbr doesn't change.
Basically what I am trying to do is use the sample database Time and Billing
in Access 2000 which has a PrintInvoice screen but the Invoice number has to
be manually input. I'm trying to create a control on that form that
generates a sequential number. Incidently it is not a multiuser environment.
Just me! I wnt to use this databas to keep track of my wife's workload, she
is a legal consultant and we want a datbase that allows us to keep track of
her projects, clients and days worked so that we can produce an invoice.
This seemed to fit the bill and I was trying to enhance it with sequential
invoice numbering.
Thanks
tony
 
Tony Williams said:
Thanks Rick. I am entering another value apart from the invnbr a control
called clientnbr which is based on a field of the same name in the table
Invoices. However on opening the form the value of clientnbr shows the last
number I entered and and the value of invnbr doesn't change.
Basically what I am trying to do is use the sample database Time and Billing
in Access 2000 which has a PrintInvoice screen but the Invoice number has to
be manually input. I'm trying to create a control on that form that generates
a sequential number. Incidently it is not a multiuser environment. Just me! I
wnt to use this databas to keep track of my wife's workload, she is a legal
consultant and we want a datbase that allows us to keep track of her projects,
clients and days worked so that we can produce an invoice. This seemed to fit
the bill and I was trying to enhance it with sequential invoice numbering.
Click to expand...

So you're saying that you open the form to a new record and see the correct
Default Value for invnbr. You make an entry in at least one other field and
save the record. Then when you close and open the form to another new record,
you see the SAME default value for invnbr?

If that is true what value do you see in the record you previously created (in
the table)? Are you sure you have the TextBox for invnbr bound to the field?
 
I'm playing around with this and getting lost :-)
This is what is happening now
I open the form and the value of invnbr is the last number in the table
invoices. I have a control called clientnbr which is based on a field
clientnbr in the same table.
I also have a calculated control Invoice Number that concatenates the two
fields using this formula
=[ClientNbr] & "/" & [InvNbr]
Lets say I open the form and the value of invnbr is 17 (that is the last
value in the table) then I input the value of clientnbr, say 60, then
Invoice Number changes to 60/18 but invnbr stays at 17

When I open the form again on another client invnbr is now 18 and the value
of clientnbr is still 60 (it shouldn't be because it is a new client)
I
n the BeforeUpdate of the form Ihave this

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim nextnbr As Integer
nextnbr = DMax("InvNbr", "Invoices") + 1
Me.InvNbr = nextnbr
End Sub

When I look in the table then the previous records are not saved ie invnbr
1-16,

Hope you can understand my ramblings here. It looks like I'm nearly there
but don't understand what's happening.

Thanks again
Tony
 
Rick,incidently the reason I would like to see the previous invoice numbers
is so that I know which client had which invoice.
Tony
 
Tony Williams said:
I'm playing around with this and getting lost :-)
This is what is happening now
I open the form and the value of invnbr is the last number in the table
invoices. I have a control called clientnbr which is based on a field
clientnbr in the same table.
I also have a calculated control Invoice Number that concatenates the two
fields using this formula
=[ClientNbr] & "/" & [InvNbr]
Lets say I open the form and the value of invnbr is 17 (that is the last value
in the table) then I input the value of clientnbr, say 60, then Invoice Number
changes to 60/18 but invnbr stays at 17
Click to expand...

Based on your assignment of InvNbr in the BeforeUpdate event Invoice Number
should not change to 60/18 until you save the record. I don't understand how
Invoice Number can be displaying 60/18 when invnbr is displaying 17 since that
number is being pulled from the same field. (right?)
When I open the form again on another client invnbr is now 18 and the value of
clientnbr is still 60 (it shouldn't be because it is a new client)
Click to expand...

You lost me here. Is this a new record? What is your default for clientnbr?
If it's a new record then it's not "a new client" until you enter a new
clientnbr and save it.
I
n the BeforeUpdate of the form Ihave this

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim nextnbr As Integer
nextnbr = DMax("InvNbr", "Invoices") + 1
Me.InvNbr = nextnbr
End Sub

When I look in the table then the previous records are not saved ie invnbr
1-16,
Click to expand...

If you see the number 1-16 then they DID get saved right?
Hope you can understand my ramblings here. It looks like I'm nearly there but
don't understand what's happening.
Click to expand...

Sorry, but a lot of it is still very muddled.
 
Thanks Rick I think I need to rethink this one. As I mentioned I'm trying to
use the sample database as my starting point and therefore I think I need to
get my thinking clearer on what I actually want this to do
Thanks, as the man said "I'll be back"
Tony

Rick Brandt said:
Tony Williams said:
I'm playing around with this and getting lost :-)
This is what is happening now
I open the form and the value of invnbr is the last number in the table
invoices. I have a control called clientnbr which is based on a field
clientnbr in the same table.
I also have a calculated control Invoice Number that concatenates the
two fields using this formula
=[ClientNbr] & "/" & [InvNbr]
Lets say I open the form and the value of invnbr is 17 (that is the last
value in the table) then I input the value of clientnbr, say 60, then
Invoice Number changes to 60/18 but invnbr stays at 17
Click to expand...

Based on your assignment of InvNbr in the BeforeUpdate event Invoice
Number should not change to 60/18 until you save the record. I don't
understand how Invoice Number can be displaying 60/18 when invnbr is
displaying 17 since that number is being pulled from the same field.
(right?)
When I open the form again on another client invnbr is now 18 and the
value of clientnbr is still 60 (it shouldn't be because it is a new
client)
Click to expand...

You lost me here. Is this a new record? What is your default for
clientnbr? If it's a new record then it's not "a new client" until you
enter a new clientnbr and save it.
I
n the BeforeUpdate of the form Ihave this

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim nextnbr As Integer
nextnbr = DMax("InvNbr", "Invoices") + 1
Me.InvNbr = nextnbr
End Sub

When I look in the table then the previous records are not saved ie
invnbr 1-16,
Click to expand...

If you see the number 1-16 then they DID get saved right?
Hope you can understand my ramblings here. It looks like I'm nearly there
but don't understand what's happening.
Click to expand...

Sorry, but a lot of it is still very muddled.
Click to expand...
 
Back
Top