Setting criteria with an Insert Into Statement
Posted on 2011-04-20
I forgot to ask this in my last post. I also need this insert into statement to check that a "WebsiteID" doesn't exist in the destination tables before inserting the current Website ID into them.
With the second insert (tblASGeneralWebsiteReg), I just fixed this by simple changing the property inside the table for Website ID to "No Duplicates". That fixes it. The first table however, has multiple entries of the same WebsiteID when it inserts as it inserts the Website ID against the 24 Site ID's it creates. Setting the table property for WebsiteID will only allow the first SiteID to show. But for each WebsiteID in tblASWebsiteSite, I need the 24 Site ID's with it.
Here's the code I got so far:
Private Sub Command20_Click()
On Error GoTo Err_Command20_Click
Dim stDocName As String
Dim stLinkCriteria As String
Dim strInsert As String
strInsert = "Insert into tblASWebsiteSite (WebsiteID, SiteID)" & vbCrLf
strInsert = strInsert & "select tblWebsite.WebsiteID as WebsiteID, tblASSitesToSubmitTO.SiteID as SiteID" & vbCrLf
strInsert = strInsert & "from tblWebsite, tblASSitesToSubmitTo" & vbCrLf
strInsert = strInsert & "where tblWebsite.WebsiteID = " & Me!WebsiteID
strInsert = "Insert into tblASGeneralWebsiteReg ([WebsiteID])"
strInsert = strInsert & " select tblWebsite.WebsiteID as WebsiteID"
strInsert = strInsert & " from tblWebsite"
strInsert = strInsert & " where tblWebsite.WebsiteID = " & Me!WebsiteID
'' DoCmd.SetWarnings False
'' DoCmd.SetWarnings True
stDocName = "frmASRegistration"
stLinkCriteria = "[WebsiteID]=" & Me![WebsiteID]
DoCmd.OpenForm stDocName, , , stLinkCriteria
Is it possible to put in criteria that checks for even one record of a WebsiteID in tblASWebsiteSite and the code above knows that it needn't do the insert again?