Help - Can't understand Code

[Guest]

Hope someone can help.
Yesterday I was directed to a very helpful link which allowed me to download
a database that demonstrates how to use multi-select list boxes.

I do not know VB but I started a new database, imported all the tables,
queries, forms and modules from this demo database. The main composition is a
form with a multiselect list box that is bound to a table. Next to it is a
subform which is bound to a query. The idea is that you can multi-select from
the list box and the results of your query result in the subform appear. The
sub-form is bound to a query with the following SQL:
SELECT Employees.*
FROM Employees
WHERE (((IsSelectedVar("frmMultiselectListDemo","lboJobTitle",[Title]))=-1));

I have successfully set up a list box bound to my own table and changed the
references in the event procedures to my own. All seems to be working fine
except when I test it, I can select from the multi-select list box but the
the results in the subform do not appear. It doesn't appear to be executing
the query that the sub-form is bound to. The SQL statement attached is
directly out of the demo database.

Would really appreciate some guidance!
Thanks
Angeline
Sydney

 

[Guest]

Just to add to the previous note: The demo database had a module with the
following code:

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 lbo As ListBox
Dim item As Variant
If IsNumeric(varValue) Then
varValue = Trim(Str(varValue))
End If
Set lbo = Forms(strFormName)(strListBoxName)
For Each item In lbo.ItemsSelected
If lbo.ItemData(item) = varValue Then
IsSelectedVar = True
Exit Function
End If
Next
End Function

Question: Am I meant to be changing this code so that the correct form Name,
ListBoxName and Variant is actually referenced properly in the code? If so,
can someone please help me with an example so I get the syntax right? In the
example the Form name is: frmMultiSelectListDemo, the ListBoxName I have
changed to: lboSpecificCompetency and the variant I imagine would be the
field in the table to list box is bound to that the end user select for the
query, in this case is it called: SpecificCompetency.

Thanks a million to anyone who can help!

 

[TC]

Ok, full marks for your problem description! It's perfectly clear, I'm
sure we can help.

The first thing I'd check, is that the example actually works for you
in its original fom. I don't know that database, so I can not comment
on it. But there is always the possibility that you download an example
that actually does not work in the first place! So, download it again
(if you don't still have an unchanged copy) & make sure that it does
actually work.

Next, you do not need to change any names within the IsSelectedVar()
function itself, because that function gets all of the information that
it needs, as parameter values which are passed to it, by whoever calls
it. But in the subforms's SELECT statement, in the IsVarSelected()
call, you /do/ need to change the first two parameter values, to suit
your own needs. Those two values should be (1) the quoted name of your
main form, and (2) the quoted name of the listbox /control/ on the main
form. So if yo have change the name of the main form, or the listbox
control on the main form, you will have to change the two parameters in
the IsVarSlected(0 call in th subform's SELECT statement, to match.

Post back if that does not help. Again,. well done on the problem
description

HTH,
TC (MVP Access)
http://tc2.atspace.com

 

[Guest]

TC,
Wow, thanks so much for the compliment - I needed it.

OK
1) I have the sample database in its orignial form. The form does work
however if I actually try and run the query which the subform is bound to by
itself, I do get an error which sais: Microsoft Access can't find the form
'frmMultiselectListDemo' referred to in a macro expression or VB code.

If I then click on debug, it highliths the following IsVarSelected Module
code:

Set lbo = Forms (strFormName)(strListBoxName)

2) The subform doesn't have a SELECT statement however the query it is bound
to does. You mentioned I had to change the parameter values. I had actually
already done this. At first I hadn't and when I tried to test the form it
came up with an error. I then got wiser to it and changed the parameter names
to suit but the result upon testing was no error, however no results
displayed in the subform.

The sql statement in the subquery that the subform is bound to with my
parameters is:

SELECT TblComp.*
FROM TblComp
WHERE
(((IsSelectedVar("frmMultiselectListDemo","lboSpecificCompetency",[SpecificCompetency]))=-1));

The third paramater [SpecificCompetency] I have assumed should be is the
variable field name that is used in the list box (as was the case in the
demo).

The list box by the way has the following select statement:
SELECT TblComp.SpecificCompetency FROM TblComp GROUP BY
TblComp.SpecificCompetency ORDER BY TblComp.SpecificCompetency;

IF anything jumps out at you I'd so appreciate it. The whole idea of doing
this was for recruiters to customize an interview guide. They view a form
than allows them to select a number of 'specific competencies' out of 50 that
would then display for them the relevent interview questions for those
selected which they can print off. I might be going about this the wrong way.

Cheers
Angeline

 

[Guest]

Hi TC
Just for your reference I got the database from:
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

and selected: Listbox with MultiSelection used in Query

Thanks
Angeline

TC,
Wow, thanks so much for the compliment - I needed it.

