Autofill Primary Key as Text Field that joins 2 other fields

[MissKitty]

Howdy all!



I've searched a bit for some guidance on this but I guess I'm not using the right keywords and keep finding relevant but just outside the box results. I'm trying to teach myself Access and hope I can find some patience and mentorship out on the web. I'm pretty decent at figuring out Excel forums and almost feel silly that I struggle with functions in Access that are simple for me in Excel. =( Thanks in advance for direction!

A database I'm building includes locality information and includes three tables - Cities, Counties, States. The Counties table includes a variety of fields - CountyName, StateID (foreign key linked to the States table's two-letter primary key field), CountySeat, CountyDate (year established), Etymology, etc. etc. I would like the primary key in the Counties table to be an autocompleted field generated by joining the text values of the table's StateID and CountyName fields. I.e. The primary key for Jefferson County, Kentucky would be KYJefferson (or perhaps KY-Jefferson).

Can someone advise regarding how to tell the primary key to autofill using an event (I'm guessing after the second of the StateID and CountyName fields have been entered), as well as how (and where exactly) to formulate the field itself to return KYJefferson or KY-Jefferson. (Hopefully this is as easy to do as it is in Excel).

Thanks again =)

[maximus]

This is a that code will populate the CountyID with the StaeID and County Name like:
KY-Jefferson.
Me.CountyID = Me.StateID & "-" & Me.CountyName

Now where to put the code:
Best place where I think this can be put in is the OnClick Event of a Save button if you have that on your form.

Like:

If Me.Newrecord Then
Me.CountyID = Me.StateID & "-" & Me.CountyName
end If
Docmd.Save
You can exclude the if part if you want the code to run all the time.

Here is an example of a similar case where the Primary Key of a Table EmployeePerformance is Autoupdated with a Number which is incremental, First to Alphabets of The Employee Name and Department e.g. 1-SA-AC for Employee Name: Sam Department: Accounts. The number in the primary key will increment by 1 with every new entry made.

Dim strIntAutogeneratedID As String
Dim strMystring As String
Dim intIDNumber As Integer
Dim intcounter As Integer
Dim intBreakSeq As Integer
Dim strMyID As String

If Me.NewRecord Then
If IsNull(Me.Employeename) Then
MsgBox "Please type Employee Name"
Exit Sub
End If
If IsNull(Me.Employeedepartment) Then
MsgBox "Please type Department"
Exit Sub
End If
strIntAutogeneratedID = IIf(IsNull(DMax("[ID]", "Table3")), "0", DMax("[ID]", "Table3"))
If strIntAutogeneratedID = "0" Then
strMyID = 1 & "-" & Left$(Me.Employeename, 2) & "-" & Left$(Me.Employeedepartment, 2)
Else
Do While intcounter < Len(strIntAutogeneratedID)
intcounter = intcounter + 1
strMystring = Left$(Right$([strIntAutogeneratedID], intcounter), 1)
If strMystring = "-" Then
intBreakSeq = intcounter
End If
Loop
intIDNumber = Left$(strIntAutogeneratedID, Len(strIntAutogeneratedID) - intBreakSeq)
intIDNumber = intIDNumber + 1
strMyID = intIDNumber & "-" & Left$(Me.Employeename, 2) & "-" & Left$(Me.Employeedepartment, 2)
End If
Me.ID = strMyID
End If
DoCmd.Save

pls mark the thread solevd if this solves your problem or post any further problems that u might have.