ListBox Search Criteria

[GendoPose]

Hi all,

I have a search form that uses several comboboxes, textboxes and checkboxes that are used as criteria in a query. You enter in the relevant information, hit search and a report opens based on the filtered query.

What I would like to do is change one of these combo boxes to a listbox and use the multiple selection as the query criteria. I know it's not as simple as just putting the listbox as a criteria in the query, and I've also tried many different variations on the varItem and strWhere code.

Have you guys got any ideas I can try out?

Thanks

 

[pr2-eugin]

something along the lines of..

    Dim iCtr As Variant, whrStr As String
    
[COLOR=Green]    'fieldList is the name of the ListBox ![/COLOR]
    For Each iCtr In Me.fieldList.ItemsSelected
        whrStr = whrStr & Me.fieldList.ItemData(iCtr) & ", "
    Next
    
    whrStr = Left(whrStr, Len(whrStr)-2)
    
    [COLOR=Green]'initialize your WHERE condition : [/COLOR]
    whrStr = "WHERE someNumberFieldName In(" & whrStr & ")"

 

[GendoPose]

something along the lines of..

    Dim iCtr As Variant, whrStr As String
 
[COLOR=green]   'fieldList is the name of the ListBox ![/COLOR]
    For Each iCtr In Me.fieldList.ItemsSelected
        whrStr = whrStr & Me.fieldList.ItemData(iCtr) & ", "
    Next
 
    whrStr = Left(whrStr, Len(whrStr)-2)
 
    [COLOR=green]'initialize your WHERE condition : [/COLOR]
    whrStr = "WHERE someNumberFieldName In(" & whrStr & ")"

So assuming this goes on command button, I've added DoCmd.OpenQuery after this code so I can view the results, however I'm stuck. Firstly, I'm not sure what to replace the "someNumberFieldName" with, and also when I click the button, the query doesn't open?

 

[pr2-eugin]

Show the code you have come up with so far ! Or explain on what field are you planning to use the listbox on, and what is its type? What are the fields involved?

 

[GendoPose]

Show the code you have come up with so far ! Or explain on what field are you planning to use the listbox on, and what is its type? What are the fields involved?

Option Explicit
Option Compare Database
    Dim iCtr As Variant
    Dim whrStr As String
    
Private Sub Command158_Click()
    For Each iCtr In Me.MAIL_PROVIDER.ItemsSelected
        whrStr = whrStr & Me.MAIL_PROVIDER.ItemData(iCtr) & ", "
    Next
    
    whrStr = Left(whrStr, Len(whrStr) - 2)
    
    whrStr = "WHERE [MAIL PROVIDER] In(" & whrStr & ")"
    DoCmd.OpenQuery "Postage Search2"
End Sub

The field is a Multiple Select (Simple) ListBox with 2 columns, ID (an autonumber primary key) and MAIL PROVIDER (text).

The button does load the form when I select something in the listbox but it doesn't filter for those results, it just shows the query as if I opened it without any criteria.

 

[pr2-eugin]

Thats a good start, now

explain on what field are you planning to use the listbox on, and what is its type?

 

[GendoPose]

Thats a good start, now

Sorry to be slow, but I don't really get what you mean, the field type in the underlying table is a lookup field, which looks up a text field, if that's what you mean. The listbox name and the field in table have the same name too (MAIL PROVIDER).

I know it's bad design to have lookup fields in tables, but the design of this database started several months ago and back then me and the other developer were very novice access users, and it's only recently that we've improved to the point where we realise it's a bad idea, but it does seem a little too late to change considering the number of users that use the backend.

 

[pr2-eugin]

Okay, your Query is a precompiled Query in other words, the filters (if you had any) are pre defined, and saved. So you have to Edit the SQL, using code,

Option Explicit
Option Compare Database
    
