Combo box problem

[Guest]

I tried asking this same question yesterday but as no-one replied, I think I
need to re-phrase it (it’s very difficult to explain it with mediocre
knowledge of Access).

This is the situation:
There is a centre that processes all the referrals from general medical
practitioners to the hospital. The referral centre receives a referral and
sends it on to the hospital with all other referrals that have arrived that
day that go to the same department. Hence I have three tables:
TblPatient (with patient details on it)
TblReferral (Referral details on it, one patient could have many referrals)
TblEnvelope (Envelope details on it, one envelope could have many referrals
on it)
The patient and envelope tables can only be linked through the referrals
table as both contain the referral id.

The problem I have is that I need to be enable the data entry clerks to
input data as fast as possible. This would mean constructing a form where all
those patients and their referrals are listed that have not yet been sent to
the hospital. To ensure data quality, it has been suggested that when the
clerks enter the envelope number, they would be able to choose it from a list
of active envelope numbers only. I have tried to construct this without
success. I can do the form and the combo box but I am not able to select the
values in the combo box and they do not update in the referrals table. This
might be because the query that is selecting the records for my form is
selecting those referrals with no envelope number against them. Can I still
have a dropdown box which only selects ‘active’ envelopes?

Many thanks for your help in advance! Any tips would be appreciated…

 

[Guest]

Ok so what have you got? Can you simplify as there is way too much
information for me to handle there.

Am i right in thinking you want

1. a form with referrals without enveolpe number (this has on it the
patientID of some kind)?

2. a combo box with only active envelope numbers?

 

[Guest]

Thanks for your reply Falty. The answer to both of your questions is 'yes'
and ideally in the same form.

In my form I want to filter those referrals where there is no envelope
number. Then I want to add an envelope number to these referrals and ideally
I want to select the envelope number from a dropdown menu of active envelope
numbers.

Many many thanks for your help in advance!

 

[Guest]

Create a query on the table that has all the fields included that you will
require on your form. For the envelope number criteria enter'Is Null' this
will narrow down the results to what you want.
Note there should also be inlcluded the field for envelope number

Now create another query on the table with envelopes including all the
information you want to be seen in the combo box.
Have the criteria for 'Active' set as 'true'

On the table of referrals (in design view) go to the envelope field and
click on the lookup tab. Change text to combo box,
then in row source select the query you just made from the drop down list.

To get it so that the combo displays the right info play with the column
count (to reflect amount of columns you want) and then change column widths

Create a form from the first query and add the fields you would like.

This should give you the basic outline of what you want?

 

[Guest]

Sounds exactly what I needed to know - thank you soooo much! I will try and
confirm that I managed to get it to work.