Deselecting in Listboxes 1

[347pg]

I have a form which has multiple listboxes. Most of these (except one) are multi-select. The one I'm concerned about is a non-multi-select. It is an either-or box, i.e. you can choose this item or the other, but not both. I've set "MultiSelect" in properties to "None" which allows the either-or, but if the user decides they do not want to use this listbox after all and tries to deselect a previously selected option, it will not deselect. Is there a way to deselect options in a Listbox that has multi-select set to "None" without going to VBA, or setting multi-select to "Simple" or "Extended"?
Thanks for your help.
pw

 

[SkipVought]

Make NONE a selection.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

I meant, to have a NONE Item in the listbox.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[PWise]

how about adding a none line but if this is a either /or box how can you have none

 

[347pg]

Well I'm writing the item selected to a query. If I have a None selection, I'll have to write more code which will complicate the query.
I can't understand how MS let this fly. You can deselect on the "simple" and "extended" options all day long. But those settings allow the user to select both options, which in my case, will basically select all the data, which is also like not selecting either option. I hate to go to an "extended" option because it makes it look like the programmer didn't know what he was doing, which is actually the case since i'm asking for help. I just don't want anyone to know that, except you guys of course!!!!!!! HA!

 

[MazeWorX]

you just add code for the none selection in other words

If list.ItemsSelected = "None" Then

'Do nothing

If I have a None selection, I'll have to write more code which will complicate the query

Really? a simple IF statement is too much for you to write? and it will have nothing to do with the query




HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[347pg]

What I'm saying is the code is extraneous. if the user doesn't want to use the limiter option, he should be able to click what he previously selected, and the highlight should be removed. having a NONE option is the same as having both selected, which searches all the records. If he wants all records searched, he doesn't need to make a selection.
Plus, I'm very limited on room. It's a busy form.
The user may want to search all data so I need a deselect option if they accidentally/or purposefully click in the listbox and then change their mind. If I have to go "Extended" I will, just not the best option.
The None option adds a line to the listbox and increases the height and I basically have that already if they don't select anything (i.e. the code bypasses the box and does not write to the query).

 

[PWise]

Well I'm writing the item selected to a query

it seems that you are using this form to for a select query


how are you doing this
1) by rewriting the SQL of the query

currentdb.querydefs("queryname").sql = xyz

2) by having a criteia in you query

forms!formname!listbox

 

[347pg]

