Combine two field values in a separate field to create a Primary Key

[alexavier]

Hi,



I know this question has been asked before, but I'm really confused.

I've followed instruction from both https://www.accessforums.net/access/...eld-14038.html and https://www.accessforums.net/access/...2-a-10960.html tried using the me.PID=me.P_FullName&me.P_State in After Update in my form for both P_FullName and P_State since either of those being updated would affect PID.

But when I run the form in form view it says "Microsoft Access cannot find the object 'me.' " - I'm utterly lost now..

Please help.

I'm using MS Office Access 2013

[RuralGuy]

Where do you have this code?

[alexavier]

Sorry, I don't think I understand your question. I use the buttons on access really and try my best to not tinker with the code unless I really have to (I use google then..) I'm quite new to Access actually, started the day before yesterday!

[RuralGuy]

Do you have this in the AfterUpdate event of a Form? If not, where is it?

[alexavier]

Oh yes - it is in the AfterUpdate event of P_FullName and P_State - which are both fields in the form.

[Missinglinq]

...it is in the AfterUpdate event of P_FullName and P_State...

But is it in the Code Module for the Form? It should be and look like this

Private Sub P_FullName_AfterUpdate()
 Me.PID= Me.P_FullName & Me.P_State 
End Sub

Private Sub P_State_AfterUpdate()
 Me.PID= Me.P_FullName & Me.P_State
End Sub

Linq ;0)>

[alexavier]

It's in the 'Property Sheet' of the field.. How do i check if it is in the code module?

[alexavier]

Thank you very much for replying!

[RuralGuy]

While viewing the property sheet for the form, select either control and press the "..." button. What happens?

[alexavier]

Oh my god! I just got it happening - so I had to go to the code module and paste the code which missinglinq posted nice! Thank you so very much!

[RuralGuy]

Glad Linq and I could help. Merry Christmas.

[alexavier]

I should also tell you - we are doing this as part of a massive research into the condition of prisons in India - so this really goes a long way! Thank you very much!

[Missinglinq]

As Allan said, we're glad we could help!

Given the reported treatment of domestic servants, in India, I cannot imagine how prisoners must be treated!

Now that you've got this working, I should warn you that using a Primary Key that is composed of a full name plus a state name could be fraught with danger! PKs must, by definition, be unique. If, by 'state,' you are speaking of states, as in 'Assam,' 'Bihar,' etc., I would think that a given 'state' would not be unique to a single Record, but would be a valid Value for multiple Records. And if P_FullName is a person's full name, you should know that no combination of first, middle and last names is ever truly unique to one person, no matter how strange, odd or curious that name is! Therefore the combination of P_Fullname & P_State is not guaranteed to be unique.

If the above assumptions about what P_State and P_Fullname represent are correct, you really need to add a third Field to this calculation, such as the Date and Time the Record is created. One way would be to use the Now() function, added to P_Fullname and P_State, to create an almost assuredly unique PK.

Private Sub P_FullName_AfterUpdate()
 Me.PID= Me.P_FullName & Me.P_State & Now() 
End Sub

Private Sub P_State_AfterUpdate()
 Me.PID= Me.P_FullName & Me.P_State & Now()
End Sub

Good luck with your project!

Linq ;0)>

[RuralGuy]

@Alexavier:
We should also point out a couple of things. 1) AutoNumbers are still the best PrimaryKey (PK) for a table. 2) You can create a unique index on those fields if you want without making them the PK.