Nikos,
I tried and failed. I think what's needed is far beyond my skill level. I
worked on this problem for 3 days and some sleepless nights and tonight it
came to me. What I want to accomplish is easily done via 5 small queries
that run with macros and a little code. Just for others, here is what I did:
I'm creating an Infection Control database for our hospital. Patient has
symptoms, culture is taken, infection diagnoses, and medication is
prescribed depending on whether the offending bacteria is sensitive to it or
resistant. Of course, there are a few twists to it, but they are irrelevant
to the task on hand.
Tables Infections, Culture, Prescription and Sensitivity (Bacteria to
medication) are linked by an Infection IDCode with one to many
relationships.
The issue is that we have 30 Medications (and this list will grow) that are
resistant or sensitive to the medication. The user based on the culture has
to select from the 30 + medications and add to each the R or S or nothing,
depending. I looked at the listbox solution so the user could just select
from the 30, write the R or S in an unbound box, Infection ID and Bacteria
would be read from the Culture form, and all would have been automatically
updated via a form (so she could see it). I cannot do this.
What I have done now:
Created TempTable from the Lookup table for the 30 meds but added a field
for sensitivity level (User can add meds as needed to the Lookup table as
needed)
Append Query: will populate the TempTable with all 30 (or whatever future
number) meds (ID, Description,Level)
Select Query: Opens TempTable which now shows all 30 substances and user can
type S or R in Level field (has value list limited to values).
Append Query: Which has the values from above Select Query and fields added
for Infection ID and Bacteria read from Culture form. The receiving table
has an AutoNumber field as UniqueID which solves that problem.
Delete Query: To clear value from TempTable so it can be used (this actually
runs before the Append Query so we always start out clean).
The delete, append, select query run via a command button which runs the
macro for these 3 queries (was easier than code for me).
When User has entered data, a command button runs the second append query.
Finally, user has a command button to check the results (or results for
previous records when it is selected). Criteria in query is set to values
from current Culture form.
All these queries take only seconds because we deal with a very, very small
data set.
This is probably boring for you, but maybe it helps someone else to avoid my
struggle.
Thanks for your attempt to help, but my VBA is too elementary for the more
complex work that you experts are doing.
Brigitte P.
Brigitte,
I'lll certainly watch this thread, but chances are I'll be off work for
the weekend by the time you get back, I'm 2 hrs ahead of GMT. Will check
again Monday morning.
Nikos
Brigitte P wrote:
I'll try this today when I'm at work. I understand that records are in
tables and what you say makes a lot of sense. I will adapt the code to
the
names in my tables, but probably need a little more help once I get
started.
So please stay with me in this link. I should be back in about 4 hours
or
so. Until then, thanks.
Brigitte
Brigitte,
To read the rows selected in a multi-select listbox, you need to resort
to VBA code; no other option. Doing that, it's easy to use a recordset
operation to create new records in the table being the form's
recordsource (records live in tables, not in forms). Your code could
look something like:
Dim db as DAO.Database
Dim rst as DAO.Recordset
set db = CurrentDb
set rst = db.OpenRecordset("TableName")
'or set rst = Forms![DataEntryForm].RecordsetClone
For Each itm in Me.ListboxName.ItemsSelected
rst.AddNew
rst.Fields(0) = Me.ListBoxName.ItemData(itm)
rst.Update
Next
rst.Close
set rst = Nothing
Set db = Nothing
The code above is purely indicative, based on assumptions... it won't
work as is, needs to be adjusted to your design. It also requires an
appropriate DAO reference.
HTH,
Nikos
Brigitte P wrote:
Can I populate a data entry form via a multiselect list box? Example,
user
selects 5 items from listbox and these values then would populate 5
records
on a data entry form with the values from column 1 in the listbox.
Listbox would look like this
1 Item A
2 Item B
3 Item C
The data entry (continous form) would be populated with whatever the
unique
index is and then
Record 1 1
Record 2 2 an so forth (first column value from the list box).
Then the user can click on any remaining fields that need to be
completed in
the continous form.
I read in help: "In addition, when the MultiSelect property is set to
Extended or Simple, the value of the list box control will always be
Null."
Will this prevent me from doing this or is there a work around?
I'm still working on the problem I previously posted, trying to use a
spreadsheet solution (and I stupidly posted several times because
something
was strang in my email program).
As always, many thank for your help.
Brigitte P.
