How to create a MemberID field that autonumbers

[dniezby]

I need to create a MemberID field that autonumbers and uses a prefix.

EG: YearJoined+0001

I want this as a the PK as well. Is there a way to do this?

I tried a default value Year(Now())&format([ID],"0000") but it gives me errors.

Any thoughts on an approach for this?

[June7]

Custom unique identifier is a common topic. Search forum or Google.

For a start review:
https://www.accessforums.net/access/...ers-21361.html

http://forums.aspfree.com/microsoft-...ta-403208.html

[dniezby]

Wow, that just confused me event more.

[June7]

It can be tricky especially for a new user without a programming background. Where exactly do you get lost? There are lots of threads discussing this.

What you tried probably doesn't work because the ID does not yet exist when the DefaultValue tries to populate field in new record.

[dniezby]

You lost me at Hello...LOL.

I actually think I will just make things simple. I formatted the member ID \"10000" and set it to increment.
Screw it. Then I just created the Joined field and I still have all the information I wanted in the ID.

Thanks anyway.

[June7]

The concatenated value will not be usable as pk/fk. It is a constructed field. The autonumber MemberID should be the pk/fk and users will not be aware of it. Can use the concatenated field for display on forms and for filtering in queries but it is not the link between tables.

[dniezby]

Okay, my other method of just creating a formatted ID field isn't going to work. AARGGGGH!

Now that, that scream is out of the way.

I've read those linked posts and they don't help me at all. They're talking about converting text to a number then they give me some code and I can't translate it to what I'm doing or even know WHERE to put it.

I can't believe something that should be so simple is so difficult. I did make some changes to my table.

1. I now use a regular ID as the PK for linking tables etc.
2. Created a Member ID field as a text (or would you recommend number)

I HAVE NO IDEA where to put ANY code.

I was thinking of making the field use a AfterUpdate macro but can't seem to do that either. Pointing me to code that I can't translate doesn't help.
I simply want to join two things and write it to Member ID field. year(Now())&FORMAT([ID],"0000)

Why is this so difficult to do?
Why isn't there someplace that has code for this as it DOES appear to be something requested a lot but never really answered? You'd think this would be built in by now because every business needs customer numbers and member numbers etc.

[June7]

It is possible to construct the member ID with the autonumber of a new record but not by using the DefaultValue property. Requires code and since I use only VBA not macros my advice will be with that in mind.

The expression you showed will have to be in some event. The trick is figuring out which event. Possibly the form BeforeUpdate. With form in design view, in the event property select [Event Procedure]. Click the ellipses (...) to open the VBA editor. Type code in the procedure.