Need help in incrementing composite primary key

[rprieto]

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

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

How can I auto-increment the Prg_Rec_No field for each new or additional Program_Name? See sample below.

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

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

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

 

[cmmrfrds]

The fields Program_Name and Prg_Rec_No together are the logical key for your table. Make another auto number field as the primary key. Do a select max(Prg_Rec_No) where
Program_Name = @yourProgramVariable. Then, put in some logic to increment the number as needed. You can index both fields and allow duplicates on Program_Name and Prg_Rec_no.

 

[psemianonymous]

The major change is that you will have to do the work yourself, whereas with AutoNumbers Access did the counting for you.


If you're doing data entry via forms, in the Form_BeforeUpdate() event, you'll have to calculate the next available ID and assign it to your "ID" textbox. It's best to do so immediately before saving, so as to avoid conflicts with other users creating records as well.

The simplest method of finding the next available ID uses the DLookup() function, or more specifically its specialized brethren DMax().


I won't go into the specifics; try a go at this and come back with any questions.

--
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

Corrupt MDBs FAQ

http://www.granite.ab.ca/access/corruptmdbs.htm

 

[rprieto]

Thank you for your near immediate reply. Forgot to mention that VBA is new to me. More info is best for me. Will work on later today.

 

[BLBurden]

foolio12 , I read your post and this is what I need to learn about DMax. I am new and this and the help in Access is not turning the bulb on for me. Can you direct me to where to go and learn more about DMax I need increment numbers. I sort of kinda need what (rprieto) need.

Thanks for your help in this matter


BLB
Always Grateful

 

[psemianonymous]

I'll give you an example of how the autonumbering can work. This starts with a "tracking number" (internal terminology, it's a project number) and then a 3-digit ID. What it does it gets the "max" value for a particular tracking number, extracts the 3-digit number from the end, adds one to it, and reformats the number back into 3 digits, and returns the newly-generated ID number. Note that in this system I can't have more than 1000 requests per job - actually 999. I expect a maximum of about 100; most jobs average about 20 apiece.


Anyway, if you were to use DMax instead of my query, you would reduce a lot of the code down. Use the help on the DMax function to see exactly how it is used; for the example below, it would be:

DMax("REQUEST_NUMBER","REQUEST_MAIN", _
"REQUEST_NUMBER LIKE """ & trackingNumber & "-###""")

---------

Public Function generateRequestNumber(trackingNumber As String) As String
    Dim strSQL As String
    Dim rs As Recordset
            
    If trackingNumber = "" Then
        generateRequestNumber = ""
        Exit Function
    End If
    
    strSQL = "SELECT Max(REQUEST_NUMBER) AS LARGEST FROM REQUEST_MAIN WHERE REQUEST_NUMBER LIKE '" & _
            trackingNumber & "-###'"
    Set rs = DBEngine(0)(0).OpenRecordset(strSQL, dbOpenSnapshot)
    If rs.BOF And rs.EOF Then
        generateRequestNumber = trackingNumber & "-" & "001"
    Else
        If IsNull(rs!LARGEST) Then
            generateRequestNumber = trackingNumber & "-" & "001"
        Else
            generateRequestNumber = trackingNumber & "-" & CStr(Format((CLng(Right(rs!LARGEST, 3) + 1)), "000"))
        End If
    End If
    
    rs.Close
    Set rs = Nothing
End Function