INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Multiselect listbox

Multiselect listbox

(OP)
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.....

RE: Multiselect listbox

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

RE: Multiselect listbox

(OP)
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.....

RE: Multiselect listbox

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

RE: Multiselect listbox

(OP)
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.....

RE: Multiselect listbox

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.

RE: Multiselect listbox

(OP)
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.....

RE: Multiselect listbox

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

RE: Multiselect listbox

(OP)
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.....

RE: Multiselect listbox

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

Quote:

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.

RE: Multiselect listbox

(OP)
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.....

RE: Multiselect listbox

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?

RE: Multiselect listbox

(OP)
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.....

RE: Multiselect listbox

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?

RE: Multiselect listbox

(OP)
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.....

RE: Multiselect listbox

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

CODE

' 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 


RE: Multiselect listbox

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.

RE: Multiselect listbox

(OP)
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.....

RE: Multiselect listbox

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.

Quote (MajP Earlier Post)

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.

CODE -->

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.

RE: Multiselect listbox

(OP)
Majp, sorry for not getting back I had a death in the family.... Now back to the DB I cant figure out how to get the Concatenate added into the query. I played with it all night and im lost...! I want to show the tbluProductDefects Defect all in a row with the comas like the Function by Duane you posted. Duane if your there maybe you can help?

CODE --> SQL

SELECT tbl_Hold.HoldID, tbl_Hold.EntryDate, tbl_Products.ProductID, tbl_Products.Product, tbl_Hold.Length, tbl_Hold.DescreteJob, tbluMachines.Machine, tbluProductDefects.Defect
FROM tbluProductDefects INNER JOIN (tbluMachines INNER JOIN (tbl_Products INNER JOIN (tbl_Hold INNER JOIN tbl_HoldProdDefects ON tbl_Hold.HoldID = tbl_HoldProdDefects.HoldID) ON tbl_Products.ProductID = tbl_Hold.ProductID) ON tbluMachines.MachineID = tbl_Hold.MachineID) ON tbluProductDefects.DefectID = tbl_HoldProdDefects.DefectID
GROUP BY tbl_Hold.HoldID, tbl_Hold.EntryDate, tbl_Products.ProductID, tbl_Products.Product, tbl_Hold.Length, tbl_Hold.DescreteJob, tbluMachines.Machine, tbluProductDefects.Defect
ORDER BY tbl_Hold.EntryDate DESC; 

Thanks,
SoggyCashew.....

RE: Multiselect listbox

Create a query with this SQL view:

CODE --> SQL

SELECT tbl_Hold.HoldID, tbl_Hold.EntryDate, tbl_Products.ProductID, tbl_Products.Product, 
tbl_Hold.Length, tbl_Hold.DescreteJob, tbluMachines.Machine, 
Concatenate("SELECT Defect FROM tbl_HoldProdDefects HPD INNER JOIN tbluProductDefects PD ON HPD.DefectID = PD.DefectID 
  WHERE HPD.HoldID = " & [HoldID]) AS Defects
FROM tbluMachines INNER JOIN (tbl_Products INNER JOIN tbl_Hold ON tbl_Products.ProductID = tbl_Hold.ProductID) 
ON tbluMachines.MachineID = tbl_Hold.MachineID
ORDER BY tbl_Hold.EntryDate DESC; 

Duane
Hook'D on Access
MS Access MVP

RE: Multiselect listbox

(OP)
Thank You....

Thanks,
SoggyCashew.....

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close