Multi-Select LIstbox can it be searched easily?

[vb9063]

Hi guys,
I have a set of services that are repeated over postal areas. I would like to find out whether it is better for searching the database whether to have a multiselect listbox when the user enters the information or to have a line for each service repeated for each postal area.

Option one:

Service | Type | Postal Area |
removal | 3 | AB |
removal | 3 | CB |

Option two:
Service | Type | Postal Area |
removal | 3 | AB, CB, AL |
removal | 4 | GL, FK |


Having the postcodes for each service in a multiselect box would be great but I am not sure whether this is easy for searching purposes. I am having trouble trying to figure out how to do queries based on listboxes and multiselects.

Any help would be great!
CHeers
VB

 

[vbaInet]

Things to consider:

With option 2 you may end up with a long list of postal areas for one type and that might over-stretch the pop-up list. If however, you know that there are a small number of postcodes and the possibility of having more than (say) 5 postcodes for one Type is unlikely, then option 2 would be the way to go.

However, you should save your data as option 1. Then you can use a query to concatenate the related types to option 2. Here's how:

http://www.mvps.org/access/modules/mdl0008.htm

 

[vb9063]

Hi VbaInet,
Thanks very much for the response. I have the services table as a subform (show in datasheet view) within a suppliers form so that the user can see what services the supplier has. If the postal area info is saved as option 1 then the table would be very long in the supplier form. So option 2 looks better when the user views the supplier form and for data entry. Am a little confused as to where you would use the concatenate query. Is it easier to find a way to search the database with the multiselect box. I have been using Allen Browne's code but cant figure out how to make it work http://allenbrowne.com/ser-50.html, http://allenbrowne.com/ser-62.html I think it is just getting more confusing!

Thanks again,
VB

 

[vbaInet]

I think you haven't gotten the gist of my suggestions.

1. With option 2 there's a possibility that you could end up with
Option two:
Service | Type | Postal Area |
removal | 3 | AB, CB, AL, AA, AZ, BB, CZ, FF, GG, HH, HI, KI, KL, MZ, MP, PP, PI, TT, SS |
removal | 4 | GL, FK |

How do you think that would look? That was why I said, "If however, you know that there are a small number of postcodes and the possibility of having more than (say) 5 postcodes for one Type is unlikely, then option 2 would be the way to go." So the question is, is there going to be a maximum number of postcodes per Type?

 

[vb9063]

Hi, the maximum number of postcodes per service would be the whole country which I is 124, which I think is possible if a service is across the whole country...what do you mean by over-stretching the pop-up list??
Thanks again,VB

 

[vbaInet]

Visualise 124 postcodes on one row in a list box. It wouldn't be very easy to read don't you think?

You could show all the postcodes related to that service area in a textbox instead. So when a user clicks on a service type the related postcodes (all concatenated) would show in the textbox.

 

[vb9063]

I suppose the main point is to ensure that the postcodes can be searched, ie if i do a search I want to search for a service and find out whether it is covered in a certain postcode or group of postcodes. It doesnt matter if the datasheet has a long list as long as it can be searched..because at the moment if you click on the button on the postcode field it pops up with a checkbox list...does that make any sense

 

[vb9063]

the ideal solution would be a map pop up but would take a few years before i could learn to do that i think!

 

[vbaInet]

I suppose the main point is to ensure that the postcodes can be searched, ie if i do a search I want to search for a service and find out whether it is covered in a certain postcode or group of postcodes. It doesnt matter if the datasheet has a long list as long as it can be searched..because at the moment if you click on the button on the postcode field it pops up with a checkbox list...does that make any sense

I see. That brings us back to the point I made in post #2. For searching purposes you would need a long list, hence, it should be saved as Option 1, but presented as Option 2 (and this is where that link I gave you comes into play).

 

[vbaInet]

the ideal solution would be a map pop up but would take a few years before i could learn to do that i think!

I'm sure you're capable. You will be able to do it in months with lots of hard work Doing it in Access would be a tedious task. A web-based solution would be ideal.

 

[vb9063]

I'm sure you're capable. You will be able to do it in months with lots of hard work Doing it in Access would be a tedious task. A web-based solution would be ideal.

Ha ha, thanks! I started learning access a month ago, so learning lots but in a very unstructured way! It would be good to have a web based solution that would be the aim, would you use SQL and vba.net, am still not quite there with understanding what it all is about!?

 

[vb9063]

Would you use that concatenate code to fill in the postal area code in the datasheet view of the subform for services? How would you get that view instead of the great long list that would be stored in the original table???

 

[vb9063]

Oops just checked and the box is a combobox, so does that make a difference.....!

 

[vbaInet]

