Multiselect listbox

[oxicottin]

Hello, I have a completed database and after reviewing and messing with it a lot and trying different scenarios I think I should have a multiselect listbox instead of a combo box on my main form. My question is right now I save my selection my table (tbl_Hold) field (ProductID) and the record source is of the combo box is:

SELECT [tbluProductDefects].[DefectID], [tbluProductDefects].[Defect] FROM tbluProductDefects ORDER BY [Defect];

I want to be able to save all the selected items I choose and when I open the form I want to be able to see the items that I choose in the listbox. What do I have to do to the table and the form?


Thanks,
SoggyCashew.....

 

[dhookom]

Is the combo box bound to a field in a table? Are you wanting to use a multi-value field? Or, is this and unbound combo box used for filtering?

Duane
Hook'D on Access
MS Access MVP

 

[oxicottin]

Its bound to a table (tbl_Hold) field (ProductID) and the record source is of the combo box is:

SELECT [tbluProductDefects].[DefectID], [tbluProductDefects].[Defect] FROM tbluProductDefects ORDER BY [Defect];



Thanks,
SoggyCashew.....

 

[dhookom]

I'm not sure why you would bind a field name ProductID to a value from DefectID. This makes no sense. I would expect the combo box to be bound to a field with a name including "defect".

If you want this field in tbl_Hold to be a multi-value, I would advise you to re-consider.

Duane
Hook'D on Access
MS Access MVP

 

[oxicottin]

Duane, in my image the combo box named Product defects is the one that I want to change to a multi select it is on my main form and the areas in light gray are a subform. The reason is sometimes a product is held for more than one reason and that's why I want to do a multi there.

Thanks,
SoggyCashew.....

 

[MajP]

You can use a multivalued field, and then there are several multi value controls. This makes it appear as if multiple values are stored in a single field, but that is not really what is happening. The values are actually stored in behind the scenes in a pseudo table in a normalized structure. So if you displayed the field in a text box it would appear as a string separating the values with commas. You would see "red,white,blue" but there is not a single string you have to parse, there are three distinct values saved. Many people shy away from these fields because there is a lot of unseen processing taking place, but it would do exactly what you are asking.
This capability came out around Access 2007. Before that to accomplish what you are asking would require a lot of code and a normalized table structure. You would need a child table to store the multiple defects for a given product. Then a code to add/remove records from the defect table each time you clicked a value in the list. Then code to load the selected values. With a standard listbox there is no way to directly bind it to multiple values.

 

[MajP]

Forgot to post. Read this

https://support.office.com/en-us/ar...lues-c8d15127-3641-45fc-aa2d-a3943d355e89#bm1

 

[oxicottin]

Majp, I'm attaching a example of the database I have. The combo box (cboDefects)is on (frm_Hold). I wouldn't have an issue with doing it with the commas and actually I think it would be better because it would conserve room because I'm limited on room on my form. I might run into another issue using a multi select though. If you look in my example the first form that comes up is the switchboard (frm_Switchboard) and I use it to search for the entries I have made and one of the searches is using the defects (cboDefects) on its form so how can I do this as well on my switchboard?

EXAMPLE DATABASE

Thanks,
SoggyCashew.....

 

[dhookom]

If a single product hold could have multiple different defects, I would create a separate, child table that stores multiple records per hold. This allows searching for holds related to a specific defect.

Duane
Hook'D on Access
MS Access MVP

 

[oxicottin]

Majp, I searched the net last night for an example on tek-tips of what you were talking about and couldn't find one BUT I think I found one elsewhere, Is this what you were talking about? I do believe if I did it this way my table would be normalized because I would have to do what Duane is talking about creating a separate child table. The example uses a continuous subform as the control and im guessing sql linking it to the list and such.


HERE IS THE EXAMPLE:

Thanks,
SoggyCashew.....

 

[MajP]

Did not look at the examples, but that appears to be talking about multivalued fields and their controls.

if I did it this way my table would be normalized because I would have to do what Duane is talking about creating a separate child table.

No. It is either one or the other. A multivalue field control can only be used on a multivalue field, unfortunately it is not an option to use that control on a self normalized table structure. AFAIK the MVF control does not expose itself to use by VBA. So you are either going to "store" multiple values inside a field and use a MVF control or you are going to create a child table and use a standard listbox.

Personally, I would only use a MVF if I was simply storing multiple choices and not relating them to multiple records. Something simple like "defect reason" and storing simply values like Blister, Bubble, Tear. I would not use it to store multiple foreign keys to some detailed records. This can be done, and it gets real confusing. So if a defect is more than a simple code, but relates to a specific defect that has multiple fields do not do it.

AFAIK you cannot use a MVF control unbounded, which is unfortunate. So if you wanted to search for multiple types of defects across records, you would need a standard listbox. (I guess you could make a small table with one record and one field to hold choices for searching, but that would be kind of confusing.

People sometimes do not like listboxes because they take up space and would like something like a combobox that is shrunk down until you go to make selections. This can be faked with a listbox. You can shrink the listbox down and have an arrow. When you click on the arrow it grows. Then when you leave you shrink it down. However, understand their is no way to bind a multiselect listbox unlike the MVF controls. You have to write all your own code.

 

[oxicottin]

Majp, I messed with this for a bit before reading your post. I got the example incorporated into the DB and working with an additional table I had to create to hold the defects but I cant get the search working and now after reading your post Im confused on if I should go further with this. If you want to take a look I can upload the revised version of the example?

Thanks,
SoggyCashew.....

 

[MajP]

Please go ahead and post. I am not sure how you are saving to another table. From what you show it appears you made Product Defects a MVF.
When you say "search" is that something different than what you are showing? I thought you are saving these values into the field Product Defects. Are you saving these values are searching for something?

 

[oxicottin]

MajP, Ok I added the table (tbl_HoldProdDefects)which hold my selections from the combo box. The issue im having now is the search on the switchboard. Before I could just get one line per "Hold" now that I can have so many defect reasons I add the defect reasons It adds a line for each reason in my search for the same record. On this example I don't even have defects enabled in the search because I cant get it to work correctly.

NEW EXAMPLE

Thanks,
SoggyCashew.....

 

[MajP]

I have not had a chance to open this yet. From the Form you show a product can be put on Hold for 1 or many different defects. So you want to tag a "Hold" with many defects. So the above hold would have "Beads, Bent End, Bumps, and Chatters.

Now since a hold could have many defects you want a switchboard to allow you to search for a any "Hold" based on possible defects. If this is correct I am not sure how you would want this to work. If say on your swwitchboard you had some kind of control to pick multiple defects, lets say you picked Angle, and Bead.

Since you will have multiple holds with multiple reasons, would this return all Hold records with Angle or Bead or any record with both and "only" Angle and Bead, or any record with Angle or Bead?

 

[oxicottin]

I wanted to only allow the user to be able to select one choice like it is now and have it return the holdID record that has that choice in it. For example if HoldID1 defects were Angle, Bead, Bumps and HoldID2 Defects were Bumps and Bead and I did a search on the Switchboard (cboDefects) for Angle the only result I want to show would be that of Hold1. Right now if I go into the query (qry_Switchboard) and add/link the tables (tbl_HoldProdDefects) and (tbluProductDefects) and drag the field (Defect) from (tbluProductDefects) down and ran the query then what it does is shows a record for each defect and I need it to somehow work the way it was.

Thanks,
SoggyCashew.....

 

[MajP]

First, what you did is pretty cool, but I cannot figure out how it works. Your subform fSubdefects must by bound by code to a multvalued field, but I cannot figure out how you do it. You then use it to create a real normalized many to many table. Please explain how you did this.

Although this is really cool, you defeated the simplification of the multivalue field. If you simply would have stored the values in a multivalued field then you could filter on that field very simply.

Now you got to roll this from scratch. To display the defects on the switchboard you have to use a function in your query to concatenate child records. There should be a FAQ on that.

To filter on the records

    ' Check for Product Defects (Text Field)
   ‘you need to bind the combo to the defect ID column 1
    If Not IsNull(Me.cboDefects) Then
        strWhere = strWhere & "( HoldID In (Select A.HoldId from [tbl_HoldProdDefects] as A where A.[DefectID] = " & Me.cboDefects & ")) AND "
    End If

 

[MajP]

I see now how you rolled your own MVF control. That is very slick. I will have to steal that idea. Thought at first it was a real MVF.

 

[oxicottin]

MajP I cant take credit for it as I said in my 5th post I found an example HERE and I used the MVF_ComboBox_2003.zip example. Ok, the filter will work but what about the query?

This is how it shows now if I go into the query (qry_Switchboard) and add/link the tables (tbl_HoldProdDefects) and (tbluProductDefects) and drag the field (Defect) from (tbluProductDefects) down.

How can I fix the query to get it to show my results as a single result before I filter ? Like the below image.

Thanks,
SoggyCashew.....

 

[MajP]

I guess I should have read that thread better, since it is a simulated MVF and not a real MVF. Some of you questions and statements make more sense, now that I understand it is a simulated MVF control using a standard one to many (or in your case many to many) relationship.

As stated you have to concatenate your child records.

To display the defects on the switchboard you have to use a function in your query to concatenate child records. There should be a FAQ on that.

Function Concatenate(pstrSQL As String, _
        Optional pstrDelim As String = ", ", _
        Optional pstrLastDelim As String = "") _
        As Variant
'   Created by Duane Hookom, 2003
'   Modified 6/30/2014 to correct some issues
'   this code may be included in any application/mdb providing
'   this statement is left intact
'   example
'   tblFamily with FamID as numeric primary key
'   tblFamMem with FamID, FirstName, DOB,...
'   return a comma separated list of FirstNames
'   for a FamID
'    John, Mary, Susan

'   ======= in a Query =========================
'   SELECT FamID,
'   Concatenate("SELECT FirstName FROM tblFamMem
'    WHERE FamID =" & [FamID]) as FirstNames
'   FROM tblFamily
'   ============================================

'   to get a return like Duane, Laura, Jake, and Chelsey

'   ======= in a Query =========================
'   SELECT FamID,
'   Concatenate("SELECT FirstName FROM tblFamMem
'    WHERE FamID =" & [FamID], ",",", and ") as FirstNames
'   FROM tblFamily
'   ============================================

'   If FamID is a string rather than numeric,
'    it will need to be delimited with quotes

'   ======= in a Query =========================
'   SELECT FamID,
'   Concatenate("SELECT FirstName FROM tblFamMem
'    WHERE FamID =""" & [FamID] & """", ",",", and ") as FirstNames
'   FROM tblFamily
'   ============================================


'======For DAO uncomment next 4 lines=======
'====== comment out ADO below =======
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim intCount As Integer
    Dim strLastValue As String
    Dim intLenB4Last As Integer     'length before last concatenation
    Set db = CurrentDb
    Set rs = db.OpenRecordset(pstrSQL)

'======For ADO uncomment next two lines=====
'====== comment out DAO above ======
    'Dim rs As New ADODB.Recordset
    'rs.Open pstrSQL, CurrentProject.Connection, _
        adOpenKeyset, adLockOptimistic    
    Dim strConcat As String 'build return string
    With rs
        If Not .EOF Then
            .MoveFirst
            Do While Not .EOF
                intCount = intCount + 1
                intLenB4Last = Len(strConcat)
                strConcat = strConcat & _
                .Fields(0) & pstrDelim
                strLastValue = .Fields(0)
                .MoveNext
            Loop
        End If
        .Close
    End With
    Set rs = Nothing
'====== uncomment next line for DAO ========    
    Set db = Nothing
    If Len(strConcat) > 0 Then
        strConcat = Left(strConcat, _
            Len(strConcat) - Len(pstrDelim))
        If Len(pstrLastDelim) > 0 And intCount > 1 Then
            strConcat = Left(strConcat, intLenB4Last - Len(pstrDelim)) & pstrLastDelim & strLastValue
        End If
    End If
    If Len(strConcat) > 0 Then
        Concatenate = strConcat
     Else
        Concatenate = Null
    End If
End Function

What you have done is a good demo to show all the things that a real MVF does without any code. As you can see it is a lot of work. Without creating a seperate defect_hold table an MVF would display the concatenated records and provide all the functionality of the simulated MVF combo.