IsSelectedVar

[bw]

I have a form with a multiselect listbox and a query with an IsSelectedVar
function in it. The query gives the results fine. The problem is, when I try
to put a button on the form to open the query, I get a run-time error 459:
Object or class does not support the set of events. I get this error even if
I just try to run the query separately. If the form property Has Module is
set to Yes, I get the error. If No, it works fine.
What am I missing?

 

[OldPro]

I have a form with a multiselect listbox and a query with an IsSelectedVar
function in it. The query gives the results fine. The problem is, when I try
to put a button on the form to open the query, I get a run-time error 459:
Object or class does not support the set of events. I get this error even if
I just try to run the query separately. If the form property Has Module is
set to Yes, I get the error. If No, it works fine.
What am I missing?

Is IsSelectedVar( ) a public function residing in a module? Do you
use distinct syntax to differentiate between fields, controls, and
variables? Is there any chance that two items share the same name?
How are you calling the query? A command button can call a query four
or five different ways... Has Module determines whether there is a
place for form code, a place to add code for events. If you aren't
coding events, then how are you operating? Through macros?

 

[bw]

Yes, IsSelectedVar is a public function in a module. If Has Module is No, and
I open the query and click Run, the query returns the result expected based
on the selection from the listbox. As soon as it is set to Yes so that an
'OK' button will run, I get the error, even trying to just run the query
separately without the button. I've tried using a simple DoCmd.OpenQuery
"qryFindRoHS_FromSupplierList", acViewNormal to run the query, figuring if I
can just click Run and it works, this should work also. I can't figure out
why setting that 1 property causes an error running a query.

Here's the function and the SQL of a simple query I'm trying to run:

Function IsSelectedVar( _
strFormName As String, _
strListBoxName As String, _
varValue As Variant) _
As Boolean
'strFormName is the name of the form
'strListBoxName is the name of the listbox
'varValue is the field to check against the listbox
Dim lst As ListBox
Dim item As Variant

If IsNumeric(varValue) Then
varValue = Trim(Str(varValue))
End If
Set lst = Forms(strFormName)(strListBoxName)
For Each item In lst.ItemsSelected
If lst.ItemData(item) = varValue Then
IsSelectedVar = True
' Exit Function
End If
Next
End Function

SELECT Parts.*
FROM Parts
WHERE (((IsSelectedVar("frmFind_RoHS_Status","lstSupplier",[Supplier
Name]))=-1));

 

[OldPro]

Yes, IsSelectedVar is a public function in a module. If Has Module is No, and
I open the query and click Run, the query returns the result expected based
on the selection from the listbox. As soon as it is set to Yes so that an
'OK' button will run, I get the error, even trying to just run the query
separately without the button. I've tried using a simple DoCmd.OpenQuery
"qryFindRoHS_FromSupplierList", acViewNormal to run the query, figuring if I
can just click Run and it works, this should work also. I can't figure out
why setting that 1 property causes an error running a query.

Here's the function and the SQL of a simple query I'm trying to run:

Function IsSelectedVar( _
strFormName As String, _
strListBoxName As String, _
varValue As Variant) _
As Boolean
'strFormName is the name of the form
'strListBoxName is the name of the listbox
'varValue is the field to check against the listbox
Dim lst As ListBox
Dim item As Variant

If IsNumeric(varValue) Then
varValue = Trim(Str(varValue))
End If
Set lst = Forms(strFormName)(strListBoxName)
For Each item In lst.ItemsSelected
If lst.ItemData(item) = varValue Then
IsSelectedVar = True
' Exit Function
End If
Next
End Function

SELECT Parts.*
FROM Parts
WHERE (((IsSelectedVar("frmFind_RoHS_Status","lstSupplier",[Supplier
Name]))=-1));

Is IsSelectedVar( ) a public function residing in a module? Do you
use distinct syntax to differentiate between fields, controls, and
variables? Is there any chance that two items share the same name?
How are you calling the query? A command button can call a query four
or five different ways... Has Module determines whether there is a
place for form code, a place to add code for events. If you aren't
coding events, then how are you operating? Through macros?- Hide quoted text -

- Show quoted text -

I used your code. It worked fine for me. It's a little weird because
the query cannot be tested without the form open. I created a second
listbox to link to the query and show the results. In the
cmdTest_click event I put the following code:
lstResults.RowSource = "qryTest"
lstResults.Requery

lstResults is the second listbox, and qryTest is the name of the query
I created.

 

[bw]

Did it work for you with Has Module = Yes in the form before you added the
2nd list box? I'm not surprised it didn't work with the form closed. Is the
2nd list box on the same form? What is in qryTest?

Yes, IsSelectedVar is a public function in a module. If Has Module is No, and
I open the query and click Run, the query returns the result expected based
on the selection from the listbox. As soon as it is set to Yes so that an
'OK' button will run, I get the error, even trying to just run the query
separately without the button. I've tried using a simple DoCmd.OpenQuery
"qryFindRoHS_FromSupplierList", acViewNormal to run the query, figuring if I
can just click Run and it works, this should work also. I can't figure out
why setting that 1 property causes an error running a query.

