IsSelectedVar - Duane Hookom

[Brigitte P]

I use the sample Listbox with Multiselect used in query from Duane's sample
database found on Roger's library, and it works great - I almost understand
the logic behind it. However, I need to select from two different listboxes
for the same query. One is txtMultiEvent and the other is txtMultiInjury.
I thought I could just use
IsSelectedVar("frmMultiselect","txtMultiEventType",[EventEvent]) but
substitute txtMulitInjury and have two IsSelectedVar statements in my query.
It doesn't work.
I suppose I need to accomodate the second box also in the module that I
copied from Duane's database and added my form and field name (field name for
the second field would be EventIllnessInjury). If this is so, I have no idea
how to do this. Also, why does the query have a -1 in the criteria line.
Would I need to do the same thing if I had a second IsSelectedVar statement
in my query?
My listboxes have only one field EventEvent and the second box
EventIllnessInjury.
I think I'm getting there, and appreciate any help to really complete this
part of the project.
I have used one of Duane Hookoms databases as a sample before, and thing
still run great.
Thanks for sharing your expertise so freely.
Brigitte P.

 

[Guest]

IsSelectedVar("frmMultiselect","txtMultiEventType",[EventEvent]) but

substitute txtMulitInjury and have two IsSelectedVar statements in my
query.
Yes

It doesn't work.

show us what you have. describe the failure.

why does the query have a -1 in the criteria line.

presumably, it goes with IsSelectedVar. show us what you have.

(david)

I use the sample Listbox with Multiselect used in query from Duane's sample
database found on Roger's library, and it works great - I almost understand
the logic behind it. However, I need to select from two different listboxes
for the same query. One is txtMultiEvent and the other is txtMultiInjury.
I thought I could just use
IsSelectedVar("frmMultiselect","txtMultiEventType",[EventEvent]) but
substitute txtMulitInjury and have two IsSelectedVar statements in my query.
It doesn't work.
I suppose I need to accomodate the second box also in the module that I
copied from Duane's database and added my form and field name (field name for
the second field would be EventIllnessInjury). If this is so, I have no idea
how to do this. Also, why does the query have a -1 in the criteria line.
Would I need to do the same thing if I had a second IsSelectedVar statement
in my query?
My listboxes have only one field EventEvent and the second box
EventIllnessInjury.
I think I'm getting there, and appreciate any help to really complete this
part of the project.
I have used one of Duane Hookoms databases as a sample before, and thing
still run great.
Thanks for sharing your expertise so freely.
Brigitte P.

 

[Brigitte P]

Thanks for picking this up:

SQL behind query
PARAMETERS [Forms]![frmMultiselect]![txtUnit] Text ( 255 ),
[Forms]![frmMultiselect]![txtMultiEventType] Text ( 255 ),
[Forms]![frmMultiselect]![txtEndDate] DateTime,
[Forms]![frmMultiselect]![txtStartDate] DateTime,
[Forms]![frmMultiselect]![txtInjury] Text ( 255 );
SELECT CliEventTable.EventEvent, CliEventTable.EventIllnessInjury,
CliEventTable.EventDate, Format([EventDate],"yyyy mm") AS MonthNumber,
CliEventTable.EventBuilding, IIf([EventDate] Between
(DateAdd("m",-12,[Forms]![frmMultiselect]![txtStartDate])) And
[Forms]![frmMultiselect]![txtStartDate],1,0) AS Twelve, IIf([EventDate]
Between (DateAdd("m",-6,[Forms]![frmMultiselect]![txtStartDate])) And
[Forms]![frmMultiselect]![txtStartDate],1,0) AS Six
FROM CliEventTable
WHERE (((CliEventTable.EventDate) Between
(DateAdd("m",-12,[Forms]![frmMultiselect]![txtStartDate])) And
[Forms]![frmMultiselect]![txtEndDate]) AND
((CliEventTable.EventBuilding)=[Forms]![frmMultiselect]![txtUnit]) AND
((IsSelectedVar("frmMultiselect","txtMultiEventType",[EventEvent]))=-1));

need to add someplace the second listbox
("frmMultiselect","txtMultiInjury",[EventIllnessInjury])


VBA from module that seems to make the whole thing work:

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 = frmMultiselect)(strListBoxName = EventEvent)
For Each item In lbo.ItemsSelected
If lbo.ItemData(item) = varValue Then
IsSelectedVar = True
Exit Function
End If
Next
End Function

I someplace need to declare the second lbo and include it but don't know
where.

IsSelectedVar("frmMultiselect","txtMultiEventType",[EventEvent]) but
substitute txtMulitInjury and have two IsSelectedVar statements in my
query.
Yes

It doesn't work.

show us what you have. describe the failure.

why does the query have a -1 in the criteria line.

presumably, it goes with IsSelectedVar. show us what you have.

(david)

