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-select List Box To Table, Access 2016    
 
   
mwatson4788
post Jan 27 2020, 12:18 PM
Post#1



Posts: 14
Joined: 20-June 17



I have a multi-select list box and I would like to know how to setup the tables to insert each value selected into the row.
I know I probably need some kind of statement to loop through the items in the list box also and any help with that would be appreciated also.
My database looks like this:

tblVisits
visitId
PPA - (just a persons name)
visitDate
Measure(can have 1 to many measures) - selected from a multiselect listbox.

tblVisitMeasures
visitId
measureId
measureName

I will have to have a report that can count how many times a measure occurred across all dates and PPAs
A PPA can have multiple visits and a visit can have multiple measures.

Thanks in Advance,
Mark

I
Go to the top of the page
 
MadPiet
post Jan 27 2020, 12:25 PM
Post#2



Posts: 3,466
Joined: 27-February 09



Measures doesn't belong in the Visits table.

Visit(VisitID (PK))---Measure(MeasureID (PK), VisitID (FK))

Just use a subform for the Measures. You could use a multi-select listbox, but I think it would make for a really horrible form.
Go to the top of the page
 
mwatson4788
post Jan 27 2020, 01:23 PM
Post#3



Posts: 14
Joined: 20-June 17



For data entry it needs to be a multi-select list box. If that makes sense.

Thanks,
Mark
Go to the top of the page
 
mwatson4788
post Jan 27 2020, 01:40 PM
Post#4



Posts: 14
Joined: 20-June 17



Picture of data entry form is attached. My main thing is this, how do I take the selections in the listbox for each visit and insert into their own table.
Attached File(s)
Attached File  Productivity_Tool_Data_Entry_Form.JPG ( 100.98K )Number of downloads: 14
 
Go to the top of the page
 
GroverParkGeorge
post Jan 27 2020, 02:00 PM
Post#5


UA Admin
Posts: 36,754
Joined: 20-June 02
From: Newcastle, WA


Sometimes we get caught up in designing an interface that appears to be "user friendly" and lose sight of the fact that the result makes managing your data much harder. This appears to be such a case.

Access provides tools to support good relational table designs. In this case, as has already been pointed out, the more effective tool here would be a subform, not a listbox.

You can make a list box work. It's just that doing it that way requires additional code, code that would not be required if you adopted the more standard approach.

All of that said, I'm going to ask to see the actual tables here, to be sure we're all on the same page about our assumptions as to what is actually going on.

Thanks.


--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
MadPiet
post Jan 27 2020, 02:10 PM
Post#6



Posts: 3,466
Joined: 27-February 09



In my opinion, that's not true. Please explain what you can do with a multi-select listbox that you cannot do with a subform.

If you use a subform, then you can delete and update existing records very easily. Not so if you use a multi-select listbox.

Sorry, but I disagree with your premise.
Go to the top of the page
 
mwatson4788
post Jan 27 2020, 02:33 PM
Post#7



Posts: 14
Joined: 20-June 17



I agree that a sub-form would be a lot more efficient from my point of view. But for the user a list-box would be much easier. However,
I will take your advice and try this with a subform.

Thanks
Mark
Go to the top of the page
 
GroverParkGeorge
post Jan 27 2020, 02:51 PM
Post#8


UA Admin
Posts: 36,754
Joined: 20-June 02
From: Newcastle, WA


Has the user requested the list box interface directly, then?

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
cheekybuddha
post Jan 27 2020, 02:54 PM
Post#9


UtterAccess Moderator
Posts: 12,264
Joined: 6-December 03
From: Telegraph Hill


Hi,

As the others have pointed out, your model requires a junction table for the many:many relationship. 1 visit can have many measures, and i measure can belong to more than one visit.

There is an example in this thread, that, IIRC, uses listboxes.

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
mwatson4788
post Jan 27 2020, 03:10 PM
Post#10



Posts: 14
Joined: 20-June 17



Yes the user has requested a list box driven user interface.
Go to the top of the page
 
GroverParkGeorge
post Jan 27 2020, 05:58 PM
Post#11


