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:
I keep getting a syntax error with this.Code:Dim strSQL As String Dim strTable As String strTable = "tmp_Formula" strSQL = "ALTER TABLE " & StrTable & " ADD CONSTRAINT <em>KeyName</em> PRIMARY KEY(ID);"
My goal is to assign a primary key to the tmp table and use something like the following to delete the duplicates:
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?Code:DELETE * FROM tmp_Formula WHERE ID NOT IN ( SELECT MIN(ID) FROM tmp_Formula GROUP BY tmp_Formula.RawMaterial )