Runtime error 2001

[ThriftyFinanceGirl]

I had this code working yesterday, now I get this runtime error 2001 "You
cancelled the previous operation."

Pops up when it hits the SQL, but it was working yesterday. Grrrrr! Any
help?

--------------------------------
Private Sub psBuildSQL()

Dim strWhere As String

strWhere = "WHERE ([State] = """ & Me.cmbFilterState & """) "

If Len(Nz(Me.cmbCounty, "")) <> 0 Then
strWhere = strWhere & " AND ([County] = """ & Me.cmbCounty & """)"
End If

If Len(Nz(Me.cmbCity, "")) <> 0 Then
strWhere = strWhere & " AND ([City] = """ & Me.cmbCity & """)"
End If

If Len(Nz(Me.cmbTaxType, "")) <> 0 Then
strWhere = strWhere & " AND ([TaxTypeID] = """ & Me.cmbTaxType & """)"
End If


Me.qryWorkWithTax_subform.Form.RecordSource = _
"SELECT Location.BusUnit, Location.Description, Location.APOCode, "
& _
"TaxRatesBases.TaxName, TaxRatesBases.TaxPercentage,
TaxRatesBases.TaxEffectiveDate, " & _
"TaxRatesBases.TaxEndDate, TaxRatesBases.TaxCollPeriod,
TaxRatesBases.TaxPeriodQEnds, " & _
"Location.State, Location.City, Location.County, TaxTypes.TaxType,
TaxTypes.TaxTypeID " & _
"FROM TaxTypes INNER JOIN (Location INNER JOIN TaxRatesBases ON
Location.BusUnit = TaxRatesBases.BusUnit) ON TaxTypes.TaxTypeID =
TaxRatesBases.TaxName " & _
"" & strWhere & " ORDER BY Location.BusUnit;"

Me.qryWorkWithTax_subform.Form.Requery

 

[ThriftyFinanceGirl]

Yes, it is the same data... and I can run the query with the chosen
parameters and it returns records. Also, I have done a step debug. The only
error it stops on is the sql statement, saying runtime error 2001 "You
cancelled the previous operation.". This is beyond frustrating!

Is it the same data? If not, try again with same data. If you haven't changed
the code then has to be data problem. Though don't know why it would generate
that particular message. Step debug, see what values are in the variables.

I had this code working yesterday, now I get this runtime error 2001 "You
cancelled the previous operation."

Pops up when it hits the SQL, but it was working yesterday. Grrrrr! Any
help?

--------------------------------
Private Sub psBuildSQL()

Dim strWhere As String

strWhere = "WHERE ([State] = """ & Me.cmbFilterState & """) "

If Len(Nz(Me.cmbCounty, "")) <> 0 Then
strWhere = strWhere & " AND ([County] = """ & Me.cmbCounty & """)"
End If

If Len(Nz(Me.cmbCity, "")) <> 0 Then
strWhere = strWhere & " AND ([City] = """ & Me.cmbCity & """)"
End If

If Len(Nz(Me.cmbTaxType, "")) <> 0 Then
strWhere = strWhere & " AND ([TaxTypeID] = """ & Me.cmbTaxType & """)"
End If


Me.qryWorkWithTax_subform.Form.RecordSource = _
"SELECT Location.BusUnit, Location.Description, Location.APOCode, "
& _
"TaxRatesBases.TaxName, TaxRatesBases.TaxPercentage,
TaxRatesBases.TaxEffectiveDate, " & _
"TaxRatesBases.TaxEndDate, TaxRatesBases.TaxCollPeriod,
TaxRatesBases.TaxPeriodQEnds, " & _
"Location.State, Location.City, Location.County, TaxTypes.TaxType,
TaxTypes.TaxTypeID " & _
"FROM TaxTypes INNER JOIN (Location INNER JOIN TaxRatesBases ON
Location.BusUnit = TaxRatesBases.BusUnit) ON TaxTypes.TaxTypeID =
TaxRatesBases.TaxName " & _
"" & strWhere & " ORDER BY Location.BusUnit;"

Me.qryWorkWithTax_subform.Form.Requery

 

[ThriftyFinanceGirl]

Yes the join is actually correct, the name of that one field "TaxName" is a
holdover from naming by a previous worker. However they are both number
fields and the join is correct. Also, I tried to take out that quote as you
suggested, and I got syntax error until I put it back....

It looks like right before you append your WHERE clause you concatenate a
single quote onto your SELECT (that's what it looks like anyway, hard to tell
when quotes are squished together). What is that for and should that really
be there? Are you sure you (or someone) didn't make some minor changes to
the SQL or one of the tables? I think this error is usually caused by an SQL
error. Are county, city and tax type all text fields? Is it possible that in
all the fields used in the join, one of the tables has the field defined as a
different type? e.g. you have

TaxTypes.TaxTypeID = TaxRatesBases.TaxName

in the JOIN. Are these fields the right ones to be joining?

I had this code working yesterday, now I get this runtime error 2001 "You
cancelled the previous operation."

Pops up when it hits the SQL, but it was working yesterday. Grrrrr! Any
help?

--------------------------------
Private Sub psBuildSQL()

Dim strWhere As String

strWhere = "WHERE ([State] = """ & Me.cmbFilterState & """) "

If Len(Nz(Me.cmbCounty, "")) <> 0 Then
strWhere = strWhere & " AND ([County] = """ & Me.cmbCounty & """)"
End If

If Len(Nz(Me.cmbCity, "")) <> 0 Then
strWhere = strWhere & " AND ([City] = """ & Me.cmbCity & """)"
End If

If Len(Nz(Me.cmbTaxType, "")) <> 0 Then
strWhere = strWhere & " AND ([TaxTypeID] = """ & Me.cmbTaxType & """)"
End If


Me.qryWorkWithTax_subform.Form.RecordSource = _
"SELECT Location.BusUnit, Location.Description, Location.APOCode, "
& _
"TaxRatesBases.TaxName, TaxRatesBases.TaxPercentage,
TaxRatesBases.TaxEffectiveDate, " & _
"TaxRatesBases.TaxEndDate, TaxRatesBases.TaxCollPeriod,
TaxRatesBases.TaxPeriodQEnds, " & _
"Location.State, Location.City, Location.County, TaxTypes.TaxType,
TaxTypes.TaxTypeID " & _
"FROM TaxTypes INNER JOIN (Location INNER JOIN TaxRatesBases ON
Location.BusUnit = TaxRatesBases.BusUnit) ON TaxTypes.TaxTypeID =
TaxRatesBases.TaxName " & _
"" & strWhere & " ORDER BY Location.BusUnit;"

Me.qryWorkWithTax_subform.Form.Requery

 

[ThriftyFinanceGirl]

Well, I moved the piece that is collecting the taxtype data to the front of
the Where clause (right after the State) so that I could see as it was
running that the value was being collected properly.

It does show correct State=GA, TaxType = 16, City = College Park
Now, I can get the query to run (via an Access query) with these criterion
however I cannot seem to write the query via SQL and get it to run. My SQL
is not the absolute best it could be but I do know how to write it, yet I
can't get the syntax correct to test it. I'm at my wits end, as not only do
I have to use this form to filter, but then I need to be able to use the
generated SQL statement to create an update query and append query as well!

I still think it's something in the SQL. Could it be something in the WHERE
clause? This could explain why it worked one time and not another. Depending
on the conditions that were true when the code was executed. you'll get a
different WHERE clause. If you know the exact conditions that were true when
you got the error, try executing that SQL (open a recordset with it or
execute it via DoCmd) and see if you get some sort of SQL-related error. If
you can execute that exact same SQL successfully then it's something else.

Yes the join is actually correct, the name of that one field "TaxName" is a
holdover from naming by a previous worker. However they are both number
fields and the join is correct. Also, I tried to take out that quote as you
suggested, and I got syntax error until I put it back....

It looks like right before you append your WHERE clause you concatenate a
single quote onto your SELECT (that's what it looks like anyway, hard to tell

[quoted text clipped - 50 lines]

Me.qryWorkWithTax_subform.Form.Requery

 

[ThriftyFinanceGirl]

Could the problem here be that the string being created is putting quotes
around an integer value? The last item that we are trying to use is the
taxtype which is a number. The other values are true strings.

However, if I try to take any quotes out of the LEN line, then it doesn't
work. What do you think?

I still think it's something in the SQL. Could it be something in the WHERE
clause? This could explain why it worked one time and not another. Depending
on the conditions that were true when the code was executed. you'll get a
different WHERE clause. If you know the exact conditions that were true when
you got the error, try executing that SQL (open a recordset with it or
execute it via DoCmd) and see if you get some sort of SQL-related error. If
you can execute that exact same SQL successfully then it's something else.

Yes the join is actually correct, the name of that one field "TaxName" is a
holdover from naming by a previous worker. However they are both number
fields and the join is correct. Also, I tried to take out that quote as you
suggested, and I got syntax error until I put it back....

It looks like right before you append your WHERE clause you concatenate a
single quote onto your SELECT (that's what it looks like anyway, hard to tell

[quoted text clipped - 50 lines]

Me.qryWorkWithTax_subform.Form.Requery

 

[ThriftyFinanceGirl]

Yep, I'm glad that we finally got to the bottom of that! I could see that it
was getting the number properly, didn't think about the quotes! Yay! Thanks
for the help!

Yes,that would cause a problem. In that case, for your where clause you need:

If Len(Nz(Me.cmbTaxType, "")) <> 0 Then
strWhere = strWhere & " AND ([TaxTypeID] = & Me.cmbTaxType & ")"
End If

The 'If' itself isn't causing the problem. If it was, your code would blow up
on the 'If'. it's that section of the WHERE clause that you're building that
is the problem. Assuming the rest of the SQL is OK that should fix it. The
error message you get is a bit misleading. If you tried to execute that SQL
directly, i.e. using it to open a recordset, the error message would let you
know there was a problem with the SQL. But because the problem is in trying
to set a RowSource property, it doesn't directly tell you that there is an
SQL error.

When building SQL in VBA and comparing fields, text field values are the only
ones that should need to have quotes around them.

Could the problem here be that the string being created is putting quotes
around an integer value? The last item that we are trying to use is the
taxtype which is a number. The other values are true strings.

However, if I try to take any quotes out of the LEN line, then it doesn't
work. What do you think?

I still think it's something in the SQL. Could it be something in the WHERE
clause? This could explain why it worked one time and not another. Depending

[quoted text clipped - 14 lines]

Me.qryWorkWithTax_subform.Form.Requery

 

[ThriftyFinanceGirl]

;-) Have a great week!

Whew! And whoops, too. When I mentioned the JOIN clause and the fields being
the right type earlier on, I meant to also mention the fields you had in the
WHERE and how it was formatted and forgot. I insinuated that in my 2nd reply
but didn't put in the details I should have. Otherwise you may have fixed it
sooner. Glad to see it's finally working anyway. Now that you've advanced
your SQL/VBA expertise you can be EvenThriftierFinanceGirl.

One thing you always want to do when you build SQL in VBA code like that -
make sure you test for all possible conditions when testing your code. If it
works for some and not for others you know you have a problem with the SQL
you're building.

Yep, I'm glad that we finally got to the bottom of that! I could see that it
was getting the number properly, didn't think about the quotes! Yay! Thanks
for the help!

Yes,that would cause a problem. In that case, for your where clause you need:

[quoted text clipped - 26 lines]

Me.qryWorkWithTax_subform.Form.Requery