No need spending time reinventing the wheel. I think there are add-ons or tools you can add to your website to handle the graphical map. Until then ay!

You would use that in a query as an alias field. Then base your form on that query to pull up the results.

The attachment is an example I once gave someone on here.
1. The code is copied and pasted into a module, I called it mdlConcatRelated
2. Look at the query called qryCountryAll and you can see how it was used in the aliased field Concat_Countries. Ignore the other parts of the database.

NB: Comboboxes and listboxes are the same. The only difference is their pop-up behaviour so nothing to worry about

 

[vb9063]

Thanks v much vbaInet,
Thank you for the database am still confused as to how i would get my datasheet view of services to allow the user to put in the postal codes (and be able to copy the postal info from another service) and have the postal codes saved in a long list....do queries work in datasheet view?? I have attached my database with the suppliers form that has the bulk of the info. I think my naming conventions have gone out the window, its a bit of a mess!! The searchcriteria form is where i have the original search, I broke it when I decided that I needed to be able to search with multiple criteria...:-(

Thanks again,
VB

 

[vbaInet]

Only just had time to look at your db.

I noticed the postcode field is a multivalued field. You should have mentioned that.

See attached and have a good look at the code. You would need to code the other listbox following what I had done.

 

[vb9063]

Thank you very much vbaInet that is great and so neat! How does the code determine how to search through the multi-valued field as opposed to a normal field? I have modified the code to be able to search the container and waste type as a multiselect combobox from the single field container and waste type columns.

Option Compare Database
Option Explicit
Const filterWHERE = "SELECT Services.*, Contacts.Con_FirstName, Contacts.Con_Mob, Contacts.Con_Surname " & _
                    "FROM Contacts INNER JOIN Services ON Contacts.Con_Sup = Services.ser_sup"

Private Sub cmdFilter_Click()
    Dim varItem As Variant, strWhere, strWhere1, strWhere2, str As String
    
    ' Initialise to zero length string
    strWhere = ""
    strWhere1 = ""
    strWhere2 = ""
    With SearchPostcode
        If .ItemsSelected.Count <> 0 Then
            strWhere = "'"
            For Each varItem In .ItemsSelected
                If Len(varItem & "") <> 0 Then
                    strWhere = strWhere & .ItemData(varItem) & "', '"
                End If
            Next
            strWhere = "Services.[ser_postal].value IN (" & Left$(strWhere, Len(strWhere) - 3) & ")"
            strWhere = strWhere & " AND "
        End If
    End With
    
    With SearchContainer
        If .ItemsSelected.Count <> 0 Then
            strWhere1 = "'"
            For Each varItem In .ItemsSelected
                If Len(varItem & "") <> 0 Then
                    strWhere1 = strWhere1 & .ItemData(varItem) & "', '"
                End If
            Next
            strWhere1 = "Services.[ser_cont] IN (" & Left$(strWhere1, Len(strWhere1) - 3) & ")"
            strWhere1 = strWhere1 & " AND "
        End If
    End With
    
     With SearchWasteType
        If .ItemsSelected.Count <> 0 Then
            strWhere2 = "'"
            For Each varItem In .ItemsSelected
                If Len(varItem & "") <> 0 Then
                    strWhere2 = strWhere2 & .ItemData(varItem) & "', '"
                End If
            Next
            strWhere2 = "Services.[ser_wastetype] IN (" & Left$(strWhere2, Len(strWhere2) - 3) & ")"
            strWhere2 = strWhere2 & " AND "
        End If
    End With
    
  str = strWhere & strWhere1 & strWhere2
 ' Strip off the trailing " And " text string
If Len(str) > 0 Then str = Left(str, Len(str) - _
    Len(" And "))
    
    Me.Filter = str
    Me.FilterOn = True
    ' YOU COULD USE THE FOLLOWING TOO
'    Me.RecordSource = filterWHERE & IIf(Len(strWhere) <> 0, " WHERE " & " " & strWhere, "") & ";"
'    Me.Requery
End Sub

Thanks v much again for all your help and patience!!
VB

 

[vbaInet]

This is where the multivalued field is different:

strWhere = "Services.[ser_postal].value IN (" & Left$(strWhere, Len(strWhere) - 3) & ")"

With a normal field you wouldn't use the Value property. I don't really use multi-valued fields. Might do at some point.

No problemo vb

 

[vb9063]

ah cool I thought that was the case as I fiddled with it and it worked! I seem to fumble my way round ! Is there a way to improve what I have done ie make it neater??

 

[vbaInet]

At quick glance I can't see anything that needs improving.

By the way, if you're not going to use the Me.Recordsource and Me.Requery methods then you don't need the Const variable declared at the top.

Good luck!