I have some code which is working okay, except it adds a blank row in my continuos form/subform. It might be normal, adding a blank row for a next record to be added, but I don't want it. There will be no editing or additions made by the user, it's just a glorified list with deeper rows than a list box provides.
Code:
strList1 = ""
For Each item In Me.KeySearch.ItemsSelected
strList1 = strList1 & ",'" & Replace(Me.KeySearch.ItemData(item), "'", "''") & "'"
Next item
If strList1 = "" Then
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * FROM Main2"
DoCmd.SetWarnings True
Forms![Copy of Mainform]!MainSubform.Form.Requery
Exit Sub
End If
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * FROM Main2"
Forms![Copy of Mainform]!MainSubform.Form.Requery
sql2 = "INSERT INTO MAIN2 ( ID1, IDNumber, Firstkeyword, FirstkeywordID )" & _
"SELECT MAIN.ID1, Main.IDNumber, First(Keywords.Keyword) AS firstKeyword, First(Keywords.KeywordID) AS firstKeywordID" & _
" FROM (Keywords INNER JOIN KEYWORD ON Keywords.KeywordID = KEYWORD.KeywordID)" & _
" INNER JOIN (MAIN INNER JOIN JUNCTION1 ON MAIN.ID1 = JUNCTION1.ID1) ON KEYWORD.ID2 = JUNCTION1.ID2" & _
" WHERE Keywords.Keyword IN (" & Mid(strList1, 2) & ")" & _
" GROUP BY MAIN.ID1, Main.IDNumber"
DoCmd.RunSQL sql2
DoCmd.SetWarnings True
Forms![Copy of Mainform]!MainSubform.Form.Requery