Automatic Membership Number

[phillsheen]

Hi,
Im putting together a membership data base. the data base has an already existing client base which all have membership numbers in the form of first letter of there sir name, last to numbers of the year and a 4 digit number. So for example Joe Bloggs membership number would be B06001 his sister Betty Bloggs would be B06002 and the guy next door John Doh would be D06001.

What I would like to do it automate this system so everytime I make a new record when I input the sir name into the 'lastname' field on the form it checks the last Membership number and makes a new one. I wouild also like it to change the 06 to 07 when the year changes.

Ive done searches for things like this with Dmax etc but i really could use help with the code as I have no idea how to code this type of thing?

Any ideas on this oue would be great!
Cheers
Phill

 

[MStef]

Hello Phill!

Look at "DemoMembershipNumA2000.mdb"
I think it is what you need.

 

[MStef]

I'm sorry, there was an error in change of year.
Look at a new one, it is OK.

 

[phillsheen]

cheers mate.
does exactly what I wanted.

Phill

 

[phillsheen]

Hi!

Ive been using the code above for the last week or so and its worked great unitl I came to input a new member this morning. The problem arises when I try to add a member whos name begins with 'R'. Ive tried most of the other letters and they seem to work but R throw up a Run-time error '13': Type Mismatch error. In the Debug it high lights wNum = Right(wMax, 3)
Any ideas what this error is throwing up??

Cheers
Phill

 

[MStef]

Hello Phill!

When I try, it works OK with "R". Is it your first record with "R"?

 

[ColinEssex]

This may not be relevant - You say that the membership number comprises of a letter, last 2 digits of the year, and a 4 digit number.

Your example "B06002" has only 3 digits after the year.
Your function Right(wMax, 3) is only going 3 digits from the right. If you had a 4 digit number would this still be ok?

Col

 

[phillsheen]

MStef, This is not the first record under R. I am currently up to R06049 so the next record should be R06050.

Col, Well spotted, the example is correct, there should be 3 letters after the date so B06002 would be correct. I cant use 4 digits after the date as the membership number formats is already in use.

Cheers
Phill

 

[MStef]

I don't know what it can be, when I try with R06049 I
have no problem, I get next R06050. I suggest try next:
open table and see all records with "R" is 049 last.

 

[phillsheen]

Hi,
I found the problem, there was some duff data in the R fields. some fool put RO6 instread of R06! Cant get the staff!!!
Cheers for your help guys!

Phill

 

[MStef]

I was thinkig something like this. It means that somebody strolling about your tables. Tell to users don't make a stupidity.

 

[purplelotus]

I was looking at this and thought what a great idea!

In a database, such as the one I have (thankfully we're still small), of only 19 members, can I modify the code so that the membership number (which is basically 1, 2, 3, etc.) can be changed to be like t07000, etc. or would this destroy the relationship index??

I'm hoping I can modify a membership database since I have limited knowledge of Access or if I'm facing with the daunting task of creating a membership database to track members, status, activties, dues, etc. all on my own from scratch.

 

[Epic]

In a database, such as the one I have (thankfully we're still small), of only 19 members, can I modify the code so that the membership number (which is basically 1, 2, 3, etc.) can be changed to be like t07000, etc. or would this destroy the relationship index??

do you really want this?
is there any benefıt for you?

if you want just for display reasons just format your autonumber and attach a character from somewhere...

 

[purplelotus]

do you really want this?
is there any benefıt for you?

if you want just for display reasons just format your autonumber and attach a character from somewhere...

It would be..the club is at 19 and expected to grow from it...In addition...(I'm trying to figure out,*How did I get volunteer for it? LOL*); We're wanting to find a way to track ads and the individuals (home businesses, outside businesses, etc) and this just helps us assign a unique number particular when we can figure out how to do membership dues, and ad payments etc.

Lol...trouble is learning Access (I'm such a newbie and still trying to figure out how to create a database to track membership, dues, due historys, committee, member profiles, ads, etc) and creating something from nothing...

I'm assuming then I go into tables, design and under auto number add a character, i.e. "AD001" for example?

 

[Epic]

No... leave the auto number where it is... this is unique and will identify the members. Add another field say "MemberType" Text, length 1 to your members table... additıonaly creat another table (lookup fo thıs fıeld) where you can store the description of the member types

A - New
B - Junior
C - Senior
D - Sailor , etc

wherever you want the member id to be shown concatenate the MemberType with the formatted Autonumber ... from the top of my head... Format(AutoNoField,"00000000") or somethıng like this. This way you may shot 2 rabbits with one bullet.

Hope that helps

 

[purplelotus]

No... leave the auto number where it is... this is unique and will identify the members. Add another field say "MemberType" Text, length 1 to your members table... additıonaly creat another table (lookup fo thıs fıeld) where you can store the description of the member types

A - New
B - Junior
C - Senior
D - Sailor , etc

wherever you want the member id to be shown concatenate the MemberType with the formatted Autonumber ... from the top of my head... Format(AutoNoField,"00000000") or somethıng like this. This way you may shot 2 rabbits with one bullet.

Hope that helps

I think I gotcha

1. Add a field called MemberType [text, length 1] to the member table

2. Create another table for the description of the type of membership

With regards to connecting the membertype to the formatted autonumber, basically does that mean I create a link froI'm the membertype to the auto number or will this require adding a code...Lol

I'm so sorry..slow here...I'm literally one level below Access for Dummies