Results 1 to 3 of 3
  1. #1
    lfernandogb is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2017
    Posts
    1

    A macro I'm running is duplicating the total of records in a table

    Hi, in the current job im having we're using a form to record data into a table by clicking a button to which I have associated a macro but the thing is that when i click the button it actually does record the field i want but it also duplicates the amount of records that my table has, so for example if it originally has 12,000 records after i click the button it updates the table to now have 24,000 records and the new 12,000 are just empty with no data except for the data i input in the form.



    Excuse my english, im not a native speaker, i hope i make my point clear.

    The macro im using is this one:

    Code:
    Private Sub Command5_Click()
    
    Dim SQL As String
    
    
        If Not IsNull(PAGO) Then
    
    
        SQL = "INSERT INTO OCREA48 (PAGO) SELECT forms.Form1.PAGO FROM OCREA48;"
        
            DoCmd.RunSQL SQL
            MsgBox "Record Saved", vbInformation
    
    
        End If
    End Sub
    Ocrea48: my original table
    PAGO: is the field I'm affecting with the form and the button

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    you dont need any code to do this.
    make a query with this Sql then run it in the macro:
    openquery "qaMyQuery"

  3. #3
    Kudos is offline Novice
    Windows 8 Access 2013 32bit
    Join Date
    Mar 2017
    Location
    Toronto, ON
    Posts
    29
    The SQL string you've created is an Insert query which tells the database to create records. It looks like you are selecting your entire table and then telling the database to create records based on that selection, so you will end up with exactly double the amount of records you started with.

    Can you outline exactly what you want this button to do? I see you want it to record data into a table, but that is a little unclear. Do you want it to create records, or update a single field in existing records? Also, can you give an example of the data that you are storing in the PAGO field?

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 06-12-2015, 12:03 AM
  2. Export To Excel Through Macro is Duplicating Records
    By raykdogs in forum Import/Export Data
    Replies: 2
    Last Post: 05-05-2015, 04:30 AM
  3. Replies: 2
    Last Post: 05-19-2011, 04:53 PM
  4. Replies: 4
    Last Post: 11-05-2010, 04:56 AM
  5. Replies: 2
    Last Post: 09-30-2009, 09:40 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums