We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Setting criteria with an Insert Into Statement

Medium Priority
585 Views
Last Modified: 2012-05-11
Hey guys,

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
    Me.Refresh

 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

    DoCmd.RunSQL strInsert

    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.RunSQL strInsert
   '' DoCmd.SetWarnings True
    stDocName = "frmASRegistration"
   
    stLinkCriteria = "[WebsiteID]=" & Me![WebsiteID]
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command20_Click:
    Exit Sub

Err_Command20_Click:
    MsgBox Err.Description
    Resume Exit_Command20_Click
   
End Sub

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?
Comment
Watch Question

Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Hi Mark,

I put in:

    strInsert = strInsert & "where tblWebsite.WebsiteID = " & Me!WebsiteID AND NOT EXIST (SELECT NULL AS NotNeeded FROM tblASWebsiteSite WHERE tblASWebsiteSite.WebsiteID = tblWebsite.WebsiteID AND tblASWebsiteSite.SiteID = tblASSitesToSubmitTO.SiteID)

but get a Syntax error?

Commented:
You need to put the AND...) into the string. Try this:
 
strInsert = strInsert & "where tblWebsite.WebsiteID = " & Me!WebsiteID
   strInsert = strInsert & " AND NOT EXIST (SELECT NULL AS NotNeeded FROM tblASWebsiteSite WHERE tblASWebsiteSite.WebsiteID = tblWebsite.WebsiteID AND tblASWebsiteSite.SiteID = tblASSitesToSubmitTO.SiteID)"

Open in new window

Author

Commented:
Now I get an error. Here's the code:

 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 = strInsert & "where tblWebsite.WebsiteID = " & Me!WebsiteID
   strInsert = strInsert & " AND NOT EXIST (SELECT NULL AS NotNeeded FROM tblASWebsiteSite WHERE tblASWebsiteSite.WebsiteID = tblWebsite.WebsiteID AND tblASWebsiteSite.SiteID = tblASSitesToSubmitTO.SiteID)"


    DoCmd.RunSQL strInsert

The attached file is a screen shot of the error. Any ideas please?

 Error-Message-VBA-210411.doc
Scott McDaniel (EE MVE )Infotrakker Software
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Commented:
You've duplicated the line:

    strInsert = strInsert & "where tblWebsite.WebsiteID = " & Me!WebsiteID
strInsert = strInsert & "where tblWebsite.WebsiteID = " & Me!WebsiteID

Only include it once.

Author

Commented:
Hey guys,

Made the changes as per your suggestions. Now get this error "Undefined function "EXPRESS" in expression"

Here's the changed code.

Dim strInsert As String
 
strInsert = "Insert into tblASWebsiteSite (WebsiteID, SiteID)"
    strInsert = strInsert & " select tblWebsite.WebsiteID as WebsiteID, tblASSitesToSubmitTO.SiteID as SiteID"
    strInsert = strInsert & " from tblWebsite, tblASSitesToSubmitTo"
    strInsert = strInsert & " where tblWebsite.WebsiteID = " & Me!WebsiteID
   strInsert = strInsert & " AND NOT EXIST (SELECT NULL AS NotNeeded FROM tblASWebsiteSite WHERE tblASWebsiteSite.WebsiteID = tblWebsite.WebsiteID AND tblASWebsiteSite.SiteID = tblASSitesToSubmitTO.SiteID)"

Debug.Print strInsert
    DoCmd.RunSQL strInsert

Any ideas please?

Author

Commented:
Sorry, that should read 'Undefined function "EXIST" in expression'

Commented:
My mistake, it is EXISTS
Scott McDaniel (EE MVE )Infotrakker Software
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
Can you show the output of the Debug.Print statement?

Author

Commented:
Thanks guys. This is excellent!
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.