Values in multiselect listbox used in query 3

[nickjar2]

Hopefully someone can help. This is what i was thinking of doing. I have a form, a multiselect listbox (set to simple) and a button. I was also thinking of using a hidden text box to store the multiple values.
Lets say the list box is populated with A, B, C, D, E and F. The use selects A, C, E and clicks the button. I need the hidden text box to then store this:
like "*A*" and like "*C" and like "*E*"

then the query will say forms!formname etc etc (another form will open based on this query). The user will then be shown a form listing all the records where a certain field conatins A, C and E

I written some code but it seems quite a lot for a simple thing. Has anyone any ideas on a simple routine for me? I think I am using the listIndex and the itemsSelcted at the mo (i am not in work so can't quite remember).

Cheers for any help Hope this is ok. If not, just let me know.

Nick (Everton Rool OK!)

 

[jebry]

Hi Nick!

I would do it like this:

Dim bolFirst As Boolean
Dim varRowCount As Variant

bolFirst = True
For Each varRowCount In YourListBox.ItemsSelected
If bolFirst = True Then
txtbox.Value = "Like " & Quote & "*" & YourListBox.Column(0, varRowCount) & "*" & Quote
bolFirst = False
Else
txtbox.Value = txtbox.Value & " And Like " & Quote & "*" & YourListBox.Column(0, varRowCount) & "*" & Quote
End If
Next varRowCount

Where Quote is a constant = """"

hth
Jeff Bridgham
bridgham@purdue.edu

 

[nickjar2]

Cheers Tipmaster,

I will give that a go tommorrow in work. So if my hidden text box says:
like "*A*" and like "*B*" etc, and in my query:
forms!formname!txtHidden

this should work? Hope this is ok. If not, just let me know.

Nick (Everton Rool OK!)

 

[jebry]

Hi Again!

Looks like it should work from. Since we both agree, I'm guessing something is going to go wrong! s-)

Jeff Bridgham
bridgham@purdue.edu

 

[nickjar2]

Lol!

Cheers Hope this is ok. If not, just let me know.

Nick (Everton Rool OK!)

 

[nickjar2]

Hi Jeff,

I changed it slightly:

bolFirst = True
For Each varRowCount In lstSkills.ItemsSelected
If bolFirst = True Then
Text3.Value = "[Person].[Skill] Like " & quote & "*" & lstSkills.Column(0, varRowCount) & "*" & quote
bolFirst = False
Else
Text3.Value = Text3.Value & " And [Person].[Skill] Like " & quote & "*" & lstSkills.Column(0, varRowCount) & "*" & quote
End If
Next varRowCount

ie. including the field name in the loop. It works brilliantly. U deserve to be tipmaster.

Thanks a lot!!!!! Hope this is ok. If not, just let me know.

Nick (Everton Rool OK!)

 

[jebry]

Hi Nick!

No problem, I'm glad it worked for you!

Jeff Bridgham
bridgham@purdue.edu

 

[nickjar2]

Hi Jeff,

Strange, but I am positive I gave u a STAR yesterday as soon as u posted. Sorry for being rude, here goes.

Nick Hope this is ok. If not, just let me know.

Nick (Everton Rool OK!)

 

[nickjar2]

I DID do it yesterday, AND I just did it then. As soon as the liitle window came up, i closed it, instead of saying Yes, before closing it. Hope this is ok. If not, just let me know.

Nick (Everton Rool OK!)

 

[jebry]

Thanks Nick!

I love programming and solving these types of problems, all that and stars too. It's a wonderful world! :-D
Jeff Bridgham
bridgham@purdue.edu