OK
1) I have the sample database in its orignial form. The form does work
however if I actually try and run the query which the subform is bound to by
itself, I do get an error which sais: Microsoft Access can't find the form
'frmMultiselectListDemo' referred to in a macro expression or VB code.

If I then click on debug, it highliths the following IsVarSelected Module
code:

Set lbo = Forms (strFormName)(strListBoxName)

2) The subform doesn't have a SELECT statement however the query it is bound
to does. You mentioned I had to change the parameter values. I had actually
already done this. At first I hadn't and when I tried to test the form it
came up with an error. I then got wiser to it and changed the parameter names
to suit but the result upon testing was no error, however no results
displayed in the subform.

The sql statement in the subquery that the subform is bound to with my
parameters is:

SELECT TblComp.*
FROM TblComp
WHERE
(((IsSelectedVar("frmMultiselectListDemo","lboSpecificCompetency",[SpecificCompetency]))=-1));

The third paramater [SpecificCompetency] I have assumed should be is the
variable field name that is used in the list box (as was the case in the
demo).

The list box by the way has the following select statement:
SELECT TblComp.SpecificCompetency FROM TblComp GROUP BY
TblComp.SpecificCompetency ORDER BY TblComp.SpecificCompetency;

IF anything jumps out at you I'd so appreciate it. The whole idea of doing
this was for recruiters to customize an interview guide. They view a form
than allows them to select a number of 'specific competencies' out of 50 that
would then display for them the relevent interview questions for those
selected which they can print off. I might be going about this the wrong way.

Cheers
Angeline

Ok, full marks for your problem description! It's perfectly clear, I'm
sure we can help.

The first thing I'd check, is that the example actually works for you
in its original fom. I don't know that database, so I can not comment
on it. But there is always the possibility that you download an example
that actually does not work in the first place! So, download it again
(if you don't still have an unchanged copy) & make sure that it does
actually work.

Next, you do not need to change any names within the IsSelectedVar()
function itself, because that function gets all of the information that
it needs, as parameter values which are passed to it, by whoever calls
it. But in the subforms's SELECT statement, in the IsVarSelected()
call, you /do/ need to change the first two parameter values, to suit
your own needs. Those two values should be (1) the quoted name of your
main form, and (2) the quoted name of the listbox /control/ on the main
form. So if yo have change the name of the main form, or the listbox
control on the main form, you will have to change the two parameters in
the IsVarSlected(0 call in th subform's SELECT statement, to match.

Post back if that does not help. Again,. well done on the problem
description

HTH,
TC (MVP Access)
http://tc2.atspace.com

 

[TC]

TC,
Wow, thanks so much for the compliment - I needed it.

No probs. It's amazing how little information many questions include! I
thought that yours got the balance just right.

OK
1) I have the sample database in its orignial form. The form does work

Ok, that's a good start.

however if I actually try and run the query which the subform is bound to by
itself, I do get an error which sais: Microsoft Access can't find the form
'frmMultiselectListDemo' referred to in a macro expression or VB code.

That's ok, because the query is using things from the form. If the form
isn't open, the query will fail. *Personally*, I dislike that way of
writing a query. I personally do not believe that a query should be
tightly bound to a form, like that. But lots of people do it like that.

2) The subform doesn't have a SELECT statement however the query it is bound to does.

Ok, got it.

You mentioned I had to change the parameter values. I had actually
already done this. At first I hadn't and when I tried to test the form it
came up with an error. I then got wiser to it and changed the parameter names
to suit but the result upon testing was no error, however no results
displayed in the subform.

I can't quite get my head around their function. Fo some reason, I'm
not quite on the ball at the moment! So here's what I'd do, to move
forward. Temporarily change the query to this:

SELECT TblComp.*
FROM TblComp

Now the subform should display all the records, regardless of what you
select in the listbox. If it *doesn't*, then, the problem is somewhere
other-than in the IsSelectedVar() function. But if it *does* display
all the records, then, there's something wrong with that function, or
the way the query has called it.

Sorry I can't be more specific. I'm sure it's quite simple, but I can't
quite get my head around it!

HTH,
TC (MVP Access)
http://tc2.atspace.com

 

[Guest]

Hello Again,
Thanks for your post and taking the time.

Okay, I did as you said and yes all the results appear in the sub-form as
you suggested. So this meand the problem must be with the Is Select Var ?

Thanks
Angeline

 

[Guest]

TC,
The problem has been solved. It was that the field value had "" around it
instead of []. The other post was answered by the guy who actually wrote the
code.

You're help has been so invaluble though, answering my questions so
specifically has given me a much better understanding of how things work
particularly. IsSelectVar doesn't need you to put in your own parameters AND
why you would get the error running the query by itself. Thank you immensily
and especially for your compliment!

kind regards,
Angeline

 

[TC]

Ok, well done for solving your problem! It's always really satisfying
to reach a successful conclusion.

My compliment was genuine; I wouldn't have said it if I did not mean
it!

Cheers,
TC (MVP Access)
http://tc2.atspace.com