Problems with displaying data from two tables in one form

[Krzysztof Bartosiewicz]

Hi!
I haven't been using Access for a very long time and I forgot everything
I will be very greatful for help since I have been fighting with this
problem for a few hours...

I have three tables: Medicines (ID_Medicine, ID_MedicineGroup,
MedicineName), MedicineGroups (ID_MedicineGroup, MedicineGroupName) and
Treatment (ID_MEDICINE, ID_CONSULT, ID_COMMENT). After the user chooses
appropraite MedicineGroup and later Medicine from appropriate Combos I add
records to table. Then I must show all the records in a subform which were
added during current consultation with an ability to input Comment regarding
the usage of medicines. It would be no problem - simply display all three
fields (ID_MEDICINE, ID_CONSULT, ID_COMMENT) but the problem is that I also
need to display the names of the medicine and medicinegroup which are in
different table.

I tried 3 solutions:
1. When I simply changed source of the form to include other tables it was
not possible to edit the values (comments). I assume that it is as it should
be and there is no way to make Access display data from other tables on the
same form and change data in only one table?

2. When I tried to use a subform in a subform which shows me the names
everything is all right but unfortunately then I am not able to display many
medicines at once since it is only possible to show single record at a time.

3. I also tried to use an editbox which should be somehow filled with a text
from a query. I created a query

SELECT [Medicines].[ID_MEDICINE], [Medicines].[MedicineName],
[MedicineGroups].[MedicineGroupName]
FROM Medicines, MedicineGroups
WHERE Medicines.ID_MEDICINEGROUP = MedicineGroups.ID_MEDICINEGROUP;

and I tried something like
=DLookUp("MedicineGroupName";"[Query_Medicines]";"Medicines.ID_MEDICINE=" &
[ID_MEDICINE])
as the value of the edit box but another problem apperas - dlookup is
calculated once and all records have the same value in the edit box no
matter what ID.MEDICINE is (all fields have the value connected with the
ID.MEDICINE of the 1st record)

Any suggestions? I will be very very very grateful for any help since I am
out of ideas
Chris Bartosiewicz

 

[Krzysztof Bartosiewicz]

Hmm.. It's not a problem any more
I struggled with that problem for a while and after I send the mail to the
newsgroup I realised how simple it was..

The entry (3 option):
=DLookUp("MedicineGroupName";"[Query_Medicines]";"Medicines.ID_MEDICINE="
&[ID_MEDICINE])
was correct but it was misplaced - I used it in default value instead of the
source...

Sorry for bothering you..
Chris