Multiple selection

[Dave]

Sorry about this repost but I don't think I explained myself very
well.

I have these tables; tblPtAntibodies (linked to patient),
tblAntibodies (120 records. List of all possible antibodies to choose
from) these two tables are many-to-many, hence a third, junction table
to connect them, tblAbJunction.

I would like to have a PtAntibody form with Antibody subform
(Continuous) with a checkbox next to each antibody record. The user
clicks one or several antibodies and these selected items become part
of the record of the one patient. eg User selects one row, one row is
added to the record. Select 10 rows, 10 new rows are added to the
record.

I know this can be done, but I seem to have a mental block on this
one. Google searches have been fruitless, or I'm asking the wrong
questions.

Thanks everyone,

Dave

 

[BruceM]

I suggested a way of doing this in an earlier posting. Did you try it? In
your original post you said you want to use a list box "or something else".
I took this to mean a list box was one of the options, and showed something
that could work using a list box. If a check box is a requirement you will
need to use a different method, perhaps by creating a form bound to the
antibodies table, wiith a yes/no field for each antibody. Check the box for
each antibody you want to show, run a query with the criteria True for the
Yes/No field, and append the resulting recordset (along with the PK value
from the parent record) to the junction table. When you close the anitbody
form, run an update query to clear the yes/no fields. Or perhaps you could
send the Yes records to a temp table, and append from there.

What is the reason you do not want simply to select an antibody for each
child record in the junction table? That's almost what you seem to want
anyhow, except you woudl be going through extra steps to set it up.

 

[Dave]

I suggested a way of doing this in an earlier posting.  Did you try it? In
your original post you said you want to use a list box "or something else".
I took this to mean a list box was one of the options, and showed something
that could work using a list box.  If a check box is a requirement you will
need to use a different method, perhaps by creating a form bound to the
antibodies table, wiith a yes/no field for each antibody.  Check the box for
each antibody you want to show, run a query with the criteria True for the
Yes/No field, and append the resulting recordset (along with the PK value
from the parent record) to the junction table.  When you close the anitbody
form, run an update query to clear the yes/no fields.  Or perhaps you could
send the Yes records to a temp table, and append from there.

What is the reason you do not want simply to select an antibody for each
child record in the junction table?  That's almost what you seem to want
anyhow, except you woudl be going through extra steps to set it up.











- Show quoted text -

Hi Bruce,

I just saw your post to my earlier posting. Thanks.

I'm not using Access 2007, hence no list box with check boxes
available. I do want check boxes and I think you may have given me the
idea. Yes/No field and append query. Although I'm not quite sure how
to automate that yet. But I'll work on it.

Thank you.

Dave

 

[BruceM]

OK, but multi-select list boxes are available in earler versions. Try
setting up a list box with a small Row Source, and set its multi-select
property to simple. Switch to form view. You can select as many of the
items as you like. There is no check box, but the items are highlighted as
you click them. It's a pretty straightforward interface. I'm going to try
an experiment later today if I can. I had not given much thought to the
situation before, but I see that it could be useful, so I'll try to set it
up.

Part of the value of the newsgroups for me is that they get me thinking
about new things. In the course of devising a response I often learn
something that I later implement on one of my own projects. I'm not trying
to talk you into anything, but just making a comment. By all means go after
the interface you prefer.

I suggested a way of doing this in an earlier posting. Did you try it? In
your original post you said you want to use a list box "or something
else".
I took this to mean a list box was one of the options, and showed
something
that could work using a list box. If a check box is a requirement you will
need to use a different method, perhaps by creating a form bound to the
antibodies table, wiith a yes/no field for each antibody. Check the box
for
each antibody you want to show, run a query with the criteria True for the
Yes/No field, and append the resulting recordset (along with the PK value
from the parent record) to the junction table. When you close the anitbody
form, run an update query to clear the yes/no fields. Or perhaps you could
send the Yes records to a temp table, and append from there.

What is the reason you do not want simply to select an antibody for each
child record in the junction table? That's almost what you seem to want
anyhow, except you woudl be going through extra steps to set it up.











- Show quoted text -

Hi Bruce,

I just saw your post to my earlier posting. Thanks.

I'm not using Access 2007, hence no list box with check boxes
available. I do want check boxes and I think you may have given me the
idea. Yes/No field and append query. Although I'm not quite sure how
to automate that yet. But I'll work on it.

Thank you.

Dave

 

[Hans Up]

I'm not using Access 2007, hence no list box with check boxes
available. I do want check boxes and I think you may have given me the
idea. Yes/No field and append query. Although I'm not quite sure how
to automate that yet. But I'll work on it.

Are antibody check boxes a "must have"? If not, a solution based on
Bruce's suggestion is reasonable.

Create a unbound multi-select list box, lstAntibodies, whose row source
is "SELECT AntibodyID, AntibodyName FROM tblAntibodies;", with
AntibodyID as the "bound" column.

Then, after your user has made her antibody selections for the current
patient test, click a "Store Antibody Results" command button.

Private Sub cmdSave_Click()
Dim lngItem As Long
Dim strSql As String

With Me.lstAntibodies
For lngItem = 0 To .ListCount
If .Selected(lngItem) = True And Not _
IsNull(.Selected(lngItem)) Then

strSql = "INSERT INTO tblAbJunction (" & _
"PatientID, test_date, AntiBodyID) " & _
"VALUES (" & Me.PatientID & ", #" & _
Format(Me.test_date, "yyyy/m/d hh:nn:ss") & "#, " _
& .ItemData(lngItem) & ");"
Debug.Print strSql
CurrentDb.Execute strSql, dbFailOnError
End If
Next lngItem
End With
End Sub

This approach would require the antibody list box hold 120 items, which
may not seem user friendly. However, it may be no less friendly than
giving the user a subform with 120 items and check boxes.

You decide.

Hans

 

[Dave]

Are antibody check boxes a "must have"?  If not, a solution based on
Bruce's suggestion is reasonable.

Create a unbound multi-select list box, lstAntibodies, whose row source
is "SELECT AntibodyID, AntibodyName FROM tblAntibodies;", with
AntibodyID as the "bound" column.

Then, after your user has made her antibody selections for the current
patient test, click a "Store Antibody Results" command button.

Private Sub cmdSave_Click()
Dim lngItem As Long
Dim strSql As String

With Me.lstAntibodies
For lngItem = 0 To .ListCount
     If .Selected(lngItem) = True And Not _
         IsNull(.Selected(lngItem)) Then

         strSql = "INSERT INTO tblAbJunction (" & _
             "PatientID, test_date, AntiBodyID) " & _
             "VALUES (" & Me.PatientID & ", #" & _
             Format(Me.test_date, "yyyy/m/d hh:nn:ss") & "#, " _
             & .ItemData(lngItem) & ");"
         Debug.Print strSql
                CurrentDb.Execute strSql, dbFailOnError
     End If
Next lngItem
End With
End Sub

This approach would require the antibody list box hold 120 items, which
may not seem user friendly.  However, it may be no less friendly than
giving the user a subform with 120 items and check boxes.

You decide.  

Hans

Thanks. I'm going to try both approaches; subform with check boxes and
multi-select listbox and see which would be an easier user interface.
Thanks for the code Hans.

Dave

 

[Hans Up]

Thanks. I'm going to try both approaches; subform with check boxes and
multi-select listbox and see which would be an easier user interface.
Thanks for the code Hans.

You're welcome, Dave. Please post the code you wind up using so I can
steal it for myself! :-0

Cheers,
Hans

 

[BruceM]

I posted some list box code in yesterday's thread "Next on my wish list" in
this group. Today I tested it successfully after modifying a portion of
what I suggested yesterday. I posted another message in that thread with
the fix.

 

[Hans Up]

I posted some list box code in yesterday's thread "Next on my wish list" in
this group. Today I tested it successfully after modifying a portion of
what I suggested yesterday. I posted another message in that thread with
the fix.

Hey Bruce. I need to go back and look at that again.

Can I assume you're OK with me ripping it off for my own selfish purposes?

Cheers,
Hans

 

[BruceM]

I adapted from Allen Browne and Albert Kallal. Allen's code was to use a
multi-select list box to filter a report (also, there was Allen's code from
a newsgroup posting to add several records at once), and Albert's was to
perform data validation, so I did quite a bit of adapting, but I want to
acknowledge the jumping-off places.

It's in a public newsgroup, so it's there for whoever wants it. If my code
is worth "stealing" I'm moving up in the world

 

[Hans Up]

I adapted from Allen Browne and Albert Kallal. Allen's code was to use a
multi-select list box to filter a report (also, there was Allen's code from
a newsgroup posting to add several records at once), and Albert's was to
perform data validation, so I did quite a bit of adapting, but I want to
acknowledge the jumping-off places.

It's in a public newsgroup, so it's there for whoever wants it. If my code
is worth "stealing" I'm moving up in the world

You're high up in my estimation already. I'll eagerly steal from you
any day!

Hans