Here's the function and the SQL of a simple query I'm trying to run:

Function IsSelectedVar( _
strFormName As String, _
strListBoxName As String, _
varValue As Variant) _
As Boolean
'strFormName is the name of the form
'strListBoxName is the name of the listbox
'varValue is the field to check against the listbox
Dim lst As ListBox
Dim item As Variant

If IsNumeric(varValue) Then
varValue = Trim(Str(varValue))
End If
Set lst = Forms(strFormName)(strListBoxName)
For Each item In lst.ItemsSelected
If lst.ItemData(item) = varValue Then
IsSelectedVar = True
' Exit Function
End If
Next
End Function

SELECT Parts.*
FROM Parts
WHERE (((IsSelectedVar("frmFind_RoHS_Status","lstSupplier",[Supplier
Name]))=-1));

I have a form with a multiselect listbox and a query with an IsSelectedVar
function in it. The query gives the results fine. The problem is, when I try
to put a button on the form to open the query, I get a run-time error 459:
Object or class does not support the set of events. I get this error even if
I just try to run the query separately. If the form property Has Module is
set to Yes, I get the error. If No, it works fine.
What am I missing?

Is IsSelectedVar( ) a public function residing in a module? Do you
use distinct syntax to differentiate between fields, controls, and
variables? Is there any chance that two items share the same name?
How are you calling the query? A command button can call a query four
or five different ways... Has Module determines whether there is a
place for form code, a place to add code for events. If you aren't
coding events, then how are you operating? Through macros?- Hide quoted text -

- Show quoted text -

I used your code. It worked fine for me. It's a little weird because
the query cannot be tested without the form open. I created a second
listbox to link to the query and show the results. In the
cmdTest_click event I put the following code:
lstResults.RowSource = "qryTest"
lstResults.Requery

lstResults is the second listbox, and qryTest is the name of the query
I created.

 

[OldPro]

Did it work for you with Has Module = Yes in the form before you added the
2nd list box? I'm not surprised it didn't work with the form closed. Is the
2nd list box on the same form? What is in qryTest?

Yes, IsSelectedVar is a public function in a module. If Has Module is No, and
I open the query and click Run, the query returns the result expected based
on the selection from the listbox. As soon as it is set to Yes so that an
'OK' button will run, I get the error, even trying to just run the query
separately without the button. I've tried using a simple DoCmd.OpenQuery
"qryFindRoHS_FromSupplierList", acViewNormal to run the query, figuring if I
can just click Run and it works, this should work also. I can't figure out
why setting that 1 property causes an error running a query.
Here's the function and the SQL of a simple query I'm trying to run:
Function IsSelectedVar( _
strFormName As String, _
strListBoxName As String, _
varValue As Variant) _
As Boolean
'strFormName is the name of the form
'strListBoxName is the name of the listbox
'varValue is the field to check against the listbox
Dim lst As ListBox
Dim item As Variant
If IsNumeric(varValue) Then
varValue = Trim(Str(varValue))
End If
Set lst = Forms(strFormName)(strListBoxName)
For Each item In lst.ItemsSelected
If lst.ItemData(item) = varValue Then
IsSelectedVar = True
' Exit Function
End If
Next
End Function
SELECT Parts.*
FROM Parts
WHERE (((IsSelectedVar("frmFind_RoHS_Status","lstSupplier",[Supplier
Name]))=-1));
:
I have a form with a multiselect listbox and a query with an IsSelectedVar
function in it. The query gives the results fine. The problem is, when I try
to put a button on the form to open the query, I get a run-time error 459:
Object or class does not support the set of events. I get this error even if
I just try to run the query separately. If the form property Has Module is
set to Yes, I get the error. If No, it works fine.
What am I missing?
- Show quoted text -

I used your code. It worked fine for me. It's a little weird because
the query cannot be tested without the form open. I created a second
listbox to link to the query and show the results. In the
cmdTest_click event I put the following code:
lstResults.RowSource = "qryTest"
lstResults.Requery

lstResults is the second listbox, and qryTest is the name of the query
I created.- Hide quoted text -

- Show quoted text -

Without the 2nd listbox, how would I know if the query worked? Yes it
is on the same form. The SQL version of the query is virtually the
same as the SQL statement that you supplied. I used my own customer
table though.

 

[bw]

A 2nd list box did the same thing. I don't know why you couldn't see the
results from the 1st listbox. Anyway, I made a new form, set Has Module as
Yes right at the beginning and then modified the query to point to it. Works
fine now. Very strange. Thanks for the help.

Did it work for you with Has Module = Yes in the form before you added the
2nd list box? I'm not surprised it didn't work with the form closed. Is the
2nd list box on the same form? What is in qryTest?

Yes, IsSelectedVar is a public function in a module. If Has Module is No, and
I open the query and click Run, the query returns the result expected based
on the selection from the listbox. As soon as it is set to Yes so that an
'OK' button will run, I get the error, even trying to just run the query
separately without the button. I've tried using a simple DoCmd.OpenQuery
"qryFindRoHS_FromSupplierList", acViewNormal to run the query, figuring if I
can just click Run and it works, this should work also. I can't figure out
why setting that 1 property causes an error running a query.

Here's the function and the SQL of a simple query I'm trying to run:

Function IsSelectedVar( _
strFormName As String, _
strListBoxName As String, _
varValue As Variant) _
As Boolean
'strFormName is the name of the form
'strListBoxName is the name of the listbox
'varValue is the field to check against the listbox
Dim lst As ListBox
Dim item As Variant

If IsNumeric(varValue) Then
varValue = Trim(Str(varValue))
End If
Set lst = Forms(strFormName)(strListBoxName)
For Each item In lst.ItemsSelected
If lst.ItemData(item) = varValue Then
IsSelectedVar = True
' Exit Function
End If
Next
End Function

SELECT Parts.*
FROM Parts
WHERE (((IsSelectedVar("frmFind_RoHS_Status","lstSupplier",[Supplier
Name]))=-1));

:
I have a form with a multiselect listbox and a query with an IsSelectedVar
function in it. The query gives the results fine. The problem is, when I try
to put a button on the form to open the query, I get a run-time error 459:
Object or class does not support the set of events. I get this error even if
I just try to run the query separately. If the form property Has Module is
set to Yes, I get the error. If No, it works fine.
What am I missing?

- Show quoted text -

I used your code. It worked fine for me. It's a little weird because
the query cannot be tested without the form open. I created a second
listbox to link to the query and show the results. In the
cmdTest_click event I put the following code:
lstResults.RowSource = "qryTest"
lstResults.Requery

lstResults is the second listbox, and qryTest is the name of the query
I created.- Hide quoted text -

- Show quoted text -

Without the 2nd listbox, how would I know if the query worked? Yes it
is on the same form. The SQL version of the query is virtually the
same as the SQL statement that you supplied. I used my own customer
table though.

 

[OldPro]

A 2nd list box did the same thing. I don't know why you couldn't see the
results from the 1st listbox. Anyway, I made a new form, set Has Module as
Yes right at the beginning and then modified the query to point to it. Works
fine now. Very strange. Thanks for the help.

Did it work for you with Has Module = Yes in the form before you added the
2nd list box? I'm not surprised it didn't work with the form closed. Is the
2nd list box on the same form? What is in qryTest?
:
Yes, IsSelectedVar is a public function in a module. If Has Module is No, and
I open the query and click Run, the query returns the result expected based
on the selection from the listbox. As soon as it is set to Yes so that an
'OK' button will run, I get the error, even trying to just run the query
separately without the button. I've tried using a simple DoCmd.OpenQuery
"qryFindRoHS_FromSupplierList", acViewNormal to run the query, figuring if I
can just click Run and it works, this should work also. I can't figure out
why setting that 1 property causes an error running a query.
Here's the function and the SQL of a simple query I'm trying to run:
Function IsSelectedVar( _
strFormName As String, _
strListBoxName As String, _
varValue As Variant) _
As Boolean
'strFormName is the name of the form
'strListBoxName is the name of the listbox
'varValue is the field to check against the listbox
Dim lst As ListBox
Dim item As Variant
If IsNumeric(varValue) Then
varValue = Trim(Str(varValue))
End If
Set lst = Forms(strFormName)(strListBoxName)
For Each item In lst.ItemsSelected
If lst.ItemData(item) = varValue Then
IsSelectedVar = True
' Exit Function
End If
Next
End Function
SELECT Parts.*
FROM Parts
WHERE (((IsSelectedVar("frmFind_RoHS_Status","lstSupplier",[Supplier
Name]))=-1));
:
I have a form with a multiselect listbox and a query with an IsSelectedVar
function in it. The query gives the results fine. The problem is, when I try
to put a button on the form to open the query, I get a run-time error 459:
Object or class does not support the set of events. I get this error even if
I just try to run the query separately. If the form property Has Module is
set to Yes, I get the error. If No, it works fine.
What am I missing?
- Show quoted text -
I used your code. It worked fine for me. It's a little weird because
the query cannot be tested without the form open. I created a second
listbox to link to the query and show the results. In the
cmdTest_click event I put the following code:
lstResults.RowSource = "qryTest"
lstResults.Requery
lstResults is the second listbox, and qryTest is the name of the query
I created.- Hide quoted text -
- Show quoted text -

Without the 2nd listbox, how would I know if the query worked? Yes it
is on the same form. The SQL version of the query is virtually the
same as the SQL statement that you supplied. I used my own customer
table though.- Hide quoted text -

- Show quoted text -

I used the first listbox to select the records for the query, and the
second listbox to display the results of the query.