Append query with multiple joins

[Guest]

Hi there, I'm trying to append a table using data from two tables that can be
joined in two places.

What I have is tblAuditDetails from which I want to pick the Audit ID. I
then have tblCMMISpecifics, from which I want to pick the PA ID (and
associated details) when it matches EITHER the value of cmbCMMIPA1 OR
cmbCMMIPA2 on my form. The source for both these combos comes from
tblAuditDetails.

My code is as follows:

Private Sub MultiPAs() 'Runs if multiple process areas on evaluation

On Error Resume Next

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim stLinkCriteria As String
Dim stDocName As String
Dim strPA1 As String
Dim strPA2 As String
Dim strSQL As String
Set db = CurrentDb
Set qdf = db.CreateQueryDef("qryMultiPAs")

'Set link criteria to open CMMI ratings form
stLinkCriteria = "[Audit ID] =" & txtAuditID
stDocName = "frmCMMIRatings"

'Set variable based on evaluation type
If Me.cmbType = "CMMI Assessment" Then
strPA1 = Me.cmbCMMIPA1.Value
strPA2 = Me.cmbCMMIPA2.Value
End If

'append PA1
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 RIGHT JOIN tblCMMISpecifics " & _
"ON tblAuditDetails.CMMIPA1 = tblCMMISpecifics.[PA ID] " & _
"WHERE [Audit ID] = " & Me.txtAuditID & " " & _
"AND ([PA ID] = '" & strPA1 & "')"

Debug.Print strSQL
qdf.SQL = strSQL

DoCmd.OpenQuery "qryMultiPAs"
DoCmd.OpenForm FormName:=stDocName, WhereCondition:=stLinkCriteria,
DataMode:=acFormEdit

Set qdf = Nothing
strSQL = ""
DoCmd.DeleteObject acQuery, "qryMultiPAs"

'then append PA2
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 RIGHT JOIN tblCMMISpecifics " & _
"ON tblAuditDetails.CMMIPA2 = tblCMMISpecifics.[PA ID] " & _
"WHERE [Audit ID] = " & Me.txtAuditID & " " & _
"AND ([PA ID] = '" & strPA2 & "')"

Debug.Print strSQL
qdf.SQL = strSQL

DoCmd.OpenQuery "qryMultiPAs"
DoCmd.OpenForm FormName:=stDocName, WhereCondition:=stLinkCriteria,
DataMode:=acFormEdit

Set qdf = Nothing
'DoCmd.DeleteObject acQuery, "qryMultiPAs"

Exit_MultiPAs:
Exit Sub

Err_MultiPAs:
MsgBox "Please contact the database administrator", vbCritical, "Error"
Resume Exit_MultiPAs

End Sub

--------------------

I can get the data relating to the value of cmbCMMIPA1 but not the value of
cmbCMMIPA2. However, in the immediate window, the correct values are being
passed to strSQL.

Can anyone tell me where I am going wrong? And if there is a (much) easier
way of doing this?

Many thanks in advance

 

[Jeff Boyce]

I'm not sure I understand the business need...

If you can create a query (joining your tables) and returning the values you
wish to see, why do you also need a table to which you append these values?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi there, I'm trying to append a table using data from two tables that can
be
joined in two places.

What I have is tblAuditDetails from which I want to pick the Audit ID. I
then have tblCMMISpecifics, from which I want to pick the PA ID (and
associated details) when it matches EITHER the value of cmbCMMIPA1 OR
cmbCMMIPA2 on my form. The source for both these combos comes from
tblAuditDetails.

My code is as follows:

Private Sub MultiPAs() 'Runs if multiple process areas on evaluation

On Error Resume Next

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim stLinkCriteria As String
Dim stDocName As String
Dim strPA1 As String
Dim strPA2 As String
Dim strSQL As String
Set db = CurrentDb
Set qdf = db.CreateQueryDef("qryMultiPAs")

'Set link criteria to open CMMI ratings form
stLinkCriteria = "[Audit ID] =" & txtAuditID
stDocName = "frmCMMIRatings"

'Set variable based on evaluation type
If Me.cmbType = "CMMI Assessment" Then
strPA1 = Me.cmbCMMIPA1.Value
strPA2 = Me.cmbCMMIPA2.Value
End If

'append PA1
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 RIGHT JOIN tblCMMISpecifics " & _
"ON tblAuditDetails.CMMIPA1 = tblCMMISpecifics.[PA ID] " &
_
"WHERE [Audit ID] = " & Me.txtAuditID & " " & _
"AND ([PA ID] = '" & strPA1 & "')"

Debug.Print strSQL
qdf.SQL = strSQL

DoCmd.OpenQuery "qryMultiPAs"
DoCmd.OpenForm FormName:=stDocName, WhereCondition:=stLinkCriteria,
DataMode:=acFormEdit

Set qdf = Nothing
strSQL = ""
DoCmd.DeleteObject acQuery, "qryMultiPAs"

'then append PA2
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 RIGHT JOIN tblCMMISpecifics " & _
"ON tblAuditDetails.CMMIPA2 = tblCMMISpecifics.[PA ID] " &
_
"WHERE [Audit ID] = " & Me.txtAuditID & " " & _
"AND ([PA ID] = '" & strPA2 & "')"

Debug.Print strSQL
qdf.SQL = strSQL

DoCmd.OpenQuery "qryMultiPAs"
DoCmd.OpenForm FormName:=stDocName, WhereCondition:=stLinkCriteria,
DataMode:=acFormEdit

Set qdf = Nothing
'DoCmd.DeleteObject acQuery, "qryMultiPAs"

Exit_MultiPAs:
Exit Sub

Err_MultiPAs:
MsgBox "Please contact the database administrator", vbCritical, "Error"
Resume Exit_MultiPAs

End Sub

--------------------

I can get the data relating to the value of cmbCMMIPA1 but not the value
of
cmbCMMIPA2. However, in the immediate window, the correct values are being
passed to strSQL.

Can anyone tell me where I am going wrong? And if there is a (much) easier
way of doing this?

Many thanks in advance