Incremental numbering from existing Number Sequence (1 Viewer)

jcbhydro · Aug 28, 2013

Good Afternoon from West Sussex, UK,

I have a Primary Key field named Member ID(Number format) in a Table named Mail List. The field is populated with existing ID numbers and my need is to use my Member Entry Form to increment by 1 from the last entry.
I have gleaned from other references that this can easily be achieved by the following entry in the Default Value of the property sheet for the relevant field;

=DMax("Member ID","Mail List")+1

The Form saves without error but when I attempt to add a new record in the entry form, the ID Number reads "#Error".

The solution seems so simple that I cannot vizualize what can go wrong.

Any suggestions would be gratefully received.

Regards,

jcbhydro

pr2-eugin · Aug 28, 2013

Try..

Code:

=DMax("[COLOR=Red][B][[/B][/COLOR]Member ID[COLOR=Red][B]][/B][/COLOR]","[COLOR=Red][B][[/B][/COLOR]Mail List[COLOR=Red][B]][/B][/COLOR]")+1

jcbhydro · Aug 28, 2013

Paul,

Thank you for your rapid response to my problem.
I have incorporated the modified code in the 'Default Value', but the result is still #Error.
There must be something else preventing activation. Any other thoughts?

jcbhydro

pr2-eugin · Aug 28, 2013

Are you using Access 2010? If so.. for some weird reason some Domain functions does not work when you use them as the Control Source/Default Value.. Try it in VBA..

Code:

Private Sub Form_Load()
    Me.maxIDTxtBox = Nz(DMax("[Member ID]", "[Mail List]"), 0) + 1
End Sub

jcbhydro · Aug 28, 2013

Paul,

Thanks for the VBA alternative suggestion.
Sadly, I have no experience of VBA and wouldn't know where to start.

I tried to make a 'module' incorporating the code you suggested but I suspect that that is not what is required. It certainly didn't work.

Your further assistance would be appreciated.

jcbhydro

pr2-eugin · Aug 29, 2013

No it should not be a Module.. It should be the Form method.. Go to Form Design view, then click on the Events tab,

1. Set the On Load property of the Quantity text box to [Event Procedure].
2. Click the Build button (...) beside this. Access opens the Code window.
3. Enter this line between the Private Sub... and End Sub lines:

Code:

Private Sub Form_Load()
    Me.maxIDTxtBox = Nz(DMax("[Member ID]", "[Mail List]"), 0) + 1
End Sub

pr2-eugin · Aug 29, 2013

Sorry the code should be..

Code:

Private Sub Form_Load()
    [COLOR=RoyalBlue][B]If Me.NewRecord Then [/B][/COLOR]Me.maxIDTxtBox = Nz(DMax("[Member ID]", "[Mail List]"), 0) + 1
End Sub

jcbhydro · Aug 29, 2013

Thanks again Paul,
However, there is still a problem.
I followed your detailed procedured but I now get a compling error.
With maxIDTxtBox highlighter the error is "Method or Data Member Not Found". I found that this is a specific error in Access 2010.
Is the reference ID in the Code something different to my [Member ID]?
Any ideas?

jcbhydro

pr2-eugin · Aug 29, 2013

Okay I do not know what your Text Box name is.. So I gave it a random name - maxIDTxtBox.. Change it to the name you have it.. Try Me.[Member ID]

jcbhydro · Aug 29, 2013

Paul,

I was confident that your 'new record' addition was going to do the trick. Sadly not so!
I have corrected the ID to [Member ID] but I am now receiving a 'syntax' error.
Having added an 'If' statement should I not need an 'endif' statement also.

jcbhydro

pr2-eugin · Aug 30, 2013

jcbhydro, the If does not need an End If in this case as it is only executing one line of code.. What is the code you have? Could you copy and paste it as you have written it?

jcbhydro · Aug 30, 2013

Pauk,

Thank you for your continued assistance.

The single line of code is;
If Me.NewRecord Then Me.max[Member ID]TxtBox=Nz(DMax("[Member ID]", "[Mail List]"), 0) + 1

This preceded by the Private SubForm statement and followed by the endsub statement, both of which are automatically generated by the procedure yo outlined.
As previously mentioned Member ID is the primary key field in number format which I am wishing to increment for new records and Mail List is the table name.

Regards,

jcbhydro

pr2-eugin · Aug 30, 2013

