Search + Multi Select Box

[aaronkmar]

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

------------------------ Put his code behind the button to search -------
 
Private Sub VIEW_Click()
'On Error GoTo Err_VIEW_Click
‘------------------------------ Starting here ----------------------
    Dim MySQL As String, MyCriteria As String, MyRecordSource As String
    Dim ArgCount As Integer
    '  Initialize SELECT statement.
    MySQL = "SELECT * FROM [PhoneLog] WHERE "
 
    '  Use values entered in text boxes in form header to create criteria for WHERE clause.
        'text box name on form     'Field name in Table  'blank info ' number of times run Addt
    AddToWhere [Find1], "[CustomerName]", MyCriteria, ArgCount
    AddToWhere [Find2], "[City]", MyCriteria, ArgCount
 
    '  If no criterion specifed, return all records.
    If MyCriteria = "" Then
        MyCriteria = "True"
    End If
 
    '  Create SELECT statement.
    MyRecordSource = MySQL & MyCriteria
 
      ' set record source to Subform
    Me![YOURsubform].Form.RecordSource = MyRecordSource
-------------------------------------------------------------    
 
Exit_VIEW_Click:
    Exit Sub
 
Err_VIEW_Click:
    MsgBox Error$
    Resume Exit_VIEW_Click
 
End Sub
 
--------------------------------------------------------------
    This is the AddToWhere SUB "copy and paste in a module"
--------------------------------------------------------------
Public Sub AddToWhere(FieldValue As Variant, FieldName As String, MyCriteria As String, ArgCount As Integer)
 
    '  Create criteria for WHERE clause.
    If FieldValue <> "" Then
        '  Add "and" if other criterion exists.
        If ArgCount > 0 Then
            MyCriteria = MyCriteria & " and "
        End If
 
        '  Append criterion to existing criteria.
        '  Enclose FieldValue and asterisk in quotation marks.
        MyCriteria = (MyCriteria & FieldName & " Like " & Chr(39) & FieldValue & Chr(42) & Chr(39))
 
        '  Increase argument count.
        ArgCount = ArgCount + 1
    End If
End Sub

Very Respectfully,

Aaron M.
US ARMY

[ChipR]

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.

    dim i as integer
 
    i = 0
    while i < myListBox.ListCount
        if myListBox.selected(i) then
            AddToWhere myListBox.column(0,i), _
             & myListBox.column(1,i), MyCriteria, ArgCount
        end if
        i = i + 1
    wend

[aaronkmar]

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

[ChipR]

I just figured out what you want, but I won't be able to post until tomorrow.

[aaronkmar]

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

v/r,

Aaron

[aaronkmar]

@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

[ChipR]

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.

  i = 0
  while i < myListBox.ListCount 
    if myListBox.selected(i) then 
      'you could use a function similar to AddToWhere 
      MyCriteria = MyCriteria & " OR Status = """ & myListBox.column(0,i) & """"
    end if 
    i = i + 1 
  wend
 

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.

[aaronkmar]

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

[aaronkmar]

@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

[ChipR]

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:

  count = 0
  i = 0 
  while i < myListBox.ListCount  
    if myListBox.selected(i) then  
      count = count + 1
      if count > 1 then
        MyCriteria = MyCriteria & " OR "
      end if
      MyCriteria = MyCriteria & "Status = """ & myListBox.column(0,i) & """" 
    end if  
    i = i + 1  
  wend

[aaronkmar]

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

[aaronkmar]

@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

[ChipR]

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.

[ChipR]

Where's the error at? There's nothing in the code that sets any properties. Unless count is being used for something else?

[ChipR]

You can put a break at the beginning of the function and step through and see where it jumps to the error handling code.

[aaronkmar]

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.

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

[ChipR]

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.

  count = 0 
  i = 0  
  while i < myListBox.ListCount   
    if myListBox.selected(i) then   
      count = count + 1 
      if count = 0 then    'changed!
        mycriteria = mycriteria & "("
      else
        MyCriteria = MyCriteria & " OR " 
      end if  'to here!
      MyCriteria = MyCriteria & "Status = """ & myListBox.column(0,i) & """"  
    end if   
    i = i + 1   
  wend 
  if count > 0 then 'Added this!
    mycriteria = mycriteria & ")"

[aaronkmar]

@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.

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 = 0 then 'changed!
mycriteria = mycriteria & "("
else
mycriteria = mycriteria & " OR "
end if 'to here!
mycriteria = mycriteria & "[STATUS] = """ & Find8.Column(0,1) & """"
end if
i = i + 1
wend
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
 

The addtowhere module is as follows:

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 " & Chr(39) & fieldvalue & chr(42) & chr(39))
argcount = argcount + 1
end if
end sub
 

I sure hope this helps.

v/r,

Aaron

[ChipR]

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.

[aaronkmar]

@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

[aaronkmar]

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....

[ChipR]

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) & """"

[ChipR]

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

[aaronkmar]

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...

select jcahold.UICNEEDUNITNAME, jcahold.STATUS
FROM jcahold
WHERE (((jcahold.UICNEEDUNITNAME) like "0001*" AND ((jcahold.status)="COMPLETED" or (jcahold.status)="DELETED"))
 

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.

[aaronkmar]

@ChipR
Ok let me type this in over there.

/fingers freakin' crossed!!!

v/r,

Aaron

[ChipR]

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 & """"

[aaronkmar]

@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.

[ChipR]

So close!
What's the generated SQL string look like that gives the bad results?

[aaronkmar]

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

v/r,

Aaron

[aaronkmar]

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], "[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 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
 

AddtoWhere module:

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
 

When putting 0001 in the unit text box and multi-select (Find8) has two selections. "COMPLETED" and "DELETED"

The resulting myrecordsource is the following:

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

[ChipR]

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

[aaronkmar]

Alrighty, lemme go give this a spin!

[aaronkmar]

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

[ChipR]

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!

[aaronkmar]

@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!!

[ChipR]

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 :)

[aaronkmar]

@ChipR
Haha! One less tasker!

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

v/r,

Aaron