you canceled the previous operation

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello Everyone,

I know what the problem is and even know how you would normally fix this
problem. In this situation I cannot figure out the way to fix the problem.

I get the above error message. First off yes everything is spelled
correctly. The Flange_Type is a number field. The Form uses a list box so
that a user can select as many items as possible. The way I know how to
write the code to do this makes the values in varients even though they are
numbers. How can I write the code to work but that it will not give me this
problem.

I am only putting in the needed code to fix problem.

Dim varItem5 As Variable
Dim strCriteria5 As String

If Me.listFlange.ItemsSelected.Count > 0 Then
For Each varItem5 In Me.listFlange.ItemsSelected
strCriteria5 = strCriteria5 & ",'" & Me.listFlange.ItemData(varItem5) &
"'"
Next varItem5
strCriteria5 = Right(strCriteria5, Len(strCriteria5) - 1)
Else
strCriteria5 = "NewTable.Flange_Type Like ' * ' "
End If



strSQL = "SELECT * FROM NewTable " & _
"WHERE NewTable.Flange_Type IN(" & strCriteria5 & ");"

Thank you,
Swordsman8

If there is no way to do it I will have to fix the excel sheets and change
the numbers to words.
 
Using Variant the way you have for inspecting the ItemsSelected collection of
a listbox is perfectly valid and OK.

Looks to me like the problem is you IN() clause.
Unless there is some other code to build strCriteria5 it isn't valid at the
point where you build strSQL.

Even if you do, I suspect that is the problem anyway...
I'd check the value of strSQL and make sure all SQL in the string is valid
when used...

Steve
 
How would I fix what I have for code. Is there something I have to do with
strCriteria5 to make it work for a number field. Do I do something in the
IN() statement to make it understand that it is a number.

The code I have works fine with all the other code I have but when I use it
here is craps out.

thank you,
Swordsman8
 
Under what circumstances do you get the error? From what you've posted, in
the case where no items are selected then your WHERE clause will look
something like this:

"WHERE NewTable.Flange_Type IN(NewTable.Flange_Type Like ' * ' );"

That's definitely not right. Can you set a breakpoint just after building
the SQL string and print out the string to see what it looks like?

Carl Rapson
 
This code is attached to the OK button. Everything else in the code works.
I have tested it with out the Flange code and it runs perfectly everytime. I
have simply told the database users they must always select something in each
list box.

Entire Code:

Private Sub cmdOk_Click()

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String


Set db = CurrentDb()
Set qdf = db.QueryDefs("SortedSeals")

If Me.listMfg.ItemsSelected.Count > 0 Then
For Each varItem In Me.listMfg.ItemsSelected
strCriteria = strCriteria & ",'" & Me.listMfg.ItemData(varItem) & "'"
Next varItem
strCriteria = Right(strCriteria, Len(strCriteria) - 1)
Else
strCriteria = "NewTable.MFG Like ' * ' "
End If

Dim varItem2 As Variant
Dim strCriteria2 As String

If Me.listProcess.ItemsSelected.Count > 0 Then
For Each varItem2 In Me.listProcess.ItemsSelected
strCriteria2 = strCriteria2 & ",'" & Me.listProcess.ItemData(varItem2) &
"'"
Next varItem2
strCriteria2 = Right(strCriteria2, Len(strCriteria2) - 1)
Else
strCriteria2 = "NewTable.Process Like ' * ' "
End If


Dim varItem3 As Variant
Dim strCriteria3 As String

If Me.listCast.ItemsSelected.Count > 0 Then
For Each varItem3 In Me.listCast.ItemsSelected
strCriteria3 = strCriteria3 & ",'" & Me.listCast.ItemData(varItem3) & "'"
Next varItem3
strCriteria3 = Right(strCriteria3, Len(strCriteria3) - 1)
Else
strCriteria3 = "NewTable.Cast Like ' * ' "
End If


Dim varItem4 As Variant
Dim strCriteria4 As String

