Auto Increment Composite Primary Key

[rprieto]

I need help in creating an autonumber like field.
Program_Name and Task_ID are composite primary keys.

Originally Task_ID was an autonumber field type and this was OK since the database had only one Program. Now it is to be used for multiple programs.

How can I auto-increment the Task_ID field to create a unique ID for each Program_Name? See sample below.

Task_ID Program_Name
1 Program A
2 Program A
1 Program B
1 Program C
2 Program C
3 Program C

VBA is new to me so more info is best for me.
Thanks for your time.

 

[VBAjedi]

What about just concatenating Task_ID and Program_Name into a third column (so the first data row in your example would have a key of "1Program A&quot? You could do this with a spreadsheet formula. If your data starts in cell A1 and your new key is going to be in column C, put the following formula in C1:

=A1&B1

Or you could use VBA as follows:

Sub CreateKey()
Dim x As Integer, NumRows as Integer
NumRows = 6
For x = 1 To NumRows
    Range("C" & x).Value = Range("A" & x).Value & Range("B" & x).Value
Next x
End Sub

Let me know if this helps

VBAjedi

 

[rprieto]

What I hope to do is this. Upon entering data for a new Program_Name, say "Program D", I would like Access to recogonize there are no pre-existing records of "Program D" and automatically create a new Task_ID to be "1".

Also, I need Access to recogonize upon entering a new record for Program B that the last known record # (Task_ID) is 1 thus assigning the "new" record Task_ID # to be "2" (for Program B) and so on.