By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,785 Members | 2,074 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,785 IT Pros & Developers. It's quick & easy.

Search + Multi Select Box

P: 38
Hello Bytes,

I've been a long time lurker, there so much information here I always find my answers with ease. Until today...

I'm hoping someone can exert a little brain power for me.

I have a search form using the tried and true 'AddToWhere' module, this is working like a champ for user input text boxes but now I'm faced with integrating a multi-select listbox into the same routine.

An added bit of difficulty is all of my code is on the DoD's SECRET network, so I'm unable to show you what I've attempted as it's not connected to the net. I'm posting from the high side at the moment.

How would you implement a single multi-select listbox into the following routine?

If possible please provide the modified code so I can print and hand-jam it on the SIPR workstation.

ACCESS 2003
Expand|Select|Wrap|Line Numbers
  1. ------------------------ Put his code behind the button to search -------
  2.  
  3. Private Sub VIEW_Click()
  4. 'On Error GoTo Err_VIEW_Click
  5. ‘------------------------------ Starting here ----------------------
  6.     Dim MySQL As String, MyCriteria As String, MyRecordSource As String
  7.     Dim ArgCount As Integer
  8.     '  Initialize SELECT statement.
  9.     MySQL = "SELECT * FROM [PhoneLog] WHERE "
  10.  
  11.     '  Use values entered in text boxes in form header to create criteria for WHERE clause.
  12.         'text box name on form     'Field name in Table  'blank info ' number of times run Addt
  13.     AddToWhere [Find1], "[CustomerName]", MyCriteria, ArgCount
  14.     AddToWhere [Find2], "[City]", MyCriteria, ArgCount
  15.  
  16.     '  If no criterion specifed, return all records.
  17.     If MyCriteria = "" Then
  18.         MyCriteria = "True"
  19.     End If
  20.  
  21.     '  Create SELECT statement.
  22.     MyRecordSource = MySQL & MyCriteria
  23.  
  24.       ' set record source to Subform
  25.     Me![YOURsubform].Form.RecordSource = MyRecordSource
  26. -------------------------------------------------------------    
  27.  
  28. Exit_VIEW_Click:
  29.     Exit Sub
  30.  
  31. Err_VIEW_Click:
  32.     MsgBox Error$
  33.     Resume Exit_VIEW_Click
  34.  
  35. End Sub
  36.  
  37. --------------------------------------------------------------
  38.     This is the AddToWhere SUB "copy and paste in a module"
  39. --------------------------------------------------------------
  40. Public Sub AddToWhere(FieldValue As Variant, FieldName As String, MyCriteria As String, ArgCount As Integer)
  41.  
  42.     '  Create criteria for WHERE clause.
  43.     If FieldValue <> "" Then
  44.         '  Add "and" if other criterion exists.
  45.         If ArgCount > 0 Then
  46.             MyCriteria = MyCriteria & " and "
  47.         End If
  48.  
  49.         '  Append criterion to existing criteria.
  50.         '  Enclose FieldValue and asterisk in quotation marks.
  51.         MyCriteria = (MyCriteria & FieldName & " Like " & Chr(39) & FieldValue & Chr(42) & Chr(39))
  52.  
  53.         '  Increase argument count.
  54.         ArgCount = ArgCount + 1
  55.     End If
  56. End Sub
Very Respectfully,

Aaron M.
US ARMY
Dec 2 '08 #1
Share this Question
Share on Google+
36 Replies


Expert 100+
P: 1,287
Kind of depends on what you want to select from your listbox. If you select multiple fields to include, where are the values coming from?
Anyway, here's how I get selections from my list box. I usually have multiple columns of data in the list box, but not all of them are visible to the user.

Expand|Select|Wrap|Line Numbers
  1.     dim i as integer
  2.  
  3.     i = 0
  4.     while i < myListBox.ListCount
  5.         if myListBox.selected(i) then
  6.             AddToWhere myListBox.column(0,i), _
  7.              & myListBox.column(1,i), MyCriteria, ArgCount
  8.         end if
  9.         i = i + 1
  10.     wend
Dec 2 '08 #2

P: 38
Thank you ChipR.

I certainly understand the logic but I'm just not fluent enough to take the logic and turn it into working code.

The values in this multi-select listbox were entered when I placed the listbox on the form.

The code above is for a form with text boxes for the criteria of a sql select query. This works wonderfully.

How would I modify the code above to also take the values of a multi-select listbox using an 'or' between the selections as well as the values from the text boxes which currently uses an 'and' with great results.

I've been close but no cigar.

v/r,

Aaron
Dec 2 '08 #3

