Results 1 to 5 of 5
  1. #1
    lccrews is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Location
    Los Angeles
    Posts
    194

    VBA To Add Primary Key To Temporary Table

    Hi,


    I have a qry built to pull information from two tables and make a temporary table from that for users to make changes to. One of the buttons on the form that houses this subform (tmp table) runs a code to add sub-ingredients associated to the raw material they just added.
    RawMaterial
    130199
    < --130199S1

    However, even when the sub-ingredient is added, the code will add another, causing duplicates:
    RawMaterial
    130199
    130199S1
    < --130199S1

    My current approach is to find a way to find and delete the duplicate entries simultaneously when the code runs. Unfortunately, as a tmp table there is no primary key to make this easy. I've looked for a way online to add a primary key but either I don't understand what I'm reading or it doesn't work. Here is one that I have tried:
    Code:
    Dim strSQL As String   Dim strTable As String
       strTable = "tmp_Formula"
       strSQL = "ALTER TABLE " &amp; StrTable &amp; " ADD CONSTRAINT <em>KeyName</em> PRIMARY KEY(ID);"
    I keep getting a syntax error with this.

    My goal is to assign a primary key to the tmp table and use something like the following to delete the duplicates:
    Code:
    DELETE * FROM tmp_Formula
    WHERE ID NOT IN
    (
    SELECT MIN(ID)
    FROM tmp_Formula
    GROUP BY tmp_Formula.RawMaterial
    )
    I more than likely have made a mistake in that last bit of code because I'm new to VBA. IF there is an issue please raise the flag and let me know. I really appreciate any help. Even as I proofread this, the idea came to mind to modify the code adding sub-ingredients to check to see if the sub-ingredient exists or not already. However I have no clue how to do that. Wouldn't that be the better solution?

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    not sure if it is the way you have copy/pasted but you have html mixed up in the code

    also you need to add the field - you havent said whether you have or not

    this link may help

    https://www.w3schools.com/sql/sql_constraints.asp

  3. #3
    lccrews is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Location
    Los Angeles
    Posts
    194
    I think it would be better to avoid adding duplicates in the first place. Wouldn't you agree? I wrote this SQL from a query build and added in the bit: '" & RawMaterial & "'

    However I'm getting error: Too few parameters. Expected 3.
    Code:
        sSQL = "INSERT INTO tmp_Formula ( BP, Item, BillType, RawMaterial, UoM ) " _         
             & "SELECT [Forms]![frm_Formulation]![BP] AS BP, [Forms]![frm_Formulation]![ITEM] AS Item, " _
             & "[Forms]![frm_Formulation]![BILL TYPE] AS BillType, tbl_RawMaterial.RawMaterial, '' AS UoM " _
             & "FROM tbl_RawMaterial, qry_CurrentFormula_Pt3 " _
             & "WHERE (((tbl_RawMaterial.RawMaterial) Like '" & RawMaterial & "' & 'S*'));"
        CurrentDb.Execute sSQL, dbFailOnError
    When I try to debug, the bolded portion of the code is highlighted only.

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    this is a different question to your original post - is that now resolved? If so, this next question should really be in its own thread. However to answer it, you have your quotes mixed up. try

    "WHERE (((tbl_RawMaterial.RawMaterial) Like '" & RawMaterial & "S*'));"

    As a development technique, it is always worth putting a debug.print sSQL once the string is constructed so you can see what it looks like before committing to execution. Once happy it is working as required it can removed or commented out

    I think it would be better to avoid adding duplicates in the first place. Wouldn't you agree?
    I do, but don't see anything in your code to prevent that, primary keys identify a record, they don't prevent duplicates of the data so presumably you have set the indexes of the table to not allow duplicates

  5. #5
    lccrews is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Location
    Los Angeles
    Posts
    194
    I'm sorry about the question sort of changing. The orignal idea was to handle duplicates as they arise, but now I think it would be better to avoid duplicates in the first place. The code above is a start to an end goal of this: When the user types in a raw material, VBA automatically takes the raw material and finds like raw material with 'S*' affixed to it and then adds those. The users won't be adding the same ingredients twice. This is just to stop the system from creating duplicates. I need to make additional mods to the SQL/VBA because I was missing one thing in my last post. I'll post a URL to my new post to avoid confusion.

    https://www.accessforums.net/showthr...887#post398887
    Last edited by lccrews; 05-25-2018 at 12:52 PM. Reason: OP question changed.

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

Similar Threads

  1. Replies: 5
    Last Post: 01-10-2018, 04:16 PM
  2. Replies: 6
    Last Post: 11-22-2017, 01:05 AM
  3. Create a temporary Table from Query
    By WickidWe in forum Queries
    Replies: 1
    Last Post: 12-11-2013, 07:19 AM
  4. Update Master Table with Temporary Table
    By kagoodwin13 in forum Programming
    Replies: 2
    Last Post: 10-15-2013, 11:59 AM
  5. Replies: 11
    Last Post: 04-04-2012, 05:48 PM

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