Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Query adding extra row

Status
Not open for further replies.

fileman1

Technical User
Feb 7, 2013
152
GB
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
 
Sorry, found it. Form properties, allow additions, NO.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top