qdf Object Variable Error 91 problem

T

tigger

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
 
T

tigger

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
 
D

Douglas J. Steele

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

Douglas J. Steele said:
You haven't instantiated db.

You need something like:

Set db = CurrentDb
Click to expand...
Click to expand...
 
T

tigger

Hi Doug,

I suppose the link is on the PA ID (varPA1/2/3), which is common to both
tblAuditDetails and tblCMMISpecifics. tblCMMISpecifics is a repository of
constant data that never changes (i.e. a lookup table), whereas
tblAuditDetails is the source for my main form.

What I want the query to do is populate tblCMMIRatings (which sources a
subform related to the main form by AuditID) with the Audit ID, taken from
the field Me.txtAuditID on my form, collect the data from tblCMMISpecifics
based on any of PA1, PA2 or PA3 (which are three fields on my main form) and
link the data - i.e. PAID by Audit ID.

Should I then create the INNER JOIN on the PAID field?

Thanks for your patience!

Douglas J. Steele said:
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

Douglas J. Steele said:
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
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J. Steele

Sorry. I can't see your tables: you can. Try it and see!

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


tigger said:
Hi Doug,

I suppose the link is on the PA ID (varPA1/2/3), which is common to both
tblAuditDetails and tblCMMISpecifics. tblCMMISpecifics is a repository of
constant data that never changes (i.e. a lookup table), whereas
tblAuditDetails is the source for my main form.

What I want the query to do is populate tblCMMIRatings (which sources a
subform related to the main form by AuditID) with the Audit ID, taken from
the field Me.txtAuditID on my form, collect the data from tblCMMISpecifics
based on any of PA1, PA2 or PA3 (which are three fields on my main form)
and
link the data - i.e. PAID by Audit ID.

Should I then create the INNER JOIN on the PAID field?

Thanks for your patience!

Douglas J. Steele said:
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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Append query with multiple joins 1
Between Dates on Combo Form 10
Compile Error Help 1
Run time error 2001 5
Multi-Select List Box Code Problem 5
HOW TO Simplify following code 4
I manipulate a record that I've put into a listbox (vba) 3
Problems with SQL in vba code 2

Top