Function as criteria in query

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

Guest

I have a simple list box which I hope to use as criteria in a query. So far
it works, but only if there is one item selected. With more than one item, I
get "Error 2001 - You canceled the previous operation" when I try to run the
query. I'm using Access 2000.

Public Function PoolsCrit()
Dim PoolChos As Variant, Pools As String
For Each PoolChos In
Forms![PoolCommitSelection]![PoolSelector].ItemsSelected()
If Len(Pools) <> 0 Then Pools = Pools & " Or "
Pools = Pools & Forms![PoolCommitSelection]![PoolSelector].Column(0,
PoolChos)
Next PoolChos
PoolsCrit = Pools
End Function

"PoolSelector" is the name of the simple list box in the form called
"PoolCommitSelection"

Can anybody tell me what exactly this error means and how to get around it?
 
Thanks for the prompt reply, Duane - Here you go:

SELECT ReconMaster.LOANNUM, ReconMaster.Pool
FROM ReconMaster
WHERE (((ReconMaster.Pool)=PoolsCrit()));


Duane Hookom said:
We can't see your query's SQL view.

You might want to consider using the generic listbox function found at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

--
Duane Hookom
MS Access MVP

scojerroc said:
I have a simple list box which I hope to use as criteria in a query. So
far
it works, but only if there is one item selected. With more than one
item, I
get "Error 2001 - You canceled the previous operation" when I try to run
the
query. I'm using Access 2000.

Public Function PoolsCrit()
Dim PoolChos As Variant, Pools As String
For Each PoolChos In
Forms![PoolCommitSelection]![PoolSelector].ItemsSelected()
If Len(Pools) <> 0 Then Pools = Pools & " Or "
Pools = Pools & Forms![PoolCommitSelection]![PoolSelector].Column(0,
PoolChos)
Next PoolChos
PoolsCrit = Pools
End Function

"PoolSelector" is the name of the simple list box in the form called
"PoolCommitSelection"

Can anybody tell me what exactly this error means and how to get around
it?
Click to expand...
Click to expand...
 
If your code doesn't work then try mine which was at the page I posted. I
don't think your concatenated values will work as you anticipate. I could be
wrong. I know my solution works in all situations that I tested.

--
Duane Hookom
MS Access MVP

scojerroc said:
Thanks for the prompt reply, Duane - Here you go:

SELECT ReconMaster.LOANNUM, ReconMaster.Pool
FROM ReconMaster
WHERE (((ReconMaster.Pool)=PoolsCrit()));


Duane Hookom said:
We can't see your query's SQL view.

You might want to consider using the generic listbox function found at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

--
Duane Hookom
MS Access MVP

scojerroc said:
I have a simple list box which I hope to use as criteria in a query. So
far
it works, but only if there is one item selected. With more than one
item, I
get "Error 2001 - You canceled the previous operation" when I try to
run
the
query. I'm using Access 2000.

Public Function PoolsCrit()
Dim PoolChos As Variant, Pools As String
For Each PoolChos In
Forms![PoolCommitSelection]![PoolSelector].ItemsSelected()
If Len(Pools) <> 0 Then Pools = Pools & " Or "
Pools = Pools & Forms![PoolCommitSelection]![PoolSelector].Column(0,
PoolChos)
Next PoolChos
PoolsCrit = Pools
End Function

"PoolSelector" is the name of the simple list box in the form called
"PoolCommitSelection"

Can anybody tell me what exactly this error means and how to get around
it?
Click to expand...
Click to expand...
Click to expand...
 
It looks like this will work just fine. Thanks much.

s

Duane Hookom said:
We can't see your query's SQL view.

You might want to consider using the generic listbox function found at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

--
Duane Hookom
MS Access MVP

scojerroc said:
I have a simple list box which I hope to use as criteria in a query. So
far
it works, but only if there is one item selected. With more than one
item, I
get "Error 2001 - You canceled the previous operation" when I try to run
the
query. I'm using Access 2000.

Public Function PoolsCrit()
Dim PoolChos As Variant, Pools As String
For Each PoolChos In
Forms![PoolCommitSelection]![PoolSelector].ItemsSelected()
If Len(Pools) <> 0 Then Pools = Pools & " Or "
Pools = Pools & Forms![PoolCommitSelection]![PoolSelector].Column(0,
PoolChos)
Next PoolChos
PoolsCrit = Pools
End Function

"PoolSelector" is the name of the simple list box in the form called
"PoolCommitSelection"

Can anybody tell me what exactly this error means and how to get around
it?
Click to expand...
Click to expand...
 
Back
Top