Search Form

[orange]

When you did the search with 2 company names in the Criteria, everything worked ok, right?


When you do the second search, now looking for lastname, not company name, it still uses the Company name criteria?

Your Clear_Click event code looks ok to me.

Your BuildFilter code has this hard coded

strSQL = "SELECT * FROM [Account List] " & _ "WHERE [Account List].[Company Name] IN(" & strCriteria & ");"

so it's always looking for [company name]

[cbrsix]

1) Yes
2) Yes, but does filter it more if the last name also has the same company criteria

[orange]

I'm not following your point #2.

Can you save a copy of your database in mdb format--- I have access 2003 and can not open/use an accdb format database?

[cbrsix]

Has nothing selected
Just Company selected
Company and name selected
Cleared Button clicked
just name selected no company (this should return more data than this as I know this person has more than 2 accounts)

[cbrsix]

Let me try and make this a little bit clearer. For my Clear_Click() event, I need to know of a way to clear out the criteria from the query that was put in from the listbox. (see the pic)


This is what is messing me up. This is only a problem after I choose something from my listbox.

[orange]

I tried mocking up a small database last night. There is a syntax error in this part of your code

"WHERE [Account List].[Company Name] IN(" & strCriteria & ");"

You need an extra space before the W in Where.

Can you save a copy of your database in mdb format--- I have access 2003 and can not open/use an accdb format database?

[cbrsix]

You need an extra space before the W in Where.

I just tried this and it didn't have an effect on it. I no longer get the invalid code as before. I now only have the problem of removing the criteria from the query as mentioned in my last post.

[orange]

Is this still the Search query sql?
In your code it was
qdf.sql

If so, then something is not overwriting the qdf.sql in some cases. I suggest you put some debug.print statements in and see what is the qdf.sql at various points. Step through the code as needed.

It's hard to debug this without seeing the form/subform info and not knowing the details of the application.

[ssanfu]

PMFJI,
If you are having trouble clearing a multi-select list box, here is some code to clear the selections:

'clear multiselect list box
Sub ClearListBox()
   Dim theList As Control
   Dim vItem
   Dim n As Long

   'change "Company Name" to your list box name
   Set theList = Me("Company Name")

   For n = 0 To theList.ListCount
      theList.Selected(n) = False
   Next
End Sub

[cbrsix]

I want to thank everyone that provided help to me on this problem. I was able to get it to work exactly how I want. I am not quite sure what I did differently but I restarted the listbox project from the beginning and now it functions appropriately.

Thanks again.