Not use autonumber

[jamccarley]

I have a field where I want to have a number and increment +1 every new
record. I have auto number as the primary key, but i don't want to use that
as the number I record. The field name is "SVR_number", the form is
"SVR_form" and the table is "SVR_log". In the defalt value of "SVR_number" I
have DMax("SVR_number","SVR_log")+1. It does not return any value, not even
an error.

Josh

 

[BruceM]

Is that the SVR_Number field in the table, or the name of the control on the
form. If the latter, be sure it has a different name from the field
(txtSVR_Number, for instance).

If it is the default value of the text box bound to SVR_ Number, try placing
an = sign in front of the expression.

You can use VBA code, as suggested, but I would still use the Default Value
property of the text box. Another option is to test for a new record, but I
would use Default Value if possible.

You could have problems with your system in a multi-user environment. One
solution can be found in the sample database here:
http://www.rogersaccesslibrary.com/...?TID=395&SID=ceccb77af36fad58az7c212dca792a8d

The link should all be on one line. The whole library is here:
http://www.rogersaccesslibrary.com/forum/forum_topics.asp?FID=1
The database is named AutonumberProblem.

 

[Larry Daugherty]

Something like:

Me.MyControl = Nz(DMax("MyField", "Mytable"), 0) + 1

HTH

 

[BruceM]

To clarify about the default value property of the text box in VBA, I meant
something like:

Me.TextBoxName.DefaultValue = DMax("SVR_number","SVR_log") + 1

or

Me.TextBoxName.DefaultValue = Nz(DMax("SVR_number","SVR_log"),0) + 1

 

[John W. Vinson]

I have a field where I want to have a number and increment +1 every new
record. I have auto number as the primary key, but i don't want to use that
as the number I record. The field name is "SVR_number", the form is
"SVR_form" and the table is "SVR_log". In the defalt value of "SVR_number" I
have DMax("SVR_number","SVR_log")+1. It does not return any value, not even
an error.

Josh

A default value in a Table cannot refer to any table field.

I'd do this on the Form that you use to enter data; you can use the form's
BeforeInsert event

Private Sub Form_BeforeInsert(Cancel as Integer)
Me![SVR_Number]) = NZ(DMax("[SVR_Number]", "[SVR_log]"))+1
End Sub

This can be a bit risky if it's a multiuser application; it's possible that
one user might assign an SVR_Number and a second user creates a new record
before the first user's record is saved to disk. If there are no other
required fields you can add a line

DoCmd.RunCommand acCmdSaveRecord

to explicitly save the record after finding the dmax.

 

[jamccarley]

This database will only get about 100 records a year, so is there really any
harm in using the autonumber to generate the unique number. There is no
reason that they have to be in sequential order?

I have a field where I want to have a number and increment +1 every new
record. I have auto number as the primary key, but i don't want to use that
as the number I record. The field name is "SVR_number", the form is
"SVR_form" and the table is "SVR_log". In the defalt value of "SVR_number" I
have DMax("SVR_number","SVR_log")+1. It does not return any value, not even
an error.

Josh

A default value in a Table cannot refer to any table field.

I'd do this on the Form that you use to enter data; you can use the form's
BeforeInsert event

Private Sub Form_BeforeInsert(Cancel as Integer)
Me![SVR_Number]) = NZ(DMax("[SVR_Number]", "[SVR_log]"))+1
End Sub

This can be a bit risky if it's a multiuser application; it's possible that
one user might assign an SVR_Number and a second user creates a new record
before the first user's record is saved to disk. If there are no other
required fields you can add a line

DoCmd.RunCommand acCmdSaveRecord

to explicitly save the record after finding the dmax.

 

[John W. Vinson]

This database will only get about 100 records a year, so is there really any
harm in using the autonumber to generate the unique number. There is no
reason that they have to be in sequential order?

I use autonumbers all the time, but they're never visible to the user. For
instance a People table with an autonumber ID will be presented to the user,
generally, using a Combo Box storing the numeric ID, but showing "Lastname,
firstname middlename" as the visible portion of the combo.

The code to assign sequential numbers is useful when there's a valid business
need for sequential numbers, for example an invoice number. Accountants and
auditors break out in hives when they see Invoices 312, 313, 316 and 317...