docmd.OpenQuery produces error 2001

[ultimateguy]

Totally new to Access VBA. I have the following code... basically I am creating a query called codeNotUsedAt and I just want to open it in a table to see the results.

Dim qryQuery1 As DAO.QueryDef
Dim qryQuery2 As DAO.QueryDef
Dim qryNotUsedAt As DAO.QueryDef

Set qryQuery1 = dbTrivia.CreateQueryDef("", "SELECT CompletedTrivias.CustomerID, UsedQuestions.QuestionID " _
& "FROM CompletedTrivias RIGHT JOIN UsedQuestions ON CompletedTrivias.ID = UsedQuestions.TriviaID;")

Set qryQuery2 = dbTrivia.CreateQueryDef("", "SELECT Clients.ID, Questions.ID " _
& "FROM Clients, Questions;")

Set qryNotUsedAt = dbTrivia.CreateQueryDef("codeNotUsedAt", "SELECT qryQuery2.Clients.ID, " _
& "qryQuery2.Questions.ID, qryQuery1.QuestionID FROM qryQuery2 LEFT JOIN qryQuery1 ON " _
& "(qryQuery2.Questions.ID = qryQuery1.QuestionID) AND " _
& "(qryQuery2.Clients.ID = qryQuery1.CustomerID) WHERE " _
& "(((qryQuery2.Clients.ID)=" & Me.cboClientName.Value & ") AND " _
& "((qryQuery1.QuestionID) Is Null));")

DoCmd.OpenQuery "codeNotUsedAt"

On the DoCmd.OpenQuery line I get the error code 2001 one saying "You cancelled the previous operation."

Why is this error being thrown? Also I am a bit confused about the difference between qryNotUsedAt and codeNotUsedAt in this code, what's the difference?

[June7]

Why are you using VBA to create query objects? Why not use Access query builder? The query object can have a dynamic parameter that references the combobox.

Or better, bind the query to a form or report and apply the filter to the form or report.

The difference is one (qryNotUsedAt) is an object variable and the other (codeNotUsedAt) is the name assigned to the query that is created.

Query names are not supplied for the first two. That is possibly the source of error.

[ultimateguy]

Wow, you're right. I've been going about this the wrong way. I actually have this query constructed already in the query builder, but one of the criterion references the combo box in another form because that form also makes use of the query. Should I just duplicate the query under a different name and change the criterion?

I'm working on some code for a trivia database that would allow me to pull x number of random questions from a Questions table that haven't been used at a certain establishment. Hence I need to pull some random records from the results of this query. I thought I would have to code the query as well but I realize now that is not the case.

[June7]

Code that modifies db structure should be avoided.

Build your query.

[John_G]

Should I just duplicate the query under a different name and change the criterion?

That is probably the easiest, if the query is used by two forms.

If the query is not particularly complex, and you are using it to create a recordset in VBA, you could also just use the SQL right in the VBA, without referencing the stored query definition.

John