473,327 Members | 2,074 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

Problems with displaying data from two tables on one form

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
Nov 13 '05 #1
4 3631
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.chip.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_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

Nov 13 '05 #2
> 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.chip.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_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


Nov 13 '05 #3
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_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.
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].[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

Nov 13 '05 #4
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("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

Uzytkownik "Salad" <oi*@vinegar.com> napisal w wiadomosci
news:N0*****************@newsread1.news.pas.earthl ink.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_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.


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].[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

Nov 13 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

13
by: Aladdin | last post by:
I have an MS Access form on which I have a listbox listing tables in that database. I want to be able to click on any of those tables and view its contents on the same form using subforms or any...
5
by: Robert | last post by:
Hello Accessors I have some reports created in Access that are very good for what they do. However, it seems to me that when you are displaying information you don't need to print out that a...
5
by: Scott | last post by:
I have a customer that had developed an Access97 application to track their business information. The application grew significantly and they used the Upsizing Wizard to move the tables to SQL...
0
by: WindAndWaves | last post by:
Hi Gurus Here is another question that I have been struggling with for over a year. Basically, I would like to allow users to enter data into three tables in at the same time. The reason it...
7
by: Joe | last post by:
I am using Access 2003 and are linking to an Oracle 9i ODBC datasource (using Oracle ODBC drivers). After linking the tables in Access, I inspect the data contained in the linked tables. For...
9
by: Susan Bricker | last post by:
Greetings. I am having trouble populating text data that represents data in my table. Here's the setup: There is a People Table (name, address, phone, ...) peopleID = autonumber key There...
3
by: Al Wilkerson | last post by:
Hey, I have a Web Form with a drop down list, textbox, and search button. When click the search button an SQL server database is queried fordata. Once I have the data in a dataset I use the...
0
by: latin & geek via DotNetMonster.com | last post by:
hi! ok, im working on a database application. ive successfully managed to establish a relationship between two tables and display them on a datagrid, edit and add new records to them. now i...
1
by: Dave | last post by:
I'm having problems getting the GridView to reliably display a large amount of data (50,000+ rows). I am working my way through the excellent book “Real World ASP.NET Best Practices” by Farhan...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.