Full Version: Increment without using Autonumber
UtterAccess Forums > Microsoft® Access > Access Forms
asurfc
Can I increment one number (without using Autonumber) in a form relative to the last record on that form?
dannyseager
to increment a number without using a autonumber you need to use the dmax() function
max("fakeID","tablename")+1
where fakeID is your fake autonumber.
quest4
Absolutly, in fact there are a couple of ways, but the easiest is putting this in the form's OnCurrent event:
If Me.NewRecord Then
Me!Your Name.Value = Nz(DMax("[YourTableField]", "YourTable"), 0) + 1
End If
Hope this is clear. hth.
asurfc
Wouldn't this increment according to the highet value in the table and not the last record in the form (which is what I am looking to do)?
asurfc
Understaood, but wouldn't this increment according to the highet value in the table and not the last record in the form (which is what I am looking to do)?
quest4
Your highest value should be yuou last record, normally.
asurfc
I agree, but in this case, I have different users entering info into the same table for their department.
quest4
I understand, I have the same problem on my main forms, I use this on my maintainance forms, for add types and things. I got a function from the MS Knowlwdge base , of all places. Just put the in the forms OnOpen event:
Me.YourName= NextIDNumber()
Then in the Cancell or Delete cmdButtons add this procedure to the OnClick:
Call DeleteRecord 'Call DeleteRecord() to reset ID No
DoCmd.Echo False
DoCmd.SetWarnings False
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
DoCmd.SetWarnings True
DoCmd.Echo True
DoCmd.Close
Try it and play with it, but it works for me. hth.
PS: Set the number in the table to yourcurrent highest number.
Edited by: quest4 on Wed Nov 24 9:36:30 EST 2004.
dannyseager
>
Odont see the problem...
oes the fake autonumber need to be for the whole table (i.e. does it increment up by 1 for every record or is it for every record for a specific person, or for a specific project?
does it want to look like..
fakeID | UserID | ProjectID
1 1 1
2 1 2
3 2 6
4 5 6
For does it need to look like
fakeID | UserID | ProjectID
1 1 1
2 1 1
1 2 6
2 5 6
3 1 6
or am I missing something
Orv
Not trying to throw anyone off, but I think my question (problem) is the same, and didn't see the need for a new thread.
I have a field (RTFnumber) where the input is based on year-record# (04-0001).
What I want to do is to be able to have the new record display the new# (the next one) automatically.
I was thinking of using 'primary key' for this, but will I be able to change the 'autonumber' when I start entering records in 2005 (ie..05-0001, etc...). Currently my records for the past few years have never gone over 700 (04-0700) the extra '0' is for 'just in case'.
I think the code below (posted by 'guest4' may be the answer, or at least on the right track.
If Me.NewRecord Then
Me!Your Name.Value = Nz(DMax("[YourTableField]", "YourTable"), 0) + 1
End If
What do I replace 'Your Name.Value' with?
Thanks, Orv
Larry Larsen
An alternative to AutoNumbers
On order to utilise Access properly, you will need to use relationships between tables, and to do this, you will need to ensure that there is a unique field in the one side of each 'one-to-many' relationship.
If there is a natural key for the table, then that may be used. However, there might be the problem that although there is a unique value for each object, at the time of entry this value isn't known. Therefore, you must use an artificial key. Access supplies its own built-in unique field, in the shape of an Autonumber. However, there are various problems with using an autonumber:
* Firstly, you shouldn't be revealing the Autonumber field to the user in a database, and expect it to have any meaning. The Autonumber should really only be used internally by Access to relate records in relationships.
* With A2K, there is the possibility that this number might not be unique. Look at article Q257408 on the Knowledge Base for further information.
* Autonumbers are not guaranteed to be consecutive. Firstly, in a database that is replicated, the Autonumber NewValue is set to Random from increment, in order to try and minimise the chances of two users in different replicas creating different records with the same primary field. Secondly, if the entry of a new record is interrupted, the primary key will already have been allocated, and therefore when the next record is added successfully, it is not given the value of the uncommitted record, but the one after that. Once a autonumber has been created and then deleted, there is no way to reuse that number.
You can create your own unique primary key, using DMax, called in the BeforeInsert event for the Form:
Private Sub Form_BeforeInsert(Cancel As Integer)
Me!UniqueFieldName=Nz(DMax("[UniqueFieldName]","[TableName]"),0)+1
End Sub
Another option that you might want to consider is creating a single field table, and storing the unique value in there until it is needed, at which point it is read out, and a new value created instead in the table.
A question that is often asked is how to reset the autonumber back, so that it starts incrementing from 1 again. Well, firstly, if you are using the autonumber datatype properly, then the value of the field should have no meaning whatsoever. Anyway, to reset the autonumber, delete all of the data from the table, and then compact your database. Now the first record that you enter into this table will start off at 1.
Something else that seems to be popular is to try and create and primary key field that uses the date in conjunction with an autonumber, for example 010120010001 might be the first record that was created on 1 Jan 2001, whilst 050220010015 would be the fifteenth record created on 5 Feb 2001. This is a very bad idea - the idea of relational databases is to store one piece of information in each field. If you wanted to do something like this, you would need to modify the code above slightly, and add a date field to the table:
Private Sub Form_BeforeInsert(Cancel As Integer)
Me!UniqueFieldName = Nz(DMax("[UniqueFieldName]", "[tblUnique]", "[DateField]=Date()"), 0) + 1
Me!DateField = Date
End Sub
You can then create a primary key for the table that is based on both the DateField and UniqueFieldName by selecting them both in the design view of the table by using the Control key and then clicking on the Primary Key symbol. You will then have a number that increments each day, and resets itself the next day.
thumbup.gif
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.