Multivalue fields in subform

[DavidBonsall]

Hi,

I have two fields in a form [Patient] and [visit number], within the form I
have a subform. When I type the patient number into the correct field I get
the relevant patients displayed in the subform, but I have tried to set the
[visit number field] as a multivalue check-box so I can view multiple
"visits" for each patient in the subform. However, it won't let me. Any ideas
why?

Regards

David

 

[Steve Schapel]

David,

So far I haven't quite been able to imagine what you have there and what
you are trying to achieve. But this does not sound like an appropriate
purpose for a multi-value field, and I would suggest you may have a
misconception here.

It sounds like the "fields" (they are actually called 'controls') in the
main form are unbound... am I correct?

Can you paint a picture for us here, with data examples, of what you
want the subform to do?

 

[DavidBonsall]

Sorry, I'll try to make this clearer.

I have a database of different types of clinical samples that have been
collected from patients on different visits. I am trying to design a form
that allows users to search for samples in a user friendly manner. Ideally
they would fill in the following controls [Patient number], [visit number],
and [sample type], one record at a time and this would display the samples
available to them in the subform. If they see the sample they are looking
form they would edit a checkbox control in the sub form: [Pick sample]

When they have 'picked' all their samples I planned to include a "run query
button" on the form that generates a list of all the samples they need to
pick. When they have physically picked their samples they would come back to
the form and click another "run query button" that would uncheck the [pick
field] and update a field stating that the sample is now missing. It needs to
have these two stages, incase they are unable to pick the sample they are
interested in for whatever reason.

The problem I have is that the user can't generate two records that have the
same patient number, because the [patient number] field has to be set to "no
duplicates" in order to be compatible with the subform. However, If there was
a way of entering multiple values into the other controls of the main form
(for instance [patient number]=John Doe, [visit number]=1 or 2 or 3) in order
to filter the patients in the sub form accordingly, I would have a solution.

However, I can't work out how to set a control that allows multiple values.

Regards

David

David,

So far I haven't quite been able to imagine what you have there and what
you are trying to achieve. But this does not sound like an appropriate
purpose for a multi-value field, and I would suggest you may have a
misconception here.

It sounds like the "fields" (they are actually called 'controls') in the
main form are unbound... am I correct?

Can you paint a picture for us here, with data examples, of what you
want the subform to do?

--
Steve Schapel, Microsoft Access MVP

Hi,

I have two fields in a form [Patient] and [visit number], within the form I
have a subform. When I type the patient number into the correct field I get
the relevant patients displayed in the subform, but I have tried to set the
[visit number field] as a multivalue check-box so I can view multiple
"visits" for each patient in the subform. However, it won't let me. Any ideas
why?

Regards

David

 

[Steve Schapel]

David,

For each Patient, there can be more than one Visit. And for each of
these Visits, there can be more than one Sample. Right? So I assume in
your database design you therefore have three tables to represent these
three entities Patients, Visits, and Samples?

 

[Tom Wickerath]

Hi David,

However, I can't work out how to set a control that allows multiple values.

Rather than setting a control that allows multiple values, you could try
implementing an unbound QBF (Query by Form) search form, which includes a
bound subform. As long as you do not enter search criteria in a given text
box on the main form, no filtering will occur for that field. I have a sample
posted on the Seattle Access web site. See the February 12, 2008 download
here:

http://www.seattleaccess.org/downloads.htm

Other examples that I have available include:

http://www.accessmvp.com/TWickerath/downloads/customdialogbox.zip
http://www.accessmvp.com/TWickerath/downloads/elements.zip
http://www.accessmvp.com/TWickerath/downloads/Chap08QBF.zip

The Elements sample simply demonstrates using a multiselect list box (you
can make continuous selections using the shift key, or discontinuous
selections using the Control key).


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

Sorry, I'll try to make this clearer.

I have a database of different types of clinical samples that have been
collected from patients on different visits. I am trying to design a form
that allows users to search for samples in a user friendly manner. Ideally
they would fill in the following controls [Patient number], [visit number],
and [sample type], one record at a time and this would display the samples
available to them in the subform. If they see the sample they are looking
form they would edit a checkbox control in the sub form: [Pick sample]

When they have 'picked' all their samples I planned to include a "run query
button" on the form that generates a list of all the samples they need to
pick. When they have physically picked their samples they would come back to
the form and click another "run query button" that would uncheck the [pick
field] and update a field stating that the sample is now missing. It needs to
have these two stages, incase they are unable to pick the sample they are
interested in for whatever reason.

The problem I have is that the user can't generate two records that have the
same patient number, because the [patient number] field has to be set to "no
duplicates" in order to be compatible with the subform. However, If there was
a way of entering multiple values into the other controls of the main form
(for instance [patient number]=John Doe, [visit number]=1 or 2 or 3) in order
to filter the patients in the sub form accordingly, I would have a solution.

However, I can't work out how to set a control that allows multiple values.

Regards

David

 

[DavidBonsall]