I haven't added code for the non-multi-select listbox yet but was waiting to see if there was a way to deselect. Here is my code as it stands right now (with help from PHV). I've got 4 multi-selects and 5 text boxes (3 with 'OR' between them and 2 with "AND' between them) which all write to the query. It's working great now but I have to add in two more multi-selects and two non-multi-select listboxes.
I'll probably go with the 'Extended' listboxes because those give the appearance of being either-or and you can deselect by using CTRL+Click. the user can select both though if they use the CTRL key.

Private Sub cmdApplyChoices_Click()

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strSQLCriteria As String
Dim strSQL As String
Dim strSQLProj As String
Dim strSQLPhase As String
Dim strSQLSkill As String
Dim strSQLMod As String
Dim strSQLKW As String

Set db = CurrentDb()
Set qdf = db.QueryDefs("IT_MTL Query")

'============================================
'Below we set all the initial strings to null
'============================================

strSQLCriteria = ""
strSQL = ""
strSQLProj = ""
strSQLPhase = ""
strSQLSkill = ""
strSQLMod = ""
strSQLKW = ""


'=======================================================
'Below we'll get items selected from the Project listbox
'=======================================================
If Me!ProjList.ItemsSelected.Count = 0 Then
'Do Nothing

Else

For Each varItem In Me!ProjList.ItemsSelected
strSQLProj = strSQLProj & "IT_MTL.[Task ID] Like '*" & Me.ProjList.ItemData(varItem) & "*' OR "
Next varItem
strSQLProj = Left(strSQLProj, Len(strSQLProj) - 4)
strSQLCriteria = " AND (" & strSQLProj & ")"


End If


'=====================================================
'Below we'll get items selected from the Phase listbox
'=====================================================
If Me!PhaseList.ItemsSelected.Count = 0 Then
'Do Nothing

Else

For Each varItem In Me!PhaseList.ItemsSelected
strSQLPhase = strSQLPhase & "IT_MTL.[Task ID] Like '*" & Me.PhaseList.ItemData(varItem) & "*' OR "
Next varItem
strSQLPhase = Left(strSQLPhase, Len(strSQLPhase) - 4)
strSQLCriteria = strSQLCriteria & " AND (" & strSQLPhase & ")"


End If


'========================================================
'Below we'll get items selected from the Skillset listbox
'========================================================
If Me!SkillList.ItemsSelected.Count = 0 Then
'Do Nothing

Else

For Each varItem In Me!SkillList.ItemsSelected
strSQLSkill = strSQLSkill & "IT_MTL.[Task ID] Like '*" & Me.SkillList.ItemData(varItem) & "*' OR "
Next varItem
strSQLSkill = Left(strSQLSkill, Len(strSQLSkill) - 4)
strSQLCriteria = strSQLCriteria & " AND (" & strSQLSkill & ")"

End If


'===================================================
'Below we'll get items selected from the Mod listbox
'===================================================
If Me!ModList.ItemsSelected.Count = 0 Then
'Do Nothing

Else

For Each varItem In Me!ModList.ItemsSelected
strSQLMod = strSQLMod & "IT_MTL.[Mod] Like '*" & Me.ModList.ItemData(varItem) & "*' OR "
Next varItem
strSQLMod = Left(strSQLMod, Len(strSQLMod) - 4)
strSQLCriteria = strSQLCriteria & " AND (" & strSQLMod & ")"

End If


'===========================================================
'Below we'll get items selected from the Key Word Text boxes
'===========================================================

If IsNull(Me!KeyWord1) Then
'Do nothing

Else

strSQLKW = " OR IT_MTL.[Task] Like '*" & Me!KeyWord1 & "*'"

End If

If IsNull(Me!KeyWord2) Then
'Do nothing

Else

strSQLKW = strSQLKW & " OR IT_MTL.[Task] Like '*" & Me!KeyWord2 & "*'"

End If

If IsNull(Me!KeyWord3) Then
'Do nothing

Else

strSQLKW = strSQLKW & " OR IT_MTL.[Task] Like '*" & Me!KeyWord3 & "*'"

End If

If IsNull(Me!KeyWord4) Then
'Do nothing

Else

strSQLKW = strSQLKW & " OR IT_MTL.[Task] Like '*" & Me!KeyWord4 & "*'"

End If

If IsNull(Me!KeyWord5) Then
'Do nothing

Else

strSQLKW = strSQLKW & " AND IT_MTL.[Task] Like '*" & Me!KeyWord5 & "*'"

End If

If strSQLKW = "" Then
'Do nothing

Else
strSQLKW = Right(strSQLKW, Len(strSQLKW) - 4)
strSQLCriteria = strSQLCriteria & " AND (" & strSQLKW & ")"

End If


If strSQLCriteria = "" Then
strSQL = "SELECT IT_MTL.[Task ID], IT_MTL.Task, IT_MTL.ProposedHours, IT_MTL.NegHours, IT_MTL.[Mod] FROM IT_MTL "

Else
strSQLCriteria = Right(strSQLCriteria, Len(strSQLCriteria) - 5)
'===================================================
'Below we'll combine all the separate criteria into
'one string called "strSQL"
'===================================================


strSQL = "SELECT IT_MTL.[Task ID], IT_MTL.Task, IT_MTL.ProposedHours, IT_MTL.NegHours, IT_MTL.[Mod] FROM IT_MTL " & _
"WHERE (" & strSQLCriteria & ");"

End If


qdf.SQL = strSQL
DoCmd.OpenQuery "IT_MTL Query"

Set db = Nothing
Set qdf = Nothing


End Sub

 

[PHV]

I need a deselect option
In the Click event procedure of this CmdButton you may try this:
Me!YourListBox.Value = Null

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[347pg]

Ok, I'll give that a whirl.
Thanks
pw

 

[347pg]

In the Click event procedure of this CmdButton you may try this:
Me!YourListBox.Value = Null"

Question though, won't this always set the contents of the listbox to null even if they choose to use it to limit their search?

 

[PHV]

You talked about a deselect option ...

 

[347pg]

Yes, but I wanted a deselect option on the form as opposed to the VBA code. What I did was added a clear choices button. Initially, it only cleared the extended multi-list boxes and text boxes, so I had to change the code so the simple multi-list boxes would clear as well.

Private Sub cmdClearChoices_Click()

Dim varItm As Variant

With Me!lstTechType

For Each varItm In .ItemsSelected
.Selected(varItm) = False
Next varItm

End With

With Me!lstProjSize

For Each varItm In .ItemsSelected
.Selected(varItm) = False
Next varItm

End With

With Me!ProjList

For Each varItm In .ItemsSelected
.Selected(varItm) = False
Next varItm

End With

With Me!PhaseList

For Each varItm In .ItemsSelected
.Selected(varItm) = False
Next varItm

End With

With Me!SkillList

For Each varItm In .ItemsSelected
.Selected(varItm) = False
Next varItm

End With

With Me!ModList

For Each varItm In .ItemsSelected
.Selected(varItm) = False
Next varItm

End With

Me!lstTailSiteSelect.Value = Null
'Me!lstTechType.Value = Null
Me!lstSimCom.Value = Null
'Me!lstProjSize.Value = Null
'Me!ProjList.Value = Null
'Me!PhaseList.Value = Null
'Me!SkillList.Value = Null
'Me!ModList.Value = Null
Me!KeyWord1.Value = Null
Me!KeyWord2.Value = Null
Me!KeyWord3.Value = Null
Me!KeyWord4.Value = Null
Me!KeyWord5.Value = Null

End Sub

 

[TheAceMan1]

How are ya 347pg . . .

[blue]Is there a way to deselect options in a Listbox that has multi-select set to "None" without going to VBA ...[/blue]

[purple]No![/purple] ... what you need to do is remember the value selected and take approriate action when the next selection matches. A [blue]static variable[/blue] does the job just right ... as in the following (be sure to disable/remove any code in any other event meant to pick up the selection ... like ... BeforeUpdate, On Click, On Dbl Click, On Mouse Down ... to prevent interaction):

[blue]Private Sub [purple][B][I]YourListboxName[/I][/B][/purple]_AfterUpdate()
   Dim lbx As ListBox
   Static valLast As String
   
   Set lbx = Me.[purple][B][I]YourListboxName[/I][/B][/purple]
   
   If lbx = valLast Then
      lbx = Null
      valLast = ""
   Else
      valLast = lbx
   End If
   
   Set lbx = Nothing
   
End Sub[/blue]

Give it a whirl ... works great!

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[347pg]

AceMan1
Whoa! Does this set the whole list box to null as opposed to just the values in it? If so, I wasn't aware you could do that.

 

[TheAceMan1]

[blue]Does this set [purple]the whole list box[/purple] to null as opposed to just the values in it?[/blue]

What it does is set the listbox to its default state ... that is:
[ol][li]Value = Null[/li]
[li]ListIndex = -1[/li][/ol]


See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[347pg]

Perfect! Thanks

 

[TheAceMan1]

347pg . . .

BTW ... did you try the code on the single select listbox?

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[347pg]

AM1
I haven't tried it yet because I'm a little fuzzy on the implementation of it. My form gets updated when I click on a button (it runs cmdApplyChoices_Click()). That sub applies all my choices in all list boxes and text boxes and writes them to one big query. After the user exports the query results table to word, I want them to close the query and go back to the form. I want the form to show their previous choices in case they made a mistake and want to modify their query, not starting from scratch. If they want to start over, they can use the clear choices button which clears all list and text boxes on click. I've never used 'on-update' before and am not sure how to incorporate. Does that function operate when you click? and would it still apply the choices to the query build? Would my form clear the choices?

Thanks for helping me understand all this.