I've got some code behind a button that seems to sometimes work and then it also does some random things which sometimes a 'Compact and Repair' will fix it for a short period and then it will start happening again.
I want to insert Website ID, Site ID from tblASWebsiteSite and ArticleID from tblASArticleDetails into a table: tblASSiteSubmitted.
Site ID at the moment contains about 24 entries. A website can have many articles which need to be posted onto each of the 24 sites.
So, if I had say 20 articles from website '1', then if the code worked correctly, it would append:
24 Sites, for each of the 20 article id's (and also corresponding WebsiteID) or 480 records in total.
The last line of code ..."And Not EXISTS..." should be checking that if these records already exist in tblASSiteSubmitted, then it shouldn't append the records and duplicate it again.
I thought it was all working, but when I check tblASSiteSubmitted, I have incorrect entries where I might have ArticleID 3, next to WebsiteID 5 AND WebsiteID 9. Each ArticleID should be a unique entry with a maximum of 24 records (one for each site)
Looking at the code below, is there something I've got wrong?
Private Sub Command32_Click()
On Error GoTo Err_Command32_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmASSubmission"
Dim strInsert As String
strInsert = "Insert into tblASSiteSubmitted (WebsiteID, SiteID, ArticleID)"
strInsert = strInsert & " select tblASWebsiteSite.WebsiteID as WebsiteID, tblASWebsiteSite.SiteID as SiteID, tblASArticleDetails.ArticleID as ArticleID"
strInsert = strInsert & " from tblASWebsiteSite, tblASArticleDetails"
strInsert = strInsert & " where tblASWebsiteSite.WebsiteID = " & Me!WebsiteID
strInsert = strInsert & " AND NOT EXISTS (SELECT NULL AS NotNeeded FROM tblASSiteSubmitted WHERE tblASWebsiteSite.SiteID = tblASSiteSubmitted.SiteID And tblASWebsiteSite.WebsiteID = tblASSiteSubmitted.WebsiteID And tblASArticleDetails.ArticleID = tblASSiteSubmitted.ArticleID)"
stLinkCriteria = "[ArticleID]=" & Me![ArticleID]
DoCmd.OpenForm stDocName, , , stLinkCriteria