I actually have one table that contains all the information for each sample,
including the visit number, sample type and patient number (and several other
pieces of information that I think are irrelevant to this thread). I do have
another table with all the visit numbers and clinic dates for each patient
(patients in this table are repeated as well).

Regards

David

David,

For each Patient, there can be more than one Visit. And for each of
these Visits, there can be more than one Sample. Right? So I assume in
your database design you therefore have three tables to represent these
three entities Patients, Visits, and Samples?

--
Steve Schapel, Microsoft Access MVP

Sorry, I'll try to make this clearer.

I have a database of different types of clinical samples that have been
collected from patients on different visits. I am trying to design a form
that allows users to search for samples in a user friendly manner. Ideally
they would fill in the following controls [Patient number], [visit number],
and [sample type], one record at a time and this would display the samples
available to them in the subform. If they see the sample they are looking
form they would edit a checkbox control in the sub form: [Pick sample]

When they have 'picked' all their samples I planned to include a "run query
button" on the form that generates a list of all the samples they need to
pick. When they have physically picked their samples they would come back to
the form and click another "run query button" that would uncheck the [pick
field] and update a field stating that the sample is now missing. It needs to
have these two stages, incase they are unable to pick the sample they are
interested in for whatever reason.

The problem I have is that the user can't generate two records that have the
same patient number, because the [patient number] field has to be set to "no
duplicates" in order to be compatible with the subform. However, If there was
a way of entering multiple values into the other controls of the main form
(for instance [patient number]=John Doe, [visit number]=1 or 2 or 3) in order
to filter the patients in the sub form accordingly, I would have a solution.

However, I can't work out how to set a control that allows multiple values.

 

[Steve Schapel]

David,

I realise we have gone off on a bit of a tangent from your original
question. And Tom has given some good information that you may want to
pursue. But I really recommend pausing and doing a bit of tidying up of
your table design first, before tackling your form based filtering.

Unless I have misunderstood, I believe you need the 3 tables I mentioned
bofore, with a PatientID or equivalent in the Visits table, in order to
relate it to the Patients table, and a VisitID or equivalent in the
Samples table in order to relate it to the Visits table.

If you could have a go at this, and then post back listing the fields in
your tables, someone will be able to advise.

 

[DavidBonsall]

Ok,

I still have the original table which has 42,000 sample positions defined by
[freezer number], [rack number], [box number], and [box location] which can
be concanentated into one field [sample location ID]. I assume this would
make a useful primary key for this table. Each sample position has all the
relevant information for each table on the same table. This same table has
all the other fields we talked about which details the samples for each
position [date] [visit number] [sample type].

I don't understand how to break this down into seperate tables. At each
visit 6 different types of sample are taken. Some patients have already had
30 visits and this number will continue to rise indefinitely.

 

[Steve Schapel]

David,

I suggest normalising the tables first, as if you were starting from
scratch.

There will be a way to get your existing data into the revised table
structure, using Append Queries and Update Queries, but we can cross
that bridge when we come to it.

Once again, I am not yet familiar enough with your business processes to
be definitive. But it seems to me that the sample positions data needs
to be normalised as well. Does each box throughout the whole system
have a unique [box number]? If so, I *imagine* you would have a table
like this:

Table: Boxes
- Box Number (PK)
- Rack Number
- Freezer Number
- Box Location

That means in your Samples table, you only need a [Box Number] foreign
key field in order to identify the sample position.

So, in addition to that Boxes table, I *imagine* the following rough
outline:

Table: Patients
- Patient Number (PK)
- PatientName
- Address
- Phone
… other patient-specific information
(I think you mentioned you already have a Patient Number?)

Table: Visits
- VisitID (PK - use an Autonumber?)
- Patient Number (FK)
- DateOfVisit
- ClinicianSeen
… other visit-specific information

Table: Samples
- SampleID (Autonumber PK)
- VisitID (FK)
- Box Number (FK)
- Sample Type
… other sample-specific information

So that way, a patient's record is entered one time. For each time they
visit, the visit data is entered one time. And then, for each of the
visits, the 6 samples are entered, along with the position.

This is really for a later step, but it may be that your data entry
interface turns out to use something like the structure defined in this
article: http://accesstips.datamanagementsolutions.biz/subsub.htm and
this may then provide you with sufficient power to search the records
you want with nothing further required, in which case your original
question evaporates. Maybe.

On a related subject, you may also benefit from reading
http://accesstips.datamanagementsolutions.biz/primarykey.htm
(acknowledging that this is one of a number of possible viewpoints!)

So, please let us know whether this is making any sense now.

--
Steve Schapel, Microsoft Access MVP

Ok,

I still have the original table which has 42,000 sample positions defined by
[freezer number], [rack number], [box number], and [box location] which can
be concanentated into one field [sample location ID]. I assume this would
make a useful primary key for this table. Each sample position has all the
relevant information for each table on the same table. This same table has
all the other fields we talked about which details the samples for each
position [date] [visit number] [sample type].

I don't understand how to break this down into seperate tables. At each
visit 6 different types of sample are taken. Some patients have already had
30 visits and this number will continue to rise indefinitely.