Record based primary key

[scubadiver007]

Hello,

I want my primary key to be

First 3 letters of surname + first 3 letters of forename + DD + YY (Date of birth)

I can use the after update event to update the primary key field but it won't get saved for some reason so have I missed something?

 

[pr2-eugin]

Code please !

 

[missinglinq]

And what happens when you have both Charles Adams and Charlene Adamo, who share a birthday, or Davie Jones and Davis Jones who also share a birthday? Primary Keys must be unique, and even using MM + DD + YY (Date of birth) is no guarantee of uniqueness! The sole purpose of a Primary Key is to uniquely identify a given Record.

You can do this if you want to, and even call it a RecordId, if you like, but don't use it as a Primary Key; use an Autonumber Field, instead.

The code would be like this

[B]Private Sub Form_BeforeUpdate(Cancel As Integer)
 Me.RecordID = Left(Me.Surname, 3) & Left(Me.Forename, 3) & Format(Me.DOB, "dd") & Format(Me.DOB, "yy")
End Sub[/B]

Just repalce the Control names in the example with your actual names.

Linq ;0)>

 

[scubadiver007]

This my code

Private Sub Cand_surname_BeforeUpdate(Cancel As Integer)
 
Me.Cand_regID = UCase(Left(Me.Cand_forename, 3) & Left(Me.Cand_surname, 3)) & Me.Cand_Consultant
 
End Sub

I get an error for

Me.Cand_Consultant

I get a "method or data member not found" error. The field is a drop down box with a query as the source and the format is text. The field is on the form.

 

[pr2-eugin]

"method or data member not found" error.

It does not exist ! You say it does, but the compiler cannot recognize it.. So type Me. and the list of all available Controls, events, properties will drop down. As you start typing Ca it should highlight everything that starts with Ca if it does not, then it is a clear indication it does not exist.

so check the spelling again and try !

 

[scubadiver007]

It is listed (attached the screenshot) but I get another error:

The value in the field or record violates the validation rule for the record or field

 

[pr2-eugin]

Check the field you are trying to update, both in the the Form and the table design, see if you have set any validation there.

 

[scubadiver007]

There are no validation rules and I have attached a screenshot above

 

[pr2-eugin]

I am talking about the Cand_regID field in the table.

Or try debugging.. See what you get in the pop up boxes..

Private Sub Cand_surname_BeforeUpdate(Cancel As Integer)
    MsgBox "ForeName - " & Left(Me.Cand_forename, 3)
    MsgBox "SurName - " & Left(Me.Cand_surname, 3)
    MsgBox "Consultant - " & Me.Cand_Consultant
   [COLOR=Green] 'Me.Cand_regID = UCase(Left(Me.Cand_forename, 3) & Left(Me.Cand_surname, 3)) & Me.Cand_Consultant[/COLOR]
End Sub

 

[scubadiver007]

I get the three message boxes and the consultant information is being stored correctly.

I will have to leave it for now, thanks.

 

[scubadiver007]

"Cand_RegID" isn't being recognised by the compiler. I had added it as a new field but would that make a difference?

 

[David R]

Did you verify that the query/SQL that your form is pulling from includes the new field? That's a frequent tripping point.

 

[scubadiver007]

I had added the field to the form so I deleted both fields and re-added them and now the compiler recognises both of them.

Solved!

 

[pr2-eugin]

Good work !