jcbhydro said:
If Me.NewRecord Then Me.max[Member ID]TxtBox=Nz(DMax("[Member ID]", "[Mail List]"), 0) + 1

There is your problem.. You have to understand that I used a DUMMY name.. You have to match it as per your design.. If the name of the Field is Member ID then use..

Code:

If Me.NewRecord Then Me.[COLOR=Red][B][Member ID] [/B][/COLOR]= Nz(DMax("[Member ID]", "[Mail List]"), 0) + 1

When you type Me. followed by M it will list the list of all controls/fields/events associated with the Form.. Choose the Field you wish to UPDATE.. Not the one I gave you.

jcbhydro · Aug 31, 2013

Paul,

Despite your considerable efforts, I fear the end result is not achieved.

The code has been modified as suggested and when run does not produce any error message, but on selecting to input a new record, the Member ID Field has not incremented as intended.

I am at a loss as to how to proceed.

I am away from any WiFi access for the next 8 days so am unlikely to access the website for a while.

regards,

jcbhydro

pr2-eugin · Sep 2, 2013

Okay when you do get back on here, please post a Stripped down version of the DB file..

pr2-eugin said:
How to Upload a Stripped DB.

To create a Sample DB (to be uploaded for other users to examine); please follow the steps..

1. Create a backup of the file, before you proceed..
2. Delete all Forms/Queries/Reports that are not in Question (except the ones that are inter-related)
3. Delete auxiliary tables (that are hanging loose with no relationships).
4. If your table has 100,000 records, delete 99,990 records.
5. Replace the sensitive information like Telephone numbers/email with simple UPDATE queries.
6. Perform a 'Compact & Repair' it would have brought the Size down to measly KBs..
7. (If your Post count is less than 10 ZIP the file and) Upload it..

Pat Hartman · Sep 2, 2013

I don't know if the problem is caused because you are using the Load event but I would never use that event to generate an ID. I would use the BeforeInsert event which runs ONCE, the first time the user types something in a control and ONLY for new records so you don't need the If statement.

The problem with using the BeforeInsert event is that some time can elapse between the time the ID is generated and when the record is saved. The user could easily take a phone call or go to lunch even. During this time, a different user could also attempt to add a record and generate the same ID so you absolutely need to trap for duplicate ID numbers and do something intelligent if the error is raised. A better place to generate the ID is the BeforeUpdate event but here you will need the If to determine if the current record is a new one. The BeforeUpdate event is the LAST event that runs before a record is saved so although it is still possible, it is much less likely that a duplicate could be generated.

So, whenever you generate your own unique ID, you have the responsibility of trapping for a duplicate ID and handling it either by generating a new number and automatically trying the save again or putting it on the user's shoulders.

jcbhydro · Sep 15, 2013

Paul,
I have doctored the file to remove all irrelevant forms, queries and reports, leaving only the main table 'Mail List' plus associated 'Group' tables and a single 'Member Entry' Form. This form is where I would expect to automatically increment the Member ID number.
I have removed all but 15 of the records and deleted unrelated fields which detail members preferences. Surprisingly, the file is still 1.3 Mb after compacting.

I would be extremely grateful if you are able to ascertain why your suggested code does not achieve the auto incrementing required.

Regards,

jcbhydro

jcbhydro · Sep 17, 2013

Paul,

I thought that I had uploaded a 'cut down' version of my database to this thread, but it hasn't appeared. I will try again.
Although I have followed the recommendations for a [cut down' version it is still pretty large.

jcbhydro

jcbhydro · Sep 20, 2013

Paul,

Have you had the opportunity to look at my cut down db yet?

Regards,

jcbhydro

jcbhydro · Oct 6, 2013

Paul,

I have revisited this problem and used the =DMax proposal again and amazingly it now works perfectly. I am pretty certain that I am not doing anything differently this time but in any case it works.

Thank you once again for the considerable effort you have expended on my behalf.

jcbhydro

Incremental numbering from existing Number Sequence (1 Viewer)

Registered User.

Super Moderator

Registered User.

Super Moderator

Registered User.

Super Moderator

Super Moderator

Registered User.

Super Moderator

Registered User.

Super Moderator

Registered User.

Super Moderator

Registered User.

Super Moderator

Super Moderator

Registered User.

Attachments

Registered User.

Attachments

Registered User.

Registered User.

Similar threads

Users who are viewing this thread