Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
DRM-free e-books 300x50
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old February 22nd, 2007, 02:30 PM
Authorized User
Join Date: Nov 2006
Location: Evergreen, CO, USA.
Posts: 35
Thanks: 0
Thanked 0 Times in 0 Posts
Default Record is Inserted Twice

This is a form with a subform The main form displays rows from the "People" table. The subform displays rows from the Class Registration table, linked by the Email field, which is the primary key for the People table, and part of the key for the Registration table.

The subform contains a control cmdAddRecord1, which copies data from the main form and inserts a row into the Registration table. The VBA code runs OK, but when I use the navigation button to move the main form to another record, it attempts to write a second row to the Registration table, and I get the error message about a duplicate record. With he primary key removed, it actually writes two identical rows.

Here's the VBA for the subform: BTW, cmdDelete works OK
Option Compare Database
Option Explicit

Private Sub cmdAddRecord1_Click()
On Error GoTo Err_cmdAddRecord1_Click
    Dim lngRecordsFound As Long
    Dim txtAddSQL As String ' Define INSERT Statement
    txtAddSQL = "INSERT INTO Registration (ShortName, ClassNumber, Email, First, Last) " & _
        "VALUES (txtShortNameS, txtClassNumberS, txtEmailS, txtFirstS, txtLastS);"

    Dim txtFindSQL As String ' Define SELECT Statement
    txtFindSQL = "SELECT * FROM Registration WHERE ShortName = txtShortNameS AND " & _
    "ClassNumber = txtClassNumberS AND Email = txtEmailS;"
    txtFirstS = Forms![Registration Update Form].txtFirst 'move person data from main form
    txtLastS = Forms![Registration Update Form].txtLast
    txtEmailS = Forms![Registration Update Form].txtEmail
    txtShortNameS = Forms![Registration Update Form].txtShortName
    txtClassNumberS = Forms![Registration Update Form].txtClassNumber
    txtGo = MsgBox("OK to Update?", 1, "Is All Data Correct")

    If txtGo = vbOK Then
        DoCmd.RunSQL txtAddSQL
        txtAction = "Cancel"
    End If

    Exit Sub

    MsgBox Err.Description
    txtAction = "Cancel"
    Resume Exit_cmdAddRecord1_Click

End Sub

Private Sub cmdDelete_Click()
' Delete the Registration Record
' This will delete both records if there are duplicate keys
On Error GoTo Err_cmdDelete_Click

    Dim txtDeleteSQL As String
    txtDeleteSQL = "DELETE FROM Registration " & _
    "WHERE ShortName = txtShortNameS AND ClassNumber = txtClassNumberS " & _
    "AND Email = txtEmailS;"
    DoCmd.RunSQL txtDeleteSQL

    Exit Sub

    MsgBox Err.Description
    Resume Exit_cmdDelete_Click

End Sub

================================================== ===========

Reply With Quote
  #2 (permalink)  
Old February 27th, 2007, 12:28 PM
Authorized User
Join Date: Feb 2007
Location: Wellingborough, Northamptonshire, United Kingdom.
Posts: 18
Thanks: 0
Thanked 0 Times in 0 Posts

I had a similar problem a whistle ago
This was cased by the sub form being based on the main forms table e.g. create a record in the sub also creates it in the main form once the forms been re queried
I would start by checking sub forms data relationship to the main forms data
If they are from un related tables check the relationship in the relationship form perhaps remove it for now until resolved.
I’m no expert hope this helps if not there are more clever people out there best of luck

Reply With Quote

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
How to Edit record right after it was Inserted? aladov ASP.NET 2.0 Professional 4 December 11th, 2007 03:18 PM
How to get last inserted record in a table?? dayaananthanm SQL Server 2005 6 June 11th, 2007 09:15 PM
Return to Inserted Record - FormView rit01 ASP.NET 2.0 Basics 15 January 27th, 2007 12:04 PM
How can I get the last inserted rows? fraijo SQL Server 2000 4 November 23rd, 2006 07:50 AM
Last record inserted ID ADAC Programming VB Databases Basics 1 June 5th, 2006 02:41 PM

All times are GMT -4. The time now is 05:53 PM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.