primary key autonumbering

[ramasterre]

I am working in an Access database that I did not create but I am now in charge of updating some of the stuff we do with it. I am wondering, one of the tables has a primary key that is basically a 'case' number made of the date and a 5 digit number. It basically takes the date and auto generates the primary keys. This works fine for the userform that populates the table. However, I want to (from time to time) be able to dump data from an excel document into the table instead of using the form. Is there a way to get the primary key to continue to autopopulate without using the user form?

any help would really be appreciated.

 

[Joe4]

Here is an idea that may or may not help.

I would look at importing it into a temporary table. Then use and Update Query to populate the primary values you need, and then use and Append Query to write the information back to your main table.

You could probably also do all this with some high level VBA as you are importing the data all in one step, but I am not quite that advanced in VBA yet.

 

[ramasterre]

hmm.. sound like i just may have to go with that. Thanks for the suggestion.


Does anyone know of any other way?

 

[Andrew Fergus]

Hi
I presume there is a macro or VBA event that runs when either a record is saved or the form is closed. Can you determine how the unique values are populated from within the form? If so, can you replicate that process in another macro as part of the import process?

If you aren't having much luck with an automated process then what is the format and default value of this particular field in the table design screen? You might also want to check the default value in the form design screen.
Andrew

 

[ramasterre]

I have located the macro for the autonumbering but quite frankly i dont understand it.

Function CreateStamp(f As Form)
    ' This function will take the name of a table and the name of the
    ' field that is the primary key.  It will increment a counter, then
    ' assign a unique key
    On Error Resume Next
    Dim db As Database, rst As Recordset
    Dim strReturn As String, intYear As String
    Dim intMonth As String, intDay As String
    Dim intI As Integer
    Dim dtRightNow As Date
    
    strReturn = ""
    Set db = CurrentDb()
    Set rst = db.OpenRecordset("tblCaseCount")
    
    'Move to the last record in tblCase Count and get value
    rst.MoveLast
    intI = rst!Count.Value
    
    dtRightNow = Now()
    intYear = Format(dtRightNow, "yyyy")
    intMonth = Format(dtRightNow, "mm")
    intDay = Format(dtRightNow, "dd")
    strReturn = intYear & intMonth & intDay & Trim(CStr(intI))
    
    With rst
        .AddNew
        !CaseType = f.CASE_TYPE
        .Update
    End With
    rst.MoveLast
    intI = rst!Count.Value
    strReturn = intYear & intMonth & intDay & Trim(CStr(intI))
    f.CASE_NO.Value = strReturn
    
    'Close and reset objects
    db.CLOSE
    Set db = Nothing
    rst.CLOSE
    
End Function

can i do anything with this? or is my only choice to rig somedthing with an append query ?

thanks for the help!