Private Sub Command158_Click()
    Dim iCtr As Variant, selStr As String, whrStr As String
    Dim qryObj As QueryDef
    
    Set qryObj = CurrentDB.QueryDefs("Postage Search2)
    selStr = qryObj.SQL
    
    selStr = Mid(selStr, 1, InStr(selStr, "WHERE") - 1)
    
    For Each iCtr In Me.MAIL_PROVIDER.ItemsSelected
        whrStr = whrStr & Me.MAIL_PROVIDER.ItemData(iCtr) & ", "
    Next
    
    whrStr = Left(whrStr, Len(whrStr) - 2)
    
    whrStr = "WHERE [MAIL PROVIDER] In(" & whrStr & ")"
    
    qryObj.SQL = selStr & " " & whrStr
    Set qryObj = Nothing
    
    DoCmd.OpenQuery "Postage Search2"
End Sub

The above code will replace any where you have already created using th Design view of the Query. If you want them included, you have to use more code to add them too.

NOTE : Code Not Tested !

 

[GendoPose]

Okay, your Query is a precompiled Query in other words, the filters (if you had any) are pre defined, and saved. So you have to Edit the SQL, using code,

Option Explicit
Option Compare Database
 
Private Sub Command158_Click()
    Dim iCtr As Variant, selStr As String, whrStr As String
    Dim qryObj As QueryDef
 
    Set qryObj = CurrentDB.QueryDefs("Postage Search2)
    selStr = qryObj.SQL
 
    selStr = Mid(selStr, 1, InStr(selStr, "WHERE") - 1)
 
    For Each iCtr In Me.MAIL_PROVIDER.ItemsSelected
        whrStr = whrStr & Me.MAIL_PROVIDER.ItemData(iCtr) & ", "
    Next
 
    whrStr = Left(whrStr, Len(whrStr) - 2)
 
    whrStr = "WHERE [MAIL PROVIDER] In(" & whrStr & ")"
 
    qryObj.SQL = selStr & " " & whrStr
    Set qryObj = Nothing
 
    DoCmd.OpenQuery "Postage Search2"
End Sub

The above code will replace any where you have already created using th Design view of the Query. If you want them included, you have to use more code to add them too.

NOTE : Code Not Tested !

Ok, I'll give this a go when I get a chance. To add in the criteria for regular textboxes and comboboxes, how would I add that to the query using VBA?

 

[GendoPose]

So I've tried to implement this code and nothing seems to work, the button doesn't open the query whether I change the list box to single or multi select and whether something is selected or not, I've tried typing in the code by hand and copying and pasting, but I can't seem to get this working, any ideas?

Thanks for the help so far

 

[pr2-eugin]

Could you upload a Stripped DB?

How to Upload a Stripped DB.

To create a Sample DB (to be uploaded for other users to examine); please follow the steps..

1. Create a backup of the file, before you proceed..
2. Delete all Forms/Queries/Reports that are not in Question (except the ones that are inter-related)
3. Delete auxiliary tables (that are hanging loose with no relationships).
4. If your table has 100,000 records, delete 99,990 records.
5. Replace the sensitive information like Telephone numbers/email with simple UPDATE queries.
6. Perform a 'Compact & Repair' it would have brought the Size down to measly KBs..
7. (If your Post count is less than 10 ZIP the file and) Upload it..

Finally, please include instructions of which Form/Query/Code we need to look at. The preferred Access version would be A2003-A2007 (.mdb files)

 

[GendoPose]

I've removed everything except the relevant form and query (and the underlying tables).

What should happen is that the user enters their criteria into the search form and then hits Postage Report to view the results, except that won't happen in this as it now runs off of the query which has no criteria in, except for what I'm trying to achieve here.

 

[pr2-eugin]

I have only given you a head start ! But I have to comment on a few issues.

• Your DB Structure is highly De-Normalized. I suggest you do some research on this topic, work some tutorials, to get the idea of how a Normalized structure can make life with Databases so much easier.
• You have so many Lookup's this is a strict NO NO in Access, I know MS built this feature in, but that's only for amateur design, a proper DB design will not have this !
• You have attachment fields in your tables, this will BLOAT your DB very quickly. You can look into Hyperlinks as alternatives.
• Stick to a good Naming convention.

So try the code, it could be made more robust. This is just a start !

 

[GendoPose]

I have only given you a head start ! But I have to comment on a few issues.

• Your DB Structure is highly De-Normalized. I suggest you do some research on this topic, work some tutorials, to get the idea of how a Normalized structure can make life with Databases so much easier.
• You have so many Lookup's this is a strict NO NO in Access, I know MS built this feature in, but that's only for amateur design, a proper DB design will not have this !
• You have attachment fields in your tables, this will BLOAT your DB very quickly. You can look into Hyperlinks as alternatives.
• Stick to a good Naming convention.

So try the code, it could be made more robust. This is just a start !

Oh trust me I do understand all of that now, me and the other developer have improved so much that we're thinking it could be worthwhile re-designing the entire thing, especially with the lookups. The attachment fields are more meant for uploading small emails and pdf's so we can quickly see user-documents that have been uploaded, but i'll definitely look into hyperlinks.

Thank you so much for this, it works perfectly so now I just need to see if I can implement it into the full version without breaking it!

 

[pr2-eugin]

The attachment fields are more meant for uploading small emails and pdf's so we can quickly see user-documents that have been uploaded, but i'll definitely look into hyperlinks.

If you want to use Attachments, use them in a separate table atleast. But look for alternatives ! Few JPEGS/Files will bloat it quickly.

Thank you so much for this, it works perfectly so now I just need to see if I can implement it into the full version without breaking it!

Glad to help ! If you get stuck post back ! Good luck !