If Me.listChamfer.ItemsSelected.Count > 0 Then
For Each varItem4 In Me.listChamfer.ItemsSelected
strCriteria4 = strCriteria4 & ",'" & Me.listChamfer.ItemData(varItem4) &
"'"
Next varItem4
strCriteria4 = Right(strCriteria4, Len(strCriteria4) - 1)
Else
strCriteria4 = "NewTable.Chamfer Like ' * ' "
End If

Dim varItem5 As Variant
Dim strCriteria5 As String

If Me.listFlange.ItemsSelected.Count > 0 Then
For Each varItem5 In Me.listFlange.ItemsSelected
strCriteria5 = strCriteria5 & ",'" & Me.listFlange.ItemData(varItem5) &
"'"
Next varItem5
strCriteria5 = Right(strCriteria5, Len(strCriteria5) - 1)
Else
strCriteria5 = "NewTable.Flange_Type Like ' * ' "
End If



strSQL = "SELECT * FROM NewTable " & _
"WHERE NewTable.MFG IN(" & strCriteria & ") and NewTable.Process
IN(" & strCriteria2 & ") and NewTable.Cast IN(" & strCriteria3 & ") and
NewTable.Chamfer IN(" & strCriteria4 & ") and NewTable.Flange_Type IN(" &
strCriteria5 & ") And NewTable.Group>=(" & cboGroupLow & ") And
NewTable.Group<=(" & cboGroupHigh & ") AND NewTable.Seal>=(" & cboSealLow &
") And NewTable.Seal<=(" & cboSealHigh & ") AND NewTable.Sa>=(" & cboSaLow &
") And NewTable.Sa<=(" & cboSaHigh & ") AND NewTable.Sq>=(" & cboSqLow & ")
And NewTable.Sq<=(" & cboSqHigh & ") AND NewTable.Sp>=(" & cboSpLow & ") And
NewTable.Sp<=(" & cboSpHigh & ") AND NewTable.Sv>=(" & cboSvLow & ") And
NewTable.Sv<=(" & cboSvHigh & ") AND NewTable.St>=(" & cboStLow & ") And
NewTable.St<=(" & cboStHigh & ") AND NewTable.Ssk>=(" & cboSskLow & ") And
NewTable.Ssk<=(" & cboSskHigh & ") AND NewTable.Sku>=(" & cboSkulow & ") And
NewTable.Sku<=(" & cboSkuHigh & ") AND NewTable.Sz>=(" & cboSzLow & ") And
NewTable.Sz<=(" & cboSzHigh & ") " & _
"AND NewTable.Smvr>=(" & cboSmvrLow & ") And NewTable.Smvr<=(" &
cboSmvrHigh & ") AND NewTable.Sds>=(" & cboSdsLow & ") And NewTable.Sds<=(" &
cboSdsHigh & ") AND NewTable.Sal>=(" & cboSalLow & ") And NewTable.Sal<=(" &
cboSalHigh & ") AND NewTable.Std>=(" & cboStdLow & ") And NewTable.Std<=(" &
cboStdHigh & ") AND NewTable.Sdq>=(" & cboSdqLow & ") And NewTable.Sdq<=(" &
cboSdqHigh & ") AND NewTable.Sdr>=(" & cboSdrLow & ") And NewTable.Sdr<=(" &
cboSdrHigh & ") AND NewTable.Sk>=(" & cboSkLow & ") And NewTable.Sk<=(" &
cboSkHigh & ") AND NewTable.Spk>=(" & cboSpkLow & ") And NewTable.Spk<=(" &
cboSpkHigh & ") AND NewTable.Svk>=(" & cboSvkLow & ") And NewTable.Svk<=(" &
cboSvkHigh & ") AND NewTable.Ra>=(" & cboRaLow & ") And NewTable.Ra<=(" &
cboRaHigh & ") AND NewTable.Rp>=(" & cboRpLow & ") And NewTable.Rp<=(" &
cboRpHigh & ") AND NewTable.Rv>=(" & cboRvLow & ") And NewTable.Rv<=(" &
cboRvHigh & ") AND NewTable.Rt>=(" & cboRtLow & ") And NewTable.Rt<=(" &
cboRtHigh & ")" & _
" AND NewTable.Rsk>=(" & cboRskLow & ") And NewTable.Rsk<=(" &
cboRskHigh & ") AND NewTable.Rku>=(" & cboRkuLow & ") And NewTable.Rku<=(" &
cboRkuHigh & ") AND NewTable.Rz>=(" & cboRzLow & ") And NewTable.Rz<=(" &
cboRzHigh & ") AND NewTable.RTp>=(" & cboRTpLow & ") And NewTable.RTp<=(" &
cboRTpHigh & ") AND NewTable.Rk>=(" & cboRkLow & ") And NewTable.Rk<=(" &
cboRkHigh & ") AND NewTable.Rpk>=(" & cboRpkLow & ") And NewTable.Rpk<=(" &
cboRpkHigh & ") AND NewTable.Rvk>=(" & cboRvkLow & ") And NewTable.Rvk<=(" &
cboRvkHigh & ") AND NewTable.PV>=(" & cboPV2Dlow & ") And NewTable.PV<=(" &
cboPV2DHigh & ") AND NewTable.PV_3D>=(" & cboPV3DLow & ") And
NewTable.PV_3D<=(" & cboPV3DHigh & ")" & _
"ORDER BY NewTable.Group, NewTable.Seal;"

qdf.SQL = strSQL

DoCmd.OpenQuery "SortedSeals", acViewNormal, acEdit

Set db = Nothing
Set qdf = Nothing


DoCmd.Close acForm, "frmSelectSeals"

End Sub

Remember that this is not as wide as the VBA window.

The way the code looks in the querry is as follows

SELECT *
FROM NewTable
WHERE (((NewTable.Group)>=(0) And (NewTable.Group)<=(1000)) AND
((NewTable.Seal)>=(0) And (NewTable.Seal)<=(10000)) AND ((NewTable.MFG) In
('Grenoble')) AND ((NewTable.Process) In ('Lap & Polish')) AND
((NewTable.Cast) In ('Toccoa')) AND ((NewTable.Chamfer) In ('Yes')) AND
((NewTable.Flange_Type)=('0')) AND ((NewTable.Sa)>=(0) And
(NewTable.Sa)<=(100)) ....

This is just a small part to allow you to see the way it works. What ever I
put in the list boxes and combo boxes in my form simply appears as the choice
to be displayed.

Thank you,
Swordsman8
 
On what line does the error occur? And under what circumstances - when items
are selected, or when no items are selected, or both?

Carl Rapson

swordsman8 said:
This code is attached to the OK button. Everything else in the code
works.
I have tested it with out the Flange code and it runs perfectly everytime.
I
have simply told the database users they must always select something in
each
list box.

Entire Code:

Private Sub cmdOk_Click()

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String


Set db = CurrentDb()
Set qdf = db.QueryDefs("SortedSeals")

If Me.listMfg.ItemsSelected.Count > 0 Then
For Each varItem In Me.listMfg.ItemsSelected
strCriteria = strCriteria & ",'" & Me.listMfg.ItemData(varItem) & "'"
Next varItem
strCriteria = Right(strCriteria, Len(strCriteria) - 1)
Else
strCriteria = "NewTable.MFG Like ' * ' "
End If

Dim varItem2 As Variant
Dim strCriteria2 As String

If Me.listProcess.ItemsSelected.Count > 0 Then
For Each varItem2 In Me.listProcess.ItemsSelected
strCriteria2 = strCriteria2 & ",'" & Me.listProcess.ItemData(varItem2)
&
"'"
Next varItem2
strCriteria2 = Right(strCriteria2, Len(strCriteria2) - 1)
Else
strCriteria2 = "NewTable.Process Like ' * ' "
End If


Dim varItem3 As Variant
Dim strCriteria3 As String

If Me.listCast.ItemsSelected.Count > 0 Then
For Each varItem3 In Me.listCast.ItemsSelected
strCriteria3 = strCriteria3 & ",'" & Me.listCast.ItemData(varItem3) &
"'"
Next varItem3
strCriteria3 = Right(strCriteria3, Len(strCriteria3) - 1)
Else
strCriteria3 = "NewTable.Cast Like ' * ' "
End If


Dim varItem4 As Variant
Dim strCriteria4 As String

If Me.listChamfer.ItemsSelected.Count > 0 Then
For Each varItem4 In Me.listChamfer.ItemsSelected
strCriteria4 = strCriteria4 & ",'" & Me.listChamfer.ItemData(varItem4)
&
"'"
Next varItem4
strCriteria4 = Right(strCriteria4, Len(strCriteria4) - 1)
Else
strCriteria4 = "NewTable.Chamfer Like ' * ' "
End If

Dim varItem5 As Variant
Dim strCriteria5 As String

If Me.listFlange.ItemsSelected.Count > 0 Then
For Each varItem5 In Me.listFlange.ItemsSelected
strCriteria5 = strCriteria5 & ",'" & Me.listFlange.ItemData(varItem5) &
"'"
Next varItem5
strCriteria5 = Right(strCriteria5, Len(strCriteria5) - 1)
Else
strCriteria5 = "NewTable.Flange_Type Like ' * ' "
End If



strSQL = "SELECT * FROM NewTable " & _
"WHERE NewTable.MFG IN(" & strCriteria & ") and
NewTable.Process
IN(" & strCriteria2 & ") and NewTable.Cast IN(" & strCriteria3 & ") and
NewTable.Chamfer IN(" & strCriteria4 & ") and NewTable.Flange_Type IN(" &
strCriteria5 & ") And NewTable.Group>=(" & cboGroupLow & ") And
NewTable.Group<=(" & cboGroupHigh & ") AND NewTable.Seal>=(" & cboSealLow
&
") And NewTable.Seal<=(" & cboSealHigh & ") AND NewTable.Sa>=(" & cboSaLow
&
") And NewTable.Sa<=(" & cboSaHigh & ") AND NewTable.Sq>=(" & cboSqLow &
")
And NewTable.Sq<=(" & cboSqHigh & ") AND NewTable.Sp>=(" & cboSpLow & ")
And
NewTable.Sp<=(" & cboSpHigh & ") AND NewTable.Sv>=(" & cboSvLow & ") And
NewTable.Sv<=(" & cboSvHigh & ") AND NewTable.St>=(" & cboStLow & ") And
NewTable.St<=(" & cboStHigh & ") AND NewTable.Ssk>=(" & cboSskLow & ") And
NewTable.Ssk<=(" & cboSskHigh & ") AND NewTable.Sku>=(" & cboSkulow & ")
And
NewTable.Sku<=(" & cboSkuHigh & ") AND NewTable.Sz>=(" & cboSzLow & ") And
NewTable.Sz<=(" & cboSzHigh & ") " & _
"AND NewTable.Smvr>=(" & cboSmvrLow & ") And NewTable.Smvr<=("
&
cboSmvrHigh & ") AND NewTable.Sds>=(" & cboSdsLow & ") And
NewTable.Sds<=(" &
cboSdsHigh & ") AND NewTable.Sal>=(" & cboSalLow & ") And NewTable.Sal<=("
&
cboSalHigh & ") AND NewTable.Std>=(" & cboStdLow & ") And NewTable.Std<=("
&
cboStdHigh & ") AND NewTable.Sdq>=(" & cboSdqLow & ") And NewTable.Sdq<=("
&
cboSdqHigh & ") AND NewTable.Sdr>=(" & cboSdrLow & ") And NewTable.Sdr<=("
&
cboSdrHigh & ") AND NewTable.Sk>=(" & cboSkLow & ") And NewTable.Sk<=(" &
cboSkHigh & ") AND NewTable.Spk>=(" & cboSpkLow & ") And NewTable.Spk<=("
&
cboSpkHigh & ") AND NewTable.Svk>=(" & cboSvkLow & ") And NewTable.Svk<=("
&
cboSvkHigh & ") AND NewTable.Ra>=(" & cboRaLow & ") And NewTable.Ra<=(" &
cboRaHigh & ") AND NewTable.Rp>=(" & cboRpLow & ") And NewTable.Rp<=(" &
cboRpHigh & ") AND NewTable.Rv>=(" & cboRvLow & ") And NewTable.Rv<=(" &
cboRvHigh & ") AND NewTable.Rt>=(" & cboRtLow & ") And NewTable.Rt<=(" &
cboRtHigh & ")" & _
" AND NewTable.Rsk>=(" & cboRskLow & ") And NewTable.Rsk<=(" &
cboRskHigh & ") AND NewTable.Rku>=(" & cboRkuLow & ") And NewTable.Rku<=("
&
cboRkuHigh & ") AND NewTable.Rz>=(" & cboRzLow & ") And NewTable.Rz<=(" &
cboRzHigh & ") AND NewTable.RTp>=(" & cboRTpLow & ") And NewTable.RTp<=("
&
cboRTpHigh & ") AND NewTable.Rk>=(" & cboRkLow & ") And NewTable.Rk<=(" &
cboRkHigh & ") AND NewTable.Rpk>=(" & cboRpkLow & ") And NewTable.Rpk<=("
&
cboRpkHigh & ") AND NewTable.Rvk>=(" & cboRvkLow & ") And NewTable.Rvk<=("
&
cboRvkHigh & ") AND NewTable.PV>=(" & cboPV2Dlow & ") And NewTable.PV<=("
&
cboPV2DHigh & ") AND NewTable.PV_3D>=(" & cboPV3DLow & ") And
NewTable.PV_3D<=(" & cboPV3DHigh & ")" & _
"ORDER BY NewTable.Group, NewTable.Seal;"

qdf.SQL = strSQL

DoCmd.OpenQuery "SortedSeals", acViewNormal, acEdit

Set db = Nothing
Set qdf = Nothing


DoCmd.Close acForm, "frmSelectSeals"

End Sub

Remember that this is not as wide as the VBA window.

The way the code looks in the querry is as follows

SELECT *
FROM NewTable
WHERE (((NewTable.Group)>=(0) And (NewTable.Group)<=(1000)) AND
((NewTable.Seal)>=(0) And (NewTable.Seal)<=(10000)) AND ((NewTable.MFG) In
('Grenoble')) AND ((NewTable.Process) In ('Lap & Polish')) AND
((NewTable.Cast) In ('Toccoa')) AND ((NewTable.Chamfer) In ('Yes')) AND
((NewTable.Flange_Type)=('0')) AND ((NewTable.Sa)>=(0) And
(NewTable.Sa)<=(100)) ....

This is just a small part to allow you to see the way it works. What ever
I
put in the list boxes and combo boxes in my form simply appears as the
choice
to be displayed.

Thank you,
Swordsman8
Click to expand...
<snip>
 
If the operator does not select something in the Flange code it will not be
used to define the query's data. When I select a item or multiple items and
hit the OK button I will get the error message: Run Time Error '2001' You
canceled the previous operation.

I believe the problem is that I have the Flange Column in the query and in
the Table labled as a Number column. But I could be very wrong because there
is alot about VBA code and Access I still do not know.

Thank you,
Steven Swordsman8 McGee
 
If the Flange code is numeric, all you need to do is remove the quotes
around the value:

strCriteria5 = strCriteria5 & "," & Me.listFlange.ItemData(varItem5)

Carl Rapson
 
Thank you,

Once I did that it worked fine. Did not see the answer there in that line.

Thank you again
Swordsman8
 
Back
Top