Expert 100+
P: 1,287
I just figured out what you want, but I won't be able to post until tomorrow.
Dec 2 '08 #4

P: 38
The listbox values are found in the [STATUS] field of the main table.

v/r,

Aaron
Dec 2 '08 #5

P: 38
@ChipR
I certainly do appreciate your time in helping me and look forward to learning where it is I've gone wrong.

It's a shame I can't just paste my code, I'm certain that I've almost got it but between output containing double ' marks or missing one altogether it's killing me. I thought to myself that I should quit wasting my time in the immediate window and get a correct answer and reverse engineer why it fails.

v/r,

Aaron
Dec 2 '08 #6

Expert 100+
P: 1,287
You could paste this in after all the text boxes are checked, might want to put parenthesis around the AND parts of the WHERE generated by the text boxes, because SQL can surprise you with how it evaluates mixed ANDs and ORs.

Expand|Select|Wrap|Line Numbers
  1.   i = 0
  2.   while i < myListBox.ListCount 
  3.     if myListBox.selected(i) then 
  4.       'you could use a function similar to AddToWhere 
  5.       MyCriteria = MyCriteria & " OR Status = """ & myListBox.column(0,i) & """"
  6.     end if 
  7.     i = i + 1 
  8.   wend
  9.  
This assumes that the ListBox values are strings. The 2 double quotes result in 1 in the string so you would get _OR Status = "value"_ appended to your criteria for each one selected. You could use single quotes instead, but I use double quotes because it allows apostrophes in my strings.
Dec 3 '08 #7

P: 38
I wish I could simply paste it in. I have to print everything and type it into a machine not connected to the world.

Gonna give this a shot, thanks again Chip.

v/r,

Aaron
Dec 3 '08 #8

P: 38
@ChipR
It threw a syntax error.

Using immediate to print myrecordsource after selecting one status item (COMPLETED) in the listbox (Find8) the resulting query is

select * from [jcahold] where OR Status = "COMPLETED"

If I select two items the resulting query is

select * from [jcahold] where OR status = "COMPLETED" or Status = "DELETED"

So it's seems like the initial OR doesn't need to be there but I understand it's necessary for a multi-select. Any ideas?

v/r,

Aaron
Dec 3 '08 #9

Expert 100+
P: 1,287
Ah I was thinking this would be after some AND conditions generated by the text boxes, but of course if there aren't any, that won't work. We can easily modify it this way not to paste in the OR on the first item:

Expand|Select|Wrap|Line Numbers
  1.   count = 0
  2.   i = 0 
  3.   while i < myListBox.ListCount  
  4.     if myListBox.selected(i) then  
  5.       count = count + 1
  6.       if count > 1 then
  7.         MyCriteria = MyCriteria & " OR "
  8.       end if
  9.       MyCriteria = MyCriteria & "Status = """ & myListBox.column(0,i) & """" 
  10.     end if  
  11.     i = i + 1  
  12.   wend
Dec 3 '08 #10

P: 38
Well at some point I'm sure the user will want to use the other text boxes too, will this newest change matter if those are being used or not?

Let me go hand jam it in now and see what we get.

I can't thank you enough for your excellent help Chip.

v/r,

Aaron
Dec 3 '08 #11

P: 38
@ChipR
Error:
This property is read only and can't be set.

This happens if I use the multi-select listbox or not.
I've double checked the code.

v/r,

Aaron
Dec 3 '08 #12

Expert 100+
P: 1,287
This code will form an appropriate WHERE clause by itself. If you have to append the text box conditions before or after it, you will have to put in an OR or AND accordingly, and I can help you figure it out if you decide you need to do that.
I had to go back and edit that code because I messed up a copy & paste, so make sure you didn't get it before the edit.
It may help to step through the code using AddToWhere and the one I just posted and watch the MyCriteria string being constructed.
When you figure out what the resulting string is that you want, it's just a little tricky to automate it in code.
Dec 3 '08 #13

Expert 100+
P: 1,287
Where's the error at? There's nothing in the code that sets any properties. Unless count is being used for something else?
Dec 3 '08 #14

Expert 100+
P: 1,287
You can put a break at the beginning of the function and step through and see where it jumps to the error handling code.
Dec 3 '08 #15

P: 38
Oh man it's soooo close.

Added DIM Count as Int and Dim I as int, I think that clear the read-only thing.

So I made a couple of text box entries to search for and also selected two items from the list box.

I also added an " and in the second mycriteria statement.

Expand|Select|Wrap|Line Numbers
  1. mycriteria = mycriteria & " and STATUS = """ ... 
