Find Last Record and Add new keyed record

causualuser · February 5th, 2009, 08:42 PM

I want to write VBA code (Access 2007) triggered from a double click Event on a field in a Form. The table is an accdb format.
I want to find the last key in the table displayed with this Form and then add 1 to that key and write a new blank record with that key. This key is the Primary Key field. I want to refresh the Form so that the new blank record is the current record and is displayed in the Form.
I can get the code to write the new record with the proper key number but I have to click the last record key in the Form before I double click the event and I have to click the Refresh All icon in the ribbon to after the event.
I want the code to do all of the process without requiring the user to click items before and after the event.
I am new to VBA coding but have used Access 2002 some. I have purchased 4 books and still canât figure it out.
Thanks for any help.
This is my first attempt at forums.

mmcdonal · February 6th, 2009, 03:55 PM

The easiest thing to do is to make the field an autonumber field since that does everything you asked for automatically, and the user doesn't need to provide any input or see the PK.

Can you post the code you are using if you still want to remove this automation?

causualuser · February 6th, 2009, 08:46 PM

Quote:

Originally Posted by mmcdonal

The easiest thing to do is to make the field an autonumber field since that does everything you asked for automatically, and the user doesn't need to provide any input or see the PK.

Can you post the code you are using if you still want to remove this automation?

I tried using autonumber but it doesn't work for various reasons.

I am including code with things I have tried with them commented out. It moves the form to the last record but doesn't set the current record to the last record. It does add the new record OK but also doesn't refresh the record in the form.

Code:

Private Sub SKey_DblClick(Cancel As Integer)

    Dim savekey As Integer
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("SowerCensus", dbOpenTable)
        ' DoCmd.GoToRecord acDataForm, "frmSower", acLast
    DoCmd.RunCommand acCmdRecordsGoToLast
        ' DoCmd.RunCommand acCmdLastPosition
        ' DoCmd.RunCommand acCmdRefreshData
        ' rst.Bookmark = rst.LastModified   ' Go to new record
    savekey = SKey + 1
    Debug.Print "Next SOWER Key is: " & savekey
    With rst
       .AddNew             ' Add new record
       !SKey = savekey     ' Set fields
       !LastName = "New Last Name"
       .Update             ' Save changes.
        ' .Bookmark = rst.LastModified   ' Go to new record
        
       Debug.Print "Current title: "
    End With
    DoCmd.RunCommand acCmdRefresh
    rst.Close
    dbs.Close
End Sub

Thanks for your help.

mmcdonal · February 9th, 2009, 08:24 AM

I would suggest using the Button Wizard, record Operations, adding the functions you want that way, and then scavenge the code if you want to use it on your buttons. Like, add a record is:

DoCmd.GoToRecord , , acNewRec

So I think the solution is there.

I can't tell you the number of times I have had to sort out someone's application because they didn't use autonumber and there were duplicate keys. Make sure you set the field to Index Yes (No Duplicates) and do the necessary error handling.

Did that help?