Problems with displaying data from two tables on 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_MedicineGrou p,
MedicineName), MedicineGroups (ID_MedicineGro up, MedicineGroupNa me) 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].[MedicineGroupNa me]
FROM Medicines, MedicineGroups
WHERE Medicines.ID_ME DICINEGROUP = MedicineGroups. ID_MEDICINEGROU P;

and I tried something like
=DLookUp("Medic ineGroupName";"[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

[Allen Browne]

Re your solution 2, open the subform in design view, and change its Default
View property to Continuous form or Datasheet. You will then be able to see
multiple records, one per row.

Use a combo for ID_MEDICINE, and set the first column of the combo to
zero-width so it displays the medicine name rather than the id in the
subform.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Krzysztof Bartosiewicz" <bb***@klub.chi p.pl> wrote in message
news:d4******** **@nemesis.news .tpi.pl...

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_MedicineGrou p,
MedicineName), MedicineGroups (ID_MedicineGro up, MedicineGroupNa me) 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].[MedicineGroupNa me]
FROM Medicines, MedicineGroups
WHERE Medicines.ID_ME DICINEGROUP = MedicineGroups. ID_MEDICINEGROU P;

and I tried something like
=DLookUp("Medic ineGroupName";"[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]

> Re your solution 2, open the subform in design view, and change its

Default View property to Continuous form or Datasheet. You will then be
able to see multiple records, one per row.
Unfortunately it is not possible to use continuous form if a subform is
present inside the form. And I don't want to use a datasheet.

Use a combo for ID_MEDICINE, and set the first column of the combo to
zero-width so it displays the medicine name rather than the id in the
subform.
Yes, that would be a solution but I wanted to use edit box since the value
is meant for display only (ID_MEDICINE and ID_CONSULT are fixed and only
Comment field should provide the user with the ability to edit data) - a
combo which doesn't allow to change its value doesn't look very good.

Chris Bartosiewicz

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Krzysztof Bartosiewicz" <bb***@klub.chi p.pl> wrote in message
news:d4******** **@nemesis.news .tpi.pl...

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_MedicineGrou p,
MedicineName), MedicineGroups (ID_MedicineGro up, MedicineGroupNa me) 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].[MedicineGroupNa me]
FROM Medicines, MedicineGroups
WHERE Medicines.ID_ME DICINEGROUP = MedicineGroups. ID_MEDICINEGROU P;

and I tried something like
=DLookUp("Medic ineGroupName";"[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

[Salad]

Krzysztof Bartosiewicz wrote:

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_MedicineGrou p,
MedicineName), MedicineGroups (ID_MedicineGro up, MedicineGroupNa me) 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.
Here is an alternative method. In your main form, have the consult
info. In your subform, based on a table with the following fields, have
the fields ConsultID (to link), the MedicineGroup (combo), the
MedicineID (visible no), the MedicineName (a calced field that displays
the medicine name based on medicineID, and Comments.

In the AfterUpdate event of the MedicineGroup, call a form (modal) that
displays a listbox of all medicines for that medicine group. Provide a
Select and Cancel buttons. If a person dbl-clicks on a medicine in the
listbox for that group, or single clicks and presses the Select button,
that value is stored into the MedicineID field. If cancel, nothing.
Requery the calced medicine description field when the form is closed.
Also, allow the user to dbl-click on the description field to pull up
the list box (if a group has been selected). Now add the comments....or
pull the comments that may be contained in comments already created for
that medicine and allow additional comments to be made. This way the
subform can be continuous.

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].[MedicineGroupNa me]
FROM Medicines, MedicineGroups
WHERE Medicines.ID_ME DICINEGROUP = MedicineGroups. ID_MEDICINEGROU P;

and I tried something like
=DLookUp("Medic ineGroupName";"[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.. Finally I used the third option

The entry:
=DLookUp("Medic ineGroupName";"[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

Uzytkownik "Salad" <oi*@vinegar.co m> napisal w wiadomosci
news:N0******** *********@newsr ead1.news.pas.e arthlink.net...

Krzysztof Bartosiewicz wrote:

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_MedicineGrou p,
MedicineName), MedicineGroups (ID_MedicineGro up, MedicineGroupNa me) 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.

Here is an alternative method. In your main form, have the consult info.
In your subform, based on a table with the following fields, have the
fields ConsultID (to link), the MedicineGroup (combo), the MedicineID
(visible no), the MedicineName (a calced field that displays the medicine
name based on medicineID, and Comments.

In the AfterUpdate event of the MedicineGroup, call a form (modal) that
displays a listbox of all medicines for that medicine group. Provide a
Select and Cancel buttons. If a person dbl-clicks on a medicine in the
listbox for that group, or single clicks and presses the Select button,
that value is stored into the MedicineID field. If cancel, nothing.
Requery the calced medicine description field when the form is closed.
Also, allow the user to dbl-click on the description field to pull up the
list box (if a group has been selected). Now add the comments....or pull
the comments that may be contained in comments already created for that
medicine and allow additional comments to be made. This way the subform
can be continuous.

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].[MedicineGroupNa me]
FROM Medicines, MedicineGroups
WHERE Medicines.ID_ME DICINEGROUP = MedicineGroups. ID_MEDICINEGROU P;

and I tried something like
=DLookUp("Medic ineGroupName";"[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