The resulting query is still throwing a syntax.

here's what myrecordsource looks like now.
This is with two text box entries and two multi-selects.

select * from [jcahold] where [NOMEN] = "STRYKER" and [AUTL] = "KABUL" and [STATUS] = "COMPLETED" OR and [STATUS] = "DELETED"

v/r,

Aaron
Dec 3 '08 #16

Expert 100+
P: 1,287
Yeah that "and" won't work. Instead, do this after you process your text boxes.

if ArgCount > 0 then
mycriteria = mycriteria & " AND "
end if

Let's put in parenthesis around the list box clauses also.

Expand|Select|Wrap|Line Numbers
  1.   count = 0 
  2.   i = 0  
  3.   while i < myListBox.ListCount   
  4.     if myListBox.selected(i) then   
  5.       count = count + 1 
  6.       if count = 0 then    'changed!
  7.         mycriteria = mycriteria & "("
  8.       else
  9.         MyCriteria = MyCriteria & " OR " 
  10.       end if  'to here!
  11.       MyCriteria = MyCriteria & "Status = """ & myListBox.column(0,i) & """"  
  12.     end if   
  13.     i = i + 1   
  14.   wend 
  15.   if count > 0 then 'Added this!
  16.     mycriteria = mycriteria & ")"
Dec 3 '08 #17

P: 38
@ChipR
I think there was a missing end if in there.
Also the parenthesis didn't quite come out right, only the trailing paren was getting rendered. so I commented that out for now.

One text box selection "boots"
two multi-selects "COMPLETED" "DELETED"

myrecordsource results in .

select * from [jcahold] where [NOMEN] = "boots" OR [STATUS] = "COMPLETE" OR "DELETED"

This is nuts. I guess I'll print out the entirety of the code on the SECRET workstation and type it here, I feel bad for taking up your time Chip.

v/r,

Aaron

Here's the code exactly as I'm using it.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command2_Click()
  2. On Error goto err_view_click
  3. dim mysql as string, mycriteria as string, myrecordsource as string, strwhere as string
  4. dim argcount as integer
  5. dim ctl as control
  6. dim varitem as variant
  7. dim count as integer
  8. dim i as integer
  9.  
  10. mysql = "Select * From [jcahold] where "
  11. Addtowhere [Find1], "[ONS ID]", mycriteria, argcount
  12. Addtowhere [Find2], "[NOMENCLATURE]", mycriteria, argcount
  13. count = 0
  14. i = 0
  15. while i < Find8.ListCount
  16. If find8.selected(i) then
  17. count = count + 1
  18. if count = 0 then 'changed!
  19. mycriteria = mycriteria & "("
  20. else
  21. mycriteria = mycriteria & " OR "
  22. end if 'to here!
  23. mycriteria = mycriteria & "[STATUS] = """ & Find8.Column(0,1) & """"
  24. end if
  25. i = i + 1
  26. wend
  27. if mycriteria = "" then
  28. mycriteria = "True"
  29. end if
  30.  
  31. myrecordsource = mysql & mycriteria
  32.  
  33. exit_view_click:
  34. exit sub
  35. err_view_click:
  36. msgbox error$
  37. resume exit_view_click
  38. end sub
  39.  
The addtowhere module is as follows:
Expand|Select|Wrap|Line Numbers
  1. Public Sub AddtoWhere(FieldValue as variant, fieldname as string, mycriteria as string, argcount as integer)
  2. if fieldvalue <> "" then
  3. if argcount > 0 then
  4. mycriteria = mycriteria & " and "
  5. end if
  6. mycriteria = (mycriteria & fieldname & " Like " & Chr(39) & fieldvalue & chr(42) & chr(39))
  7. argcount = argcount + 1
  8. end if
  9. end sub
  10.  
I sure hope this helps.

v/r,

Aaron
Dec 3 '08 #18

Expert 100+
P: 1,287
Yeah that missing "status = " looks like it's probably the result of the end if. I have enough trouble copying and pasting code, I'd hate to have to print it out and type it.
I'm happy to help, and I work for the army also, so what's the difference :) Luckily they are still only talking about getting us separate computers to run on the secure network.
Dec 3 '08 #19

P: 38
@ChipR
Yeah it's a developers nightmare. No google, wow what a crutch it really is ... nothing but one big intranet so resources are extremely limited.

I have hand-jammed all of the actual code into my post above.

Let me kick it around some more, I know it's on the brink of working right.

v/r,

Aaron
Dec 3 '08 #20

P: 38
Well I see what you mean about the parenthesis now.

I've got it working but now the results are not coming out right.

I've gotta get the parens in there somehow I guess.

I constructed a new query and looked at the sql statement that access made and there are a lot of parenthesis in it.

oy vey....
Dec 3 '08 #21

Expert 100+
P: 1,287
Ah I see my mistake
I did

count = count + 1
if count = 0

Duh, that should be

if count = 1

Also, it looks like you have
mycriteria = mycriteria & "[STATUS] = """ & Find8.Column(0,1) & """"
but it should be
mycriteria = mycriteria & "[STATUS] = """ & Find8.Column(0,i) & """"
Dec 3 '08 #22

Expert 100+
P: 1,287
Check out these changes, should work.

Private Sub Command2_Click()
On Error goto err_view_click
dim mysql as string, mycriteria as string, myrecordsource as string, strwhere as string
dim argcount as integer
dim ctl as control
dim varitem as variant
dim count as integer
dim i as integer

mysql = "Select * From [jcahold] where "
Addtowhere [Find1], "[ONS ID]", mycriteria, argcount
Addtowhere [Find2], "[NOMENCLATURE]", mycriteria, argcount
count = 0
i = 0
while i < Find8.ListCount
If find8.selected(i) then
count = count + 1
if count = 1 then 'changed!
mycriteria = mycriteria & " AND ("
else
mycriteria = mycriteria & " OR "
end if 'to here!
mycriteria = mycriteria & "[STATUS] = """ & Find8.Column(0,i) & """"
end if
i = i + 1
wend
if count > 0 then mycriteria = mycriteria & ")"
if mycriteria = "" then
mycriteria = "True"
end if

myrecordsource = mysql & mycriteria

exit_view_click:
exit sub
err_view_click:
msgbox error$
resume exit_view_click
end sub
Dec 3 '08 #23

P: 38
It's an i in the code.

I made the change regarding count.

The query looks good now in immediate window but the results are not right.

I'm guessing it's because of the lack of parens.

When I use the gui to make a query the sql select looks like this...
Expand|Select|Wrap|Line Numbers
  1. select jcahold.UICNEEDUNITNAME, jcahold.STATUS
  2. FROM jcahold
  3. WHERE (((jcahold.UICNEEDUNITNAME) like "0001*" AND ((jcahold.status)="COMPLETED" or (jcahold.status)="DELETED"))
  4.  
The results are exactly as I expected.

BUT

When I use this 'kicking my ass for two days' vb routine using the same field values as above, the query is coming back as

select * from jcahold where ((jcahold.nomenclature) like 'boot*' and [STATUS] = "COMPLETED" or [STATUS] = "DELETED"

Of course the results are completely different via the vb query, it's like it just disregards my nomenclature qualifier.

I can't believe how difficult this is. grrr.
Dec 3 '08 #24

P: 38
@ChipR
Ok let me type this in over there.

/fingers freakin' crossed!!!

v/r,

Aaron
Dec 3 '08 #25

Expert 100+
P: 1,287
It looks like those parenthesis are the result of the AddToWhere function. What if you change
mycriteria = (mycriteria & fieldname & " Like " & Chr(39) & fieldvalue & chr(42) & chr(39))
to
mycriteria = mycriteria & fieldname & " LIKE """ & fieldvalue & """"
Dec 3 '08 #26

P: 38
@ChipR
#)($*&@#

Changes made.

If I type one item in the find1 field 'boots'
then I select one thing in the multi-select the results are flawless.

As soon as I select the second item in the multi-select the results all contain one of those two multi-selects, but the 'boots' qualifier is ignored.
Dec 3 '08 #27

Expert 100+
P: 1,287
So close!
What's the generated SQL string look like that gives the bad results?
Dec 3 '08 #28

P: 38
@ChipR
Stand by while I type everything in again as I've added some parens here and there too.

v/r,

Aaron
Dec 3 '08 #29

P: 38
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command2_click()
  2. on error goto err_view_click
  3. dim mysql as string, mycriteria as string, myrecordsource as string, strwhere as string
  4. dim argcount as integer
  5. dim ctl as control
  6. dim varitem as variant
  7. dim count as integer
  8. dim i as integer
  9.  
  10. mysql = select * from [jcahold] where ("
  11. addtowhere [find1], "[NOMENCLATURE])", mycriteria, argcount
  12. addtowhere [find2], "[UNIT])", mycriteria, argcount
  13.  
  14. count = 0
  15. i = 0
  16. while i < find8.listcount
  17. if find8.selected(i) then
  18. count = count + 1
  19. if count = 1 then
  20. mycriteria = mycriteria & " AND ("
  21. else
  22. mycriteria = mycriteria & " OR ("
  23. end if
  24. mycriteria = mycriteria & "[STATUS]) = """ & Find8.column(0,i) & """"
  25. end if
  26. i = i + 1
  27. Wend
  28.  
  29. if mycriteria = "" Then
  30. mycriteria = "True"
  31. end if
  32.  
  33. 'create select statement
  34. myrecordsource = mysql & mycriteria
  35.  
  36. exit_view_click:
  37. exit sub
  38. err_view_click:
  39. msgbox error$
  40. resume exit_view_click
  41. end sub
  42.  
AddtoWhere module:
Expand|Select|Wrap|Line Numbers
  1. Public Sub AddtoWhere(Fieldvalue as variant, fieldname as string, mycriteria as string, argcount as integer)
  2. if fieldvalue <> "" Then
  3. if argcount > 0 Then
  4. mycriteria = "(" & mycriteria & ")" & " And "
  5. end if
  6. mycriteria = mycriteria & fieldname & " Like " & """ & Fieldvalue & """"
  7. argcount = argcount + 1
  8. end if
  9. end sub
  10.  
When putting 0001 in the unit text box and multi-select (Find8) has two selections. "COMPLETED" and "DELETED"

The resulting myrecordsource is the following:
Expand|Select|Wrap|Line Numbers
  1. Select * From [jcahold] where ([UNIT]) Like '0001*' and ([STATUS]) = "COMPLETED" OR ([STATUS]) = "DELETED" 
wow that's alot to type, what a PITA!

Hope this helps Chip.

v/r,

Aaron
Dec 3 '08 #30

Expert 100+
P: 1,287
Yeah some of the parenthesis are misplaced. All we want to do is say, if there is any listbox stuff selected, put a left paren before the first one, and one at the end. The rest should be unnecessary. Try these changes.

mysql = select * from [jcahold] where "
addtowhere [find1], "[NOMENCLATURE]", mycriteria, argcount
addtowhere [find2], "[UNIT]", mycriteria, argcount


count = 0
i = 0
while i < find8.listcount
if find8.selected(i) then
count = count + 1
if count = 1 then
mycriteria = mycriteria & " AND ("
else
mycriteria = mycriteria & " OR "
end if
mycriteria = mycriteria & "[STATUS] = """ & Find8.column(0,i) & """"
end if
i = i + 1
Wend

if count > 0 then
mycriteria = mycriteria & ")"
end if


if mycriteria = "" Then
mycriteria = "True"
end if

'create select statement
myrecordsource = mysql & mycriteria

exit_view_click:
exit sub
err_view_click:
msgbox error$
resume exit_view_click
end sub


Public Sub AddtoWhere(Fieldvalue as variant, fieldname as string, mycriteria as string, argcount as integer)
if fieldvalue <> "" Then
if argcount > 0 Then
mycriteria = mycriteria & " And "
end if
mycriteria = mycriteria & fieldname & " Like " & """ & Fieldvalue & """"
argcount = argcount + 1
end if
end sub
Dec 3 '08 #31

P: 38
Alrighty, lemme go give this a spin!
Dec 3 '08 #32

P: 38
It's operating at 95% now Chip!

It barfs when I don't have anything in the text boxes but have multi-select choices.

The query comes out like this;

select * from [jcahold] where AND ([STATUS] = "COMPLETED" OR [STATUS] = "DELETED")

I can see the light at the end of the tunnel.
Of course owing it all to you sir.

Maybe I can get the Army to pay your invoice! =P

v/r,

Aaron
Dec 3 '08 #33

Expert 100+
P: 1,287
Darn, I forgot about that. Change

if count = 1 then
mycriteria = mycriteria & " AND ("
else

to

if count = 1 then
if argcount > 0 then
mycriteria = mycriteria & " AND "
end if
mycriteria = mycriteria & " ("
else

That way it puts that AND in there if there was stuff in the textboxes, otherwise skips it!
Dec 3 '08 #34

P: 38
@ChipR
Oh Dear LORD it's working!

Chip do you have a paypal address?
I really want to compensate you somehow.

You have been so very helpful and I can't thank you enough!!
Dec 3 '08 #35

Expert 100+
P: 1,287
Glad to hear it. Just know that next time I have to do this for my own stupid forms (and I will) it will already be done :)
Dec 3 '08 #36

P: 38
@ChipR
Haha! One less tasker!

Karma will pay you ten-fold my friend.
All the best to you and yours.

v/r,

Aaron
Dec 3 '08 #37

Post your reply

Sign in to post your reply or Sign up for a free account.