UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Multi Listbox, Access 2013    
 
   
soggycashew
post Aug 3 2018, 07:43 PM
Post#1



Posts: 280
Joined: 23-April 13
From: WV, USA


Hello, I have a built DB that I'm running into all kinds of issues since we went from ac2007 to 365. there is a combobox setup on fom that when clicked it displays this subform made to look like a multi setup ect and its giving error after error so I'm ditching all this complicated code ect that went with to get it to work and going with a multi selection listbox.

Now, how do I set this up for what I have already.

1) I have a table (tbluProductDefects) that has two fields DefectID which is just a auto number and (Defect) which is text and it tells what the defect is.
2) Next is where the data is stored (tbl_HoldProdDefects) and it has three fields. (HoldProdDefectID) which is just an auto number and (HoldID) is the forms ID number and (DefectID) is the number from above.

Ideas?
Go to the top of the page
 
soggycashew
post Aug 4 2018, 07:24 AM
Post#2



Posts: 280
Joined: 23-April 13
From: WV, USA


Ok here is the DB with most Forms, Queries ect deleted to try and keep small. On (frm_Hold) the listbox (lstDefects) I need it to be able to multi select and save the data.
There were 4 controls this previous multi select subform was using. (qry_UpdateDefects) and two tables (tbluProductDefects) and (tbl_HoldProdDefects).
(tbluProductDefects) is a list of defects used for the multi list and (tbl_HoldProdDefects) was the data and in that table there is a “HoldID” and that is the forms ID.

If you wanted to view DB open and click on top entry 8/3/18 its missing a defect as you can tell…..
Now, how do I get the list in the listbox and populate and save as it did before?

Thanks,

Attached File(s)
Attached File  Test1_Reject.zip ( 208.74K )Number of downloads: 16
 
Go to the top of the page
 
projecttoday
post Aug 4 2018, 07:41 PM
Post#3


UtterAccess VIP
Posts: 10,246
Joined: 10-February 04
From: South Charleston, WV


I don't have Office 365 so I might be of little help but I see that no one has responded. Please confirm that your database was working fine in Access 2007 and has never worked in Office 365. It might be helpful to post the one that works.
Go to the top of the page
 
soggycashew
post Aug 6 2018, 10:01 AM
Post#4



Posts: 280
Joined: 23-April 13
From: WV, USA


The database does work in 2007 I just need to Have the newly created listbox work with the tables as I explained in post 1-2. Hope this helps.....
This post has been edited by soggycashew: Aug 6 2018, 10:49 AM
Go to the top of the page
 
projecttoday
post Aug 6 2018, 03:55 PM
Post#5


UtterAccess VIP
Posts: 10,246
Joined: 10-February 04
From: South Charleston, WV


So can we say that the question is: why does this database work in Access 2007 and not work in Office 365?

Why do you think ditching a block of code would result in a solution?

Does Office 365 offer help in upgrading as part of the subscription or any help at all?

I can put a post in the VIP forum and see if there's anybody here who works with Office 365.
Go to the top of the page
 
soggycashew
post Aug 6 2018, 06:43 PM
Post#6



Posts: 280
Joined: 23-April 13
From: WV, USA


I think I misunderstood the previous question... It (as in the previous list box) which was a subform made to look like a lisbox with lot of code I found on the net to get it to work as a pop-up multi selector wouldn't work with the upgrade and I wanted to get rid of it anyway because it had it's issues but still worked. Now I deleted the pop-up subform and created a list box in my example.

I just need to display the data the subform was such as multiple selections. You guys are over thinking this, I need just a listbox on a working database to display from the tables in post #1-2

Hope this helps!
Go to the top of the page
 
projecttoday
post Aug 6 2018, 09:37 PM
Post#7


UtterAccess VIP
Posts: 10,246
Joined: 10-February 04
From: South Charleston, WV


So basically you accumulate the selections from the listbox and then filter the fprm. Allen Browne has some sample multi-select listbox code here. In this one after creating the list he uses it as the where condition to open a report. You can change that to applying a filter the the form (using the IN operator).
Go to the top of the page
 
moke123
post Aug 6 2018, 10:05 PM
Post#8



Posts: 1,325
Joined: 26-December 12
From: Western Ma.,L.I.,N.Y.,Jupiter,Fl.



Multi-select list boxes can be complicated to code especially if you may need to remove an item from the list later on.
Here's a simple example, which stores the DefectIDs as a string (ie. 1,5,9,22)
It includes a few ways to display the data including selecting the items in the list box.
This method allows for deletions.

Attached File  LBXtest.zip ( 31.76K )Number of downloads: 9


Another method may be to use a pick list.

HTH
This post has been edited by moke123: Aug 6 2018, 10:06 PM
Go to the top of the page
 
soggycashew
post Aug 7 2018, 04:35 AM
Post#9



Posts: 280
Joined: 23-April 13
From: WV, USA


Moke123, yes this is what I needed thank you...Now for the switchboards subform to display the defects as text there how would I get the DefectIDs to display as text using your mod in the forms( fsub_Switchboard) txtDefectResults?

If you look at what I have in the subforms query its using the (mod_Concatenate) to generate the text with comas.

Go to the top of the page
 
moke123
post Aug 7 2018, 05:58 AM
Post#10



Posts: 1,325
Joined: 26-December 12
From: Western Ma.,L.I.,N.Y.,Jupiter,Fl.



You can roll your own ConCat function along the lines of...

CODE
Public Function ConCatDefect(strIN As Variant) As Variant

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSql As String
    Dim strOut As String

    If Nz(strIN, "") = "" Then  'Check for null StrIN
        ConCatDefect = Null
        Exit Function
    End If

    strSql = "select * from  tbluProductDefects where DefectID in (" & strIN & ")"
    Set db = CurrentDb()
    Set rs = db.OpenRecordset(strSql)

    Do Until rs.EOF
        strOut = strOut & rs!Defect & ", "
        rs.MoveNext
    Loop

    If Nz(strOut, "") <> "" Then
        strOut = Left(strOut, Len(strOut) - 2)
    End If

    ConCatDefect = strOut

MyExit:
    rs.Close
    Set rs = Nothing
    Set db = Nothing

End Function


and add it to your forms recordsource like...

CODE
CCDefect: ConCatDefect([DefectIDs])


be sure to correct any table or field name changes to the above to fit your situation

HTH
Go to the top of the page
 
soggycashew
post Aug 7 2018, 08:21 AM
Post#11



Posts: 280
Joined: 23-April 13
From: WV, USA


Moke123 your a life saver.... Ill try and get this working tonight! Much appreciated....
Go to the top of the page
 
soggycashew
post Aug 7 2018, 11:17 AM
Post#12



Posts: 280
Joined: 23-April 13
From: WV, USA


I had time to do it while at work... everything works perfectly!

Thanks,
Go to the top of the page
 
moke123
post Aug 10 2018, 10:54 AM
Post#13



Posts: 1,325
Joined: 26-December 12
From: Western Ma.,L.I.,N.Y.,Jupiter,Fl.



hey Soggy,
It's been bothering me the last few days that the solution I gave you stored the values in a single field rather than in a junction table.

I was playing around with it and came up with a few procedures to add and delete values from a junction table in the after update event of the multi-select list box.
Basically it compares the existing values in the table to the changes in the list box and adds or deletes as necessary.

I didn't use your data in the example but you can hopefully follow what I did.

Attached File  MultiLbxJunctionTbl.zip ( 56.85K )Number of downloads: 12

HTH
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    13th November 2018 - 11:08 AM