Solved Auto increment number based on machine number coloumn (1 Viewer)

Ravi Kumar

Registered User.
Local time
Today, 06:06
Joined
Aug 22, 2019
Messages
162
Dear all ,

In my table want to auto increment the spool number based on my machine number column :
i don't know how to explain this problem in depth , hence i am attaching my sample database , please help me.
 

Attachments

  • Cleaning Area Register.accdb
    512 KB · Views: 143

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 20:36
Joined
Feb 19, 2002
Messages
42,981
It only takes one line of code. I added it to the form's BeforeUpdate event. I also cleaned up some other things.
1. I removed the 0 defaults for the numberic fields. Foreign keys should NEVER default to 0. You might want quantity or money fields to default to 0 but never foreign keys.
2. I make fields required and I set Allow Zero Length strings to No. It makes no sense to make a text field required but allow it to be "blank".
3. I locked the ID field and the spool number controls since the user cannot provide values for those fields.
4. I also changed the tab property of ID and spool number to no so the user doesn't tab into those fields. The fields are enabled so focus can be put into those fields but only by clicking and never by tabbing.
5. I also removed the Change Auto Corrupt setting. This is more dangerous than it is helpful. If you want to change the name of a column in the table and you want Access to propagate the changes, then turn it back on and also set log to true. Otherwise, just leave it off.
 

Attachments

  • Cleaning Area RegisterPat.zip
    30.4 KB · Views: 146

Ravi Kumar

Registered User.
Local time
Today, 06:06
Joined
Aug 22, 2019
Messages
162
Thank you so much ..
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 08:36
Joined
May 7, 2009
Messages
19,169
you can also use Data Macro (After Insert) to increment the Spool_number.
With or without any Form, the field will be incremented.
 

Attachments

  • Cleaning Area Register.zip
    43.9 KB · Views: 162

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 20:36
Joined
Feb 19, 2002
Messages
42,981
arne, the macro should find the max value of spool_number for a given machine number and increment that. It should not just count records and add 1. That method fails if records are ever deleted so it should never be used to add a sequence number to a group.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 08:36
Joined
May 7, 2009
Messages
19,169
that i easy to implement. comparing the value of [counter] to the value of spool_number.
 

Users who are viewing this thread

Top Bottom