UA Admin
Posts: 36,754
Joined: 20-June 02
From: Newcastle, WA


Here's a function that does something similar.

Adapt it to fit the names of the objects in your database. I have removed error handling, but it's pretty important to use it.

CODE
Private Sub cmdAddtoMailList_Click()

    Dim db as DAO.Database
    Dim strSQL As String
    Dim varX As Variant


    Set db = CurrentDb
    
    With Me
            For Each varX In .lstAvailableNames.ItemsSelected
                strSQL = "Insert into tblMailingList (MailingID, IndividualID) SELECT " & .lstMailingDate & ", " & .lstAvailableNames.ItemData(varX)
                db.Execute Query:=strSQL, _
                    options:=dbFailOnError + dbSeeChanges
            Next varX
            .lstSelectedForMailingList.Requery
            .lstAvailableNames.Requery
  
    End With
        
End Sub

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
tina t
post Jan 27 2020, 08:27 PM
Post#12



Posts: 6,361
Joined: 11-November 10
From: SoCal, USA


QUOTE
Yes the user has requested a list box driven user interface.

i'd be much less interested in what a user says s/he wants, than in why. the average user, if asked, will say they want what they've used before (in other programs, on the internet, etc), what they're familiar with. more important to understand is what the normal workflow is, how users enter data, and what elements of a given interface they see as fast, efficient, easy to use.

i can guarantee you that if you give a user a fast, efficient, easy-to-use interface that supports the normal workflow and helps him/her enter data correctly instead of making mistakes, s/he won't know or care if it includes a listbox or a subform (and probably has no idea what a subform even is). if it turns out that a listbox is the best object to reach that goal, then so be it - dig in and make it work. but simply doing it because the user (or management) "says so", is not a design practice that i would recommend.

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
MadPiet
post Jan 27 2020, 09:29 PM
Post#13



Posts: 3,466
Joined: 27-February 09



Exactly!

I wouldn't have a problem with the listbox if there were a way to easily change what's been entered. I'd use a subform. And since I could just enter the data into the subform about as easily, I see no reason for the listbox.

For fun, create both interfaces and have the end user choose which he likes better.
Go to the top of the page
 
mwatson4788
post Jan 31 2020, 12:04 PM
Post#14



Posts: 14
Joined: 20-June 17



I normalized my table structure and created a sub-form on my Visit form for the multiple measures per visit. My problem is how do
I prevent the user from selecting the same value more than once?
The sub-form is a multi-select list box.

Thanks,
Mark


This post has been edited by mwatson4788: Jan 31 2020, 12:14 PM
Go to the top of the page
 
mempie
post Feb 14 2020, 01:48 PM
Post#15



Posts: 270
Joined: 27-September 01



A single visit will not have more than one Annual Dental Visit or more than one Colorectal Cancer Screening or more than one of any other measure, right? There are lots of measures, and I suppose that new ones will be invented later. The others were therefore right to say that you need a Measures table, related Many-to-Many with visits, using tblVisitMeasure. (If you only had a few measures and they never changed, they could be fields in the Visits table). But, given the above rule (which is a common rule), you would need to write code to make the subform implement it. You'd have a Find Duplicates query, executed in the subform's .BeforeUpdate, I think, perhaps cancelling the update if that query found records. Subforms aren't great for data entry when any sort of validation is needed. It's too hard to control what happens and when, considering that users may click on different subform rows, or the main form, and cause events to fire and dirty records to be saved (or not). The multi-select listbox is not only the control that the user requested. It's really the right answer for your interface design, as your own instinct told you. GroverParkGeorge's code, modified so that it writes to tblVisitMeasure, should work fine, and is no more complicated that what you would have to use with your subform.

I note that there are duplicate values in your listboxes. I suppose you didn't have a Measures table in your design yet, when you took that screenshot. If you are using a Measures table as the rowsource, that problem goes away.

You have three listboxes, for Medicare, Medicaid, and Ambetter (whatever that is). That's important. Is Ambetter another insurer? Will there be other insurers in the future? Would the same measure exist for multiple insurers? If so, your table and interface design will have to change, to account for all that.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    19th February 2020 - 07:41 AM