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 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
> 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
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
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
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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...
|
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...
| |