I use the sample Listbox with Multiselect used in query from Duane's sample
database found on Roger's library, and it works great - I almost understand
the logic behind it. However, I need to select from two different listboxes
for the same query. One is txtMultiEvent and the other is txtMultiInjury.
I thought I could just use
IsSelectedVar("frmMultiselect","txtMultiEventType",[EventEvent]) but
substitute txtMulitInjury and have two IsSelectedVar statements in my query.
It doesn't work.
I suppose I need to accomodate the second box also in the module that I
copied from Duane's database and added my form and field name (field name for
the second field would be EventIllnessInjury). If this is so, I have no idea
how to do this. Also, why does the query have a -1 in the criteria line.
Would I need to do the same thing if I had a second IsSelectedVar statement
in my query?
My listboxes have only one field EventEvent and the second box
EventIllnessInjury.
I think I'm getting there, and appreciate any help to really complete this
part of the project.
I have used one of Duane Hookoms databases as a sample before, and thing
still run great.
Thanks for sharing your expertise so freely.
Brigitte P.

 

[Guest]

((CliEventTable.EventBuilding)=[Forms]![frmMultiselect]![txtUnit]) AND

((IsSelectedVar("frmMultiselect","txtMultiEventType",[EventEvent]))=-1));

( (CliEventTable.EventBuilding)=[Forms]![frmMultiselect]![txtUnit] )
AND
( (IsSelectedVar("frmMultiselect","txtMultiEventType",[EventEvent]))=-1 )
AND
(
(IsSelectedVar("frmMultiselect","txtMultiInjury",[EventIllnessInjury]))=-1 )
);


-1 is the value for TRUE used by VBA (and Intel x86 compatible computers).
SQL sometimes uses +1 for TRUE (used by PDP compatible computers),
so this SQL example explicitly uses -1 to match the VBA, rather than TRUE,
which would be ambiguous in general SQL.
(david)

Thanks for picking this up:

SQL behind query
PARAMETERS [Forms]![frmMultiselect]![txtUnit] Text ( 255 ),
[Forms]![frmMultiselect]![txtMultiEventType] Text ( 255 ),
[Forms]![frmMultiselect]![txtEndDate] DateTime,
[Forms]![frmMultiselect]![txtStartDate] DateTime,
[Forms]![frmMultiselect]![txtInjury] Text ( 255 );
SELECT CliEventTable.EventEvent, CliEventTable.EventIllnessInjury,
CliEventTable.EventDate, Format([EventDate],"yyyy mm") AS MonthNumber,
CliEventTable.EventBuilding, IIf([EventDate] Between
(DateAdd("m",-12,[Forms]![frmMultiselect]![txtStartDate])) And
[Forms]![frmMultiselect]![txtStartDate],1,0) AS Twelve, IIf([EventDate]
Between (DateAdd("m",-6,[Forms]![frmMultiselect]![txtStartDate])) And
[Forms]![frmMultiselect]![txtStartDate],1,0) AS Six
FROM CliEventTable
WHERE (((CliEventTable.EventDate) Between
(DateAdd("m",-12,[Forms]![frmMultiselect]![txtStartDate])) And
[Forms]![frmMultiselect]![txtEndDate]) AND
((CliEventTable.EventBuilding)=[Forms]![frmMultiselect]![txtUnit]) AND
((IsSelectedVar("frmMultiselect","txtMultiEventType",[EventEvent]))=-1));

need to add someplace the second listbox
("frmMultiselect","txtMultiInjury",[EventIllnessInjury])


VBA from module that seems to make the whole thing work:

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 = frmMultiselect)(strListBoxName = EventEvent)
For Each item In lbo.ItemsSelected
If lbo.ItemData(item) = varValue Then
IsSelectedVar = True
Exit Function
End If
Next
End Function

I someplace need to declare the second lbo and include it but don't know
where.

IsSelectedVar("frmMultiselect","txtMultiEventType",[EventEvent]) but
substitute txtMulitInjury and have two IsSelectedVar statements in my
query.
Yes

It doesn't work.

show us what you have. describe the failure.

why does the query have a -1 in the criteria line.

presumably, it goes with IsSelectedVar. show us what you have.

(david)

I use the sample Listbox with Multiselect used in query from Duane's sample
database found on Roger's library, and it works great - I almost understand
the logic behind it. However, I need to select from two different listboxes
for the same query. One is txtMultiEvent and the other is txtMultiInjury.
I thought I could just use
IsSelectedVar("frmMultiselect","txtMultiEventType",[EventEvent]) but
substitute txtMulitInjury and have two IsSelectedVar statements in my query.
It doesn't work.
I suppose I need to accomodate the second box also in the module that I
copied from Duane's database and added my form and field name (field

name
for

the second field would be EventIllnessInjury). If this is so, I have

no
idea

how to do this. Also, why does the query have a -1 in the criteria line.
Would I need to do the same thing if I had a second IsSelectedVar statement
in my query?
My listboxes have only one field EventEvent and the second box
EventIllnessInjury.
I think I'm getting there, and appreciate any help to really complete this
part of the project.
I have used one of Duane Hookoms databases as a sample before, and thing
still run great.
Thanks for sharing your expertise so freely.
Brigitte P.