Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Aug 2001
    Location
    New York, NY
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Adding a new record (Access2000)

    I've got a routine coded which inserts a new record into a table using an ADODB connection. The routine is activated as a Button_Click event handler when the user clicks a button on a form. Now, I'm noticing that, while the record is being added okay, the form doesn't seem to show any indication of the record being added. How do I get my form to actually show the new record that's been added. I tried using a "Forms!FormName.Refresh", but ended up getting the following error message:

    The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.

    Here's my code, which basically adds a new record to the table, gets the correct date and next available job number, and assigns them to the correct fields.


    Private Sub btnAddNewRec_Click()
    On Error GoTo Err_btnAddNewRec_Click

    Dim cnThisConnect As ADODB.Connection
    Dim rcdTblDayWPDocLog As New ADODB.Recordset
    Set cnThisConnect = CurrentProject.Connection

    rcdTblDayWPDocLog.Open "tblDayWPDocLog", cnThisConnect, _
    adOpenKeyset, adLockOptimistic, adCmdTable
    rcdTblDayWPDocLog.AddNew

    Dim NewJobNo As Integer
    Dim NewDate As String
    NewDate = Str(Date)
    NewJobNo = Nz(DMax("[LogJobNo]", "tblDayWPDocLog", "LogDate=#" & Format(NewDate, "mm/dd/yyyy") & "#"), 0) + 1
    rcdTblDayWPDocLog![logDate] = NewDate
    rcdTblDayWPDocLog![logJobNo] = Trim(Str(NewJobNo))
    rcdTblDayWPDocLog.Update
    rcdTblDayWPDocLog.MoveLast

    Exit_btnAddNewRec_Click:
    Exit Sub

    Err_btnAddNewRec_Click:
    MsgBox Err.Description
    Resume Exit_btnAddNewRec_Click

    End Sub

    How can I get the form to automatically show the record that's been added?

    Thanks,
    Steve

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Adding a new record (Access2000)

    The duplicate key or index error probably means that you have a field in the table that isn't being populated properly or that your NewJobNo has already been used by another user. For example, if your DMax is returning a null, then the NewJobNo is always going to be 1, and you can only do that once. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

    You'll need to requery the form to get a new record to show up in its rowsource right away.
    Charlotte

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Adding a new record (Access2000)

    Just so you understand the terminology. When you Refresh a recordset (as when you use Me.Refresh), you are merely getting the most recent data for the record already in the recordset. This is useful when another user might have changed information in a record in your recordset.

    A Requery goes out and reforms the recordset all over again. Use this to get new records added by other users (or yourself in this instance). So, you want to use Me.Requery in your situation.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •