Primary key creation from 3 user input fields

[mattpfc]

Hi all,

I am trying to create a user form which enables the end user to add "events" to a database which can then be referenced in other areas for things such as bookings etc.

Anyway what I am having a problem with is the aspect of creating a primary key for each new event entry that will adhere to a naming convention that I want to use.

The convention uses the first 3 letters of the event name, then the number of the month, then the year number i.e. Gosport, November, 2003 would be what the user enters but the primary key would look like GOS__11_03 or GOS_NOV_03

Any suggestions on how I can do this please.

 

[bat17]

In table design you can join the three fields to make them a joint index.

>View>indexes

start a new row, in the first column add a name for the index.
and set "Unique" to Yes and "Ignore Nulls" to No.
In the second column pick your first field, then work down the column adding your fields.

I would not set this as your primary key but use an Autonumber as this is easier to work with in queries.

To show your number "joined up2 you c an add them together in a query.

You will need to think about possibility of getting two events that have the same first three letters in a month as well!


HTH

Peter

 

[mattpfc]

I followed your advice on how to add an index but this did not really help, prehaps I missed somthing or did not explain my problem properly.

I want to create a auto generated field that takes the first three/four characters from 3 different user input fields. So what I would be getting would be a combination of 3 fields to create one unique field that I could use as a primary key. I know that this field will definalty be unique because there will very rarely be a time where this data is entered, and I know for a fact that the chance of of being the same is impossible.

I have this as the default value for the text field at current, but I only want the first 3 or 4 characters from each form text field. Also if possible, a deviding character of "_" (underscore)

=[Event_Name]+[Event_Month]+{Event_Year]

Is this possible?

 

[bat17]

You will need something like:-

Me.txtID = left([Event_Name],3) & "_" & left([Event_Month],3) & "_" & right([Event_Year],2)

Where txtID is the field you wish to create the ID in. You will need to run the code somehow, this could be behind a button or from the update event of a field.
You should really add some validation as well incase not all of the fields have been filled in when the code fires!!

Peter

 

[mattpfc]

Thanks again Peter.

This line of code works, however I am having a problem getting it to activate for each record as I scroll through, it always has the value of the first record in the table.

How could I get this line of code to activate each time the record is change or a new record is entered?

 

[bat17]

If you want to update your existing records then you should create an update query and use that calculation for the update.

For new records I would add a button, and put the code behind that, as you cannot generate the ID untill all the relevant fields have beed filled in.
You could also use this button to update your existing records as you scroll thru the form if you only have a few records to do.


Peter