A member code that is the primary key

[john doe the 3rd]

hey all,
having some trouble with the following, any help would be fantastic.

i basically have a table that contains some information about library members. i have a primary key , which needs to remain primary because i will link the table later on.What i would like to know is :

is it possible to somehow auto-generate a member code # by taking the first letter of theirfirstname, and the first letter of their surname and then add an autonumber to that aswell. e.g

there are 10 members on record, autonumber assigns them a number , ie member 1, 2, 3 and so on. member 1 is called JOHN DOE so if we modify the autonumber the new code would be JD1 ,

member code = < first letter of first name > + < first letter of surname > + < autonumber >

any other suggestions as to how to create sumthing like this?

thanks in advance.

 

[Andrew Fergus]

Hi,

I'm a little confused about which item is the primary key - although I think it is the autonumber. Also, I don't think the autonumber can be changed per se (someone please correct me if I am wrong), but there are a couple of ways around it. {BTW, linked fields within tables don't have to be the primary key and vice versa - you can link any field you like so long as the database tables and relationships have been correctly set up}

The first method involves setting up a new field in your table and populating it with the member code for any new members, as follows :

You can generate the "member code" by adding "MemberCode" to your library members table, then creating an update query where you add the "MemberCode" field to the update query, in the criteria section enter "Is Null" (without the quotes) and in the "Update To" part enter :

=Left( [FirstName] , 1 ) & Left( [Surname] , 1 ) & [Autonumber]

{NB : Remember to use your actual variable names - I have assumed the variable names "FirstName", "Surname" & "Autonumber"}

Save and Run the query. This will then fill in the member codes for any new members.

If you are using a form to enter the details for new members, then you can either a) have this query run when the form is closed via a macro, or b) use the same logic within the form itself to populate the "member code" variable on the form (again via a macro that sets the value of the "member code" once either the surname or first name have been edited).

The second method involves calculating the member code if and when you either want to see the value on a form or in a report. You can do this with the same logic by creating an unbound text box on the form or report and using the same code (per the other method above) to calculate the "member code" there and then when you open the form or report. So in effect, it only exists on the form or report when you need it.

Please note that my suggestions above assume that the 2 name fields are populated with something - I have not included a test to see if one or both of the names was left blank.

Please advise if this is not quite what you are looking for.

HTH, Andrew.

 

[john doe the 3rd]

thanks for your post, i have tried the second method and made sure i use the right names e.g "FIRSTNAME" and AUTONUMBER but i get a type mismatch when trying to open the form after i add that expression.


any ideas

sorry if this isnt really clear , bit new to access , and yes the primary key is the autonumber.

 

[Andrew Fergus]

Hi,

Sorry it didn;t work first time - following are some specific notes on how to do it using the 2nd method on the form.

To create the "member code" on the form, make sure the first name and surname are set up as text (in the table design). In the form design, make sure the 3 variables are available in the underlying table (or query), create a new (unbound) text box and in the "control source" section enter :

= Left( [firstname], 1) & Left( [surname], 1) & [key]

{Note : I used the variable names "firstname" (text), "surname" (text) and "key" (autonumber) in my table - remember to use your variable names}

I got this to work in a test database by doing exactly what I posted.

If you can't get it to work, then can you post some details of the steps you are doing and the actual variable names from your table.

HTH, Andrew.

 

[john doe the 3rd]

excellent! thanks , works like a dream


would you suggest i use a different combination to make the member code more effective? although its only a school project , and the entire database will not have more than 100 member records, it seems a bit unproffesional , could you provide any other combinations of either firstname ,surname or anything else that could make a better "member code", im not very good with ideas

 

[Andrew Fergus]

I'm pleased it worked. In answer to this :

would you suggest i use a different combination to make the member code more effective?

IMO what you have done is fine. I personally don't place much "value" on unique keys - by this I mean that if I want a unique key then that is all it is - a unique id and nothing more. Others may prefer to have the first few characters mean something and then the digits signify something else - but I prefer to have separate fields for the different meanings rather than having a "cryptic" key of some description.

HTH, Andrew.