[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 548
  • Last Modified:

Setting criteria with an Insert Into Statement

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?
0
jammin140900
Asked:
jammin140900
  • 5
  • 4
  • 2
2 Solutions
 
MarkHurdCommented:
So you want tblASWebsiteSite (WebsiteID, SiteID) to be a unique pair? You can set that as a unique index or even a primary key. But also you can add to the existing where clause:

AND NOT EXIST (SELECT NULL AS NotNeeded FROM tblASWebsiteSite tAWS WHERE tAWS.WebsiteID = tblWebsite.WebsiteID AND tAWS.SiteID = tblASSitesToSubmitTO.SiteID)
0
 
jammin140900Author 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?
0
 
MarkHurdCommented:
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

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
jammin140900Author 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
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Remove the "& vbCrLf" from your code. and make sure that your strInsert statements are being properly formed, with spaces in the correct areas:

strInsert = "Insert into tblASWebsiteSite (WebsiteID, SiteID)"
strInsert = strInsert & " select tblWebsite.WebsiteID as WebsiteID, tblASSitesToSubmitTO.SiteID as SiteID"
                                      ^----------- Notice the space here
strInsert = strInsert & " from tblWebsite, tblASSitesToSubmitTo"
etc etc

Access expects a properly formed SQL statement, and yours doesn't have spaces in the correct location. vbCrLf introduces a linefeed/character return into the string, which is not needed for SQL statements and can cause you to have a malformed statement (in many cases Access can figure out the syntax, but there's just no reason to have them in your SQL statements).

If you still have issues, please post the fully formed strInsert value. To do that, add this line just before your RunSQL line:

Debug.Print strInsert

This will print the strInsert statement to the Immediate window (in the VBA editor) and you can copy/paste that here.
0
 
MarkHurdCommented:
You've duplicated the line:

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

Only include it once.
0
 
jammin140900Author 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?
0
 
jammin140900Author Commented:
Sorry, that should read 'Undefined function "EXIST" in expression'
0
 
MarkHurdCommented:
My mistake, it is EXISTS
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Can you show the output of the Debug.Print statement?
0
 
jammin140900Author Commented:
Thanks guys. This is excellent!
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

  • 5
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now