It's creating a cartesian join because you haven't told it how to join
the
two tables (i.e.: what field is in common between them)
You want something like:
strSQL = "INSERT INTO tblCMMIRatings ( " & _
"[Audit ID],[PAID],[Practice],[Title],[Description]) " & _
"SELECT tblAuditDetails.[Audit ID], tblCMMISpecifics.[PA ID], " & _
"tblCMMISpecifics.Practice, tblCMMISpecifics.Title," & _
" tblCMMISpecifics.Description " & _
"FROM tblAuditDetails INNER JOIN tblCMMISpecifics " & _
"ON tblAuditDetails.Field1 = tblCMMISpecifics.Field1 "
"WHERE [Audit ID] = '" & Me.txtAuditID & "' " & _
"AND [PA ID] = '" & varPA1 & "' " & _
"OR [PA ID] = '" & varPA2 & "' " & _
"OR [PA ID] = '" & varPA3 & "';"
If [Audit ID] and/or [PA ID] exist in both tables, you'll need to qualify
the field reference.
As well, you probably want to put some parentheses in your Where clause.
Since And takes precedence over Or, what you've currently got will select
those rows where Audit ID is equal to Me.txtAuditID and PA ID is equal to
varPA1, or where PA ID is equal to varPA2 (regardless of the value of
Audit
ID), or where PA ID is equal to varPA3 (regardless of the value of Audit
ID).
I'm guessing you want
"WHERE [Audit ID] = '" & Me.txtAuditID & "' " & _
"AND ([PA ID] = '" & varPA1 & "' " & _
"OR [PA ID] = '" & varPA2 & "' " & _
"OR [PA ID] = '" & varPA3 & "')"
or
"WHERE [Audit ID] = '" & Me.txtAuditID & "' " & _
"AND [PA ID] IN ('" & varPA1 & "', " & _
"'" & varPA2 & "', " & _
"'" & varPA3 & "')"
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
tigger said:
Doug,
Have another question. Further to the SELECT statement I showed I am
trying
to INSERT INTO one table using data from another table, plus the value
of
a
field on my form (Audit ID, sourced from tblAuditDetails). Of course
it's
not
working ...
My code is thus:
strSQL = "INSERT INTO tblCMMIRatings ([Audit
ID],[PAID],[Practice],[Title],[Description]) " & _
"SELECT tblAuditDetails.[Audit ID], tblCMMISpecifics.[PA
ID],
tblCMMISpecifics.Practice, " & _
"tblCMMISpecifics.Title, tblCMMISpecifics.Description FROM
tblAuditDetails, tblCMMISpecifics " & _
"WHERE [Audit ID] = '" & Me.txtAuditID & "' " & _
"AND [PA ID] = '" & varPA1 & "' " & _
"OR [PA ID] = '" & varPA2 & "' " & _
"OR [PA ID] = '" & varPA3 & "';"
I'm guessing the error is in selected tblAuditDetails.[Audit ID] since
this
is creating a join between the two tables. I don't know the syntax for
adding
a form variable in SQL - can it be done?
Thanks again
:
You haven't instantiated db.
You need something like:
Set db = CurrentDb
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
Hi there, I am trying to create a query with SQL and keep getting
error
91.
My code is below.
1 Dim db As DAO.Database
2 Dim qdf As DAO.QueryDef
3 Dim varPA1 As Variant
4 Dim varPA2 As Variant
5 Dim varPA3 As Variant
6 Dim strSQL As String
7 Set qdf = db.QueryDefs("qryMultiPAs")
If Me.cmbType = "CMMI Assessment" Then
varPA1 = Me.cmbCMMIPA1.Value
varPA2 = Me.cmbCMMIPA2.Value
varPA3 = Me.cmbCMMIPA3.Value
Else
MsgBox "Not CMMI"
Exit Sub
End If
strSQL = "SELECT tblCMMISpecifics.* FROM tblCMMISpecifics " & _
"WHERE CMMIPA1 = '" & varPA1 & "' " & _
"OR CMMIPA2 = '" & varPA2 & "' " & _
"OR CMMIPA3 = '" & varPA3 & "';"
qdf.SQL = strSQL
DoCmd.OpenQuery "qryMultiPAs"
Could anyone tell me where the problem(s) is? The error occurs on
Line
7,
Set qdf = ...
Would appreciate some help
Thanks
