If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
new recordset entry on top
No, this is not a dumb question. It's actually fascinating.
Yes, it is possible to do what you want, but you have to be *careful*. You should ONLY do it if your detail section is *locked*, that is if the records cannot me changed. I'll explain why in a minute. First of all, here's how to do it: Instead of having just your main table as the record source for your form, create a query that joins the main table with the lookup table(s), displaying both the medication ID number and the medication name in the field list. (Make sure you add the ID field from the *main table* and not from the linked table.) Leave your unbound combos as they are. Now, instead of displaying the medication ID in the field of the form, just display the medication name field. That ought to do it. Now, here's why you only want to do this if the form records are locked. If you go back and change a medication name, it won't be changed in the main table, but it will be changed in the lookup table! That means that every single instance of that medication ID will be changed to the new name. Not what you want, probably. So if you want your user to be able to change the selection (say if they made a mistake and chose the wrong medication), you should leave them as combo boxes. Does that help? -- --Roger Carlson MS Access MVP Access Database Samples: www.rogersaccesslibrary.com Want answers to your Access questions in your Email? Free subscription: http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L "Kathy" wrote in message ... I'm sorry that I probably did not give a very good explanation. I am pretty sure that I am using the correct combo-box construction and it does work perfectly in the "unbound controls Record on Top" model. Also in the detail section when the AddNew button is clicked, the corresponding bound control shows the correct Medication name as long as I keep the control as a combo box. Since once the medication is given, there would no longer be any choice of medication, I wanted to make the corresponding box just show the Medication name that was selected in the unbound control. However when I changed it to a plain text box, then I only get the value or Medication ID. Is there a way to change it to text or should I just leave it as a combo box? Sorry if this is really dumb. And again your help is much appreciated. -- Kbelo "Roger Carlson" wrote: I don't have a lot of experience with lookup fields. We (MVPs and other experienced Access users) discourage their use. The reason is that lookup fields mask what is really happening in the table. See this http://www.mvps.org/access/lookupfields.htm for more information about the evils of lookup fields. A better way would be get rid of the lookup field property (in table design view, change Lookup to text) and then use a combo box on your form to create the lookup. This would work with my sample. -- --Roger Carlson MS Access MVP Access Database Samples: www.rogersaccesslibrary.com Want answers to your Access questions in your Email? Free subscription: http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L "Kathy" wrote in message ... Yes. I have two tables...one tblMedication and the other tblDoses which are linked by the Medication ID. Thank you for your response. -- Kbelo "Roger Carlson" wrote: Are you using Lookup fields in your table? -- --Roger Carlson MS Access MVP Access Database Samples: www.rogersaccesslibrary.com Want answers to your Access questions in your Email? Free subscription: http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L "Kathy" wrote in message ... Hello again...I did download from your library and reconstruct my form according to that model...thank you for your help!...But I have one problem that I have not been able to correct. In the form header that has the unbound controls, I have two combo boxes that are linked so that the selection in the first box determines the selection in the second box. Everything works beautifully except that when I add the new record, the record that appears in the detail section shows the ID of the combo box 1 selection and not the name. (In this example, the first combo box is a list of Medications and the second combo is a list of standard dosages. So when a medication is selected, then only standard doses for this medication shows in the second combo. When saved, the medication ID shows up and not the name of the medication.) Is there some way I can correct this, so the name is shown? Again, many thanks, Kathy -- Kbelo "Roger Carlson" wrote: On my website (www.rogersaccesslibrary.com), is a small Access database sample called "ContinuousFormWithNewRecordOnTop.mdb" which illustrates how to do this. -- --Roger Carlson MS Access MVP Access Database Samples: www.rogersaccesslibrary.com Want answers to your Access questions in your Email? Free subscription: http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L "Kathy" wrote in message ... Hello, I am trying to have a new recordset on a subform open at the top, instead of the bottom. I noticed a similiar posting and the reply suggested using a one-line subform. If this is the best way to accomplish this design, could you please offer a little more information about how to do it? Many thanks. -- Kbelo |
#12
|
|||
|
|||
new recordset entry on top
Thank you so much again...I am a big chicken and you are right that if the
end-users made a mistake and wanted to change the medication, then it could get "ugly"...I am keeping the combo boxes. I am so close to finishing this form, I hope it will be OK to ask another question. Here goes: The form has a main form (patient information) and a subform (the medication record which now has the record on top). I would like that the patient chart number from the main form fills in the unbound text box on the medication record as the end-user moves from chart to chart. Thus when they entered the next patient record, the chart number would automatically show up in the unbound chart number box for the medication record, then when the Add/New button is clicked, the information goes into the detail section and the unbound controls are nulled. Then moves to the next patient and their chart number would automatically fill in....and etc. I have tried all kinds of combinations of code, but either nothing happens or it says I cannot enter a value in that field....and so on. Anyway, I am up a creek again and any help is gratefully accepted. -- Kbelo "Roger Carlson" wrote: No, this is not a dumb question. It's actually fascinating. Yes, it is possible to do what you want, but you have to be *careful*. You should ONLY do it if your detail section is *locked*, that is if the records cannot me changed. I'll explain why in a minute. First of all, here's how to do it: Instead of having just your main table as the record source for your form, create a query that joins the main table with the lookup table(s), displaying both the medication ID number and the medication name in the field list. (Make sure you add the ID field from the *main table* and not from the linked table.) Leave your unbound combos as they are. Now, instead of displaying the medication ID in the field of the form, just display the medication name field. That ought to do it. Now, here's why you only want to do this if the form records are locked. If you go back and change a medication name, it won't be changed in the main table, but it will be changed in the lookup table! That means that every single instance of that medication ID will be changed to the new name. Not what you want, probably. So if you want your user to be able to change the selection (say if they made a mistake and chose the wrong medication), you should leave them as combo boxes. Does that help? -- --Roger Carlson MS Access MVP Access Database Samples: www.rogersaccesslibrary.com Want answers to your Access questions in your Email? Free subscription: http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L "Kathy" wrote in message ... I'm sorry that I probably did not give a very good explanation. I am pretty sure that I am using the correct combo-box construction and it does work perfectly in the "unbound controls Record on Top" model. Also in the detail section when the AddNew button is clicked, the corresponding bound control shows the correct Medication name as long as I keep the control as a combo box. Since once the medication is given, there would no longer be any choice of medication, I wanted to make the corresponding box just show the Medication name that was selected in the unbound control. However when I changed it to a plain text box, then I only get the value or Medication ID. Is there a way to change it to text or should I just leave it as a combo box? Sorry if this is really dumb. And again your help is much appreciated. -- Kbelo "Roger Carlson" wrote: I don't have a lot of experience with lookup fields. We (MVPs and other experienced Access users) discourage their use. The reason is that lookup fields mask what is really happening in the table. See this http://www.mvps.org/access/lookupfields.htm for more information about the evils of lookup fields. A better way would be get rid of the lookup field property (in table design view, change Lookup to text) and then use a combo box on your form to create the lookup. This would work with my sample. -- --Roger Carlson MS Access MVP Access Database Samples: www.rogersaccesslibrary.com Want answers to your Access questions in your Email? Free subscription: http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L "Kathy" wrote in message ... Yes. I have two tables...one tblMedication and the other tblDoses which are linked by the Medication ID. Thank you for your response. -- Kbelo "Roger Carlson" wrote: Are you using Lookup fields in your table? -- --Roger Carlson MS Access MVP Access Database Samples: www.rogersaccesslibrary.com Want answers to your Access questions in your Email? Free subscription: http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L "Kathy" wrote in message ... Hello again...I did download from your library and reconstruct my form according to that model...thank you for your help!...But I have one problem that I have not been able to correct. In the form header that has the unbound controls, I have two combo boxes that are linked so that the selection in the first box determines the selection in the second box. Everything works beautifully except that when I add the new record, the record that appears in the detail section shows the ID of the combo box 1 selection and not the name. (In this example, the first combo box is a list of Medications and the second combo is a list of standard dosages. So when a medication is selected, then only standard doses for this medication shows in the second combo. When saved, the medication ID shows up and not the name of the medication.) Is there some way I can correct this, so the name is shown? Again, many thanks, Kathy -- Kbelo "Roger Carlson" wrote: On my website (www.rogersaccesslibrary.com), is a small Access database sample called "ContinuousFormWithNewRecordOnTop.mdb" which illustrates how to do this. -- --Roger Carlson MS Access MVP Access Database Samples: www.rogersaccesslibrary.com Want answers to your Access questions in your Email? Free subscription: http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L "Kathy" wrote in message ... Hello, I am trying to have a new recordset on a subform open at the top, instead of the bottom. I noticed a similiar posting and the reply suggested using a one-line subform. If this is the best way to accomplish this design, could you please offer a little more information about how to do it? Many thanks. -- Kbelo |
#13
|
|||
|
|||
new recordset entry on top
I don't think really want to do this. If chart number is the field linking
the form and subform (Parent/Child link properties), then it will automatically be added by the subform control and you can just use a bound form (locked). If you are linking on some other field, then you will just be storing redundant information in your medication table. You can easily get the chart number any time by joining the two tables. Have I misunderstood? -- --Roger Carlson MS Access MVP Access Database Samples: www.rogersaccesslibrary.com Want answers to your Access questions in your Email? Free subscription: http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L "Kathy" wrote in message ... Thank you so much again...I am a big chicken and you are right that if the end-users made a mistake and wanted to change the medication, then it could get "ugly"...I am keeping the combo boxes. I am so close to finishing this form, I hope it will be OK to ask another question. Here goes: The form has a main form (patient information) and a subform (the medication record which now has the record on top). I would like that the patient chart number from the main form fills in the unbound text box on the medication record as the end-user moves from chart to chart. Thus when they entered the next patient record, the chart number would automatically show up in the unbound chart number box for the medication record, then when the Add/New button is clicked, the information goes into the detail section and the unbound controls are nulled. Then moves to the next patient and their chart number would automatically fill in....and etc. I have tried all kinds of combinations of code, but either nothing happens or it says I cannot enter a value in that field....and so on. Anyway, I am up a creek again and any help is gratefully accepted. -- Kbelo "Roger Carlson" wrote: No, this is not a dumb question. It's actually fascinating. Yes, it is possible to do what you want, but you have to be *careful*. You should ONLY do it if your detail section is *locked*, that is if the records cannot me changed. I'll explain why in a minute. First of all, here's how to do it: Instead of having just your main table as the record source for your form, create a query that joins the main table with the lookup table(s), displaying both the medication ID number and the medication name in the field list. (Make sure you add the ID field from the *main table* and not from the linked table.) Leave your unbound combos as they are. Now, instead of displaying the medication ID in the field of the form, just display the medication name field. That ought to do it. Now, here's why you only want to do this if the form records are locked. If you go back and change a medication name, it won't be changed in the main table, but it will be changed in the lookup table! That means that every single instance of that medication ID will be changed to the new name. Not what you want, probably. So if you want your user to be able to change the selection (say if they made a mistake and chose the wrong medication), you should leave them as combo boxes. Does that help? -- --Roger Carlson MS Access MVP Access Database Samples: www.rogersaccesslibrary.com Want answers to your Access questions in your Email? Free subscription: http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L "Kathy" wrote in message ... I'm sorry that I probably did not give a very good explanation. I am pretty sure that I am using the correct combo-box construction and it does work perfectly in the "unbound controls Record on Top" model. Also in the detail section when the AddNew button is clicked, the corresponding bound control shows the correct Medication name as long as I keep the control as a combo box. Since once the medication is given, there would no longer be any choice of medication, I wanted to make the corresponding box just show the Medication name that was selected in the unbound control. However when I changed it to a plain text box, then I only get the value or Medication ID. Is there a way to change it to text or should I just leave it as a combo box? Sorry if this is really dumb. And again your help is much appreciated. -- Kbelo "Roger Carlson" wrote: I don't have a lot of experience with lookup fields. We (MVPs and other experienced Access users) discourage their use. The reason is that lookup fields mask what is really happening in the table. See this http://www.mvps.org/access/lookupfields.htm for more information about the evils of lookup fields. A better way would be get rid of the lookup field property (in table design view, change Lookup to text) and then use a combo box on your form to create the lookup. This would work with my sample. -- --Roger Carlson MS Access MVP Access Database Samples: www.rogersaccesslibrary.com Want answers to your Access questions in your Email? Free subscription: http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L "Kathy" wrote in message ... Yes. I have two tables...one tblMedication and the other tblDoses which are linked by the Medication ID. Thank you for your response. -- Kbelo "Roger Carlson" wrote: Are you using Lookup fields in your table? -- --Roger Carlson MS Access MVP Access Database Samples: www.rogersaccesslibrary.com Want answers to your Access questions in your Email? Free subscription: http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L "Kathy" wrote in message ... Hello again...I did download from your library and reconstruct my form according to that model...thank you for your help!...But I have one problem that I have not been able to correct. In the form header that has the unbound controls, I have two combo boxes that are linked so that the selection in the first box determines the selection in the second box. Everything works beautifully except that when I add the new record, the record that appears in the detail section shows the ID of the combo box 1 selection and not the name. (In this example, the first combo box is a list of Medications and the second combo is a list of standard dosages. So when a medication is selected, then only standard doses for this medication shows in the second combo. When saved, the medication ID shows up and not the name of the medication.) Is there some way I can correct this, so the name is shown? Again, many thanks, Kathy -- Kbelo "Roger Carlson" wrote: On my website (www.rogersaccesslibrary.com), is a small Access database sample called "ContinuousFormWithNewRecordOnTop.mdb" which illustrates how to do this. -- --Roger Carlson MS Access MVP Access Database Samples: www.rogersaccesslibrary.com Want answers to your Access questions in your Email? Free subscription: http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L "Kathy" wrote in message ... Hello, I am trying to have a new recordset on a subform open at the top, instead of the bottom. I noticed a similiar posting and the reply suggested using a one-line subform. If this is the best way to accomplish this design, could you please offer a little more information about how to do it? Many thanks. -- Kbelo |
#14
|
|||
|
|||
new recordset entry on top
Hello again...thank you for answering as I am still hacking away...I am
pretty "green" in this area, so I am not confident if I am doing things correctly, but anyway, here is the latest... The Main form and subform are linked via the Chart Number. The subform is the form with the record on top and has the Add/New button. All is working well. I would like that the chart number from the main form be automatically added to the unbound Chart Number control in the subform. So I guessed around and added this code to the Add/New button code, right after Me.Requery: Me.NewChartNumber = Forms!frmAnemiaManagementMain.Form! txtChartNumber So far this is working, each time the button is clicked, the new record is added, then clears the unbound controls, then fills the current chart number in the first unbound control. The dangerous problem is when the user moves to the next patient record, as the chart number doesn't update to the new record. So if the user doesn't change the chart number, it will be saved with the previous record. Thus I have been trying to work with the Main form with the OnCurrent event. But so far, no luck. I hope I am not totally out in left field, but your help is again greatly appreciated. -- Kbelo "Roger Carlson" wrote: I don't think really want to do this. If chart number is the field linking the form and subform (Parent/Child link properties), then it will automatically be added by the subform control and you can just use a bound form (locked). If you are linking on some other field, then you will just be storing redundant information in your medication table. You can easily get the chart number any time by joining the two tables. Have I misunderstood? -- --Roger Carlson MS Access MVP Access Database Samples: www.rogersaccesslibrary.com Want answers to your Access questions in your Email? Free subscription: http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L "Kathy" wrote in message ... Thank you so much again...I am a big chicken and you are right that if the end-users made a mistake and wanted to change the medication, then it could get "ugly"...I am keeping the combo boxes. I am so close to finishing this form, I hope it will be OK to ask another question. Here goes: The form has a main form (patient information) and a subform (the medication record which now has the record on top). I would like that the patient chart number from the main form fills in the unbound text box on the medication record as the end-user moves from chart to chart. Thus when they entered the next patient record, the chart number would automatically show up in the unbound chart number box for the medication record, then when the Add/New button is clicked, the information goes into the detail section and the unbound controls are nulled. Then moves to the next patient and their chart number would automatically fill in....and etc. I have tried all kinds of combinations of code, but either nothing happens or it says I cannot enter a value in that field....and so on. Anyway, I am up a creek again and any help is gratefully accepted. -- Kbelo "Roger Carlson" wrote: No, this is not a dumb question. It's actually fascinating. Yes, it is possible to do what you want, but you have to be *careful*. You should ONLY do it if your detail section is *locked*, that is if the records cannot me changed. I'll explain why in a minute. First of all, here's how to do it: Instead of having just your main table as the record source for your form, create a query that joins the main table with the lookup table(s), displaying both the medication ID number and the medication name in the field list. (Make sure you add the ID field from the *main table* and not from the linked table.) Leave your unbound combos as they are. Now, instead of displaying the medication ID in the field of the form, just display the medication name field. That ought to do it. Now, here's why you only want to do this if the form records are locked. If you go back and change a medication name, it won't be changed in the main table, but it will be changed in the lookup table! That means that every single instance of that medication ID will be changed to the new name. Not what you want, probably. So if you want your user to be able to change the selection (say if they made a mistake and chose the wrong medication), you should leave them as combo boxes. Does that help? -- --Roger Carlson MS Access MVP Access Database Samples: www.rogersaccesslibrary.com Want answers to your Access questions in your Email? Free subscription: http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L "Kathy" wrote in message ... I'm sorry that I probably did not give a very good explanation. I am pretty sure that I am using the correct combo-box construction and it does work perfectly in the "unbound controls Record on Top" model. Also in the detail section when the AddNew button is clicked, the corresponding bound control shows the correct Medication name as long as I keep the control as a combo box. Since once the medication is given, there would no longer be any choice of medication, I wanted to make the corresponding box just show the Medication name that was selected in the unbound control. However when I changed it to a plain text box, then I only get the value or Medication ID. Is there a way to change it to text or should I just leave it as a combo box? Sorry if this is really dumb. And again your help is much appreciated. -- Kbelo "Roger Carlson" wrote: I don't have a lot of experience with lookup fields. We (MVPs and other experienced Access users) discourage their use. The reason is that lookup fields mask what is really happening in the table. See this http://www.mvps.org/access/lookupfields.htm for more information about the evils of lookup fields. A better way would be get rid of the lookup field property (in table design view, change Lookup to text) and then use a combo box on your form to create the lookup. This would work with my sample. -- --Roger Carlson MS Access MVP Access Database Samples: www.rogersaccesslibrary.com Want answers to your Access questions in your Email? Free subscription: http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L "Kathy" wrote in message ... Yes. I have two tables...one tblMedication and the other tblDoses which are linked by the Medication ID. Thank you for your response. -- Kbelo "Roger Carlson" wrote: Are you using Lookup fields in your table? -- --Roger Carlson MS Access MVP Access Database Samples: www.rogersaccesslibrary.com Want answers to your Access questions in your Email? Free subscription: http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L "Kathy" wrote in message ... Hello again...I did download from your library and reconstruct my form according to that model...thank you for your help!...But I have one problem that I have not been able to correct. In the form header that has the unbound controls, I have two combo boxes that are linked so that the selection in the first box determines the selection in the second box. Everything works beautifully except that when I add the new record, the record that appears in the detail section shows the ID of the combo box 1 selection and not the name. (In this example, the first combo box is a list of Medications and the second combo is a list of standard dosages. So when a medication is selected, then only standard doses for this medication shows in the second combo. When saved, the medication ID shows up and not the name of the medication.) Is there some way I can correct this, so the name is shown? Again, many thanks, Kathy -- Kbelo "Roger Carlson" wrote: On my website (www.rogersaccesslibrary.com), is a small Access database sample called "ContinuousFormWithNewRecordOnTop.mdb" which illustrates how to do this. -- --Roger Carlson MS Access MVP Access Database Samples: www.rogersaccesslibrary.com Want answers to your Access questions in your Email? Free subscription: http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L "Kathy" wrote in message ... Hello, I am trying to have a new recordset on a subform open at the top, instead of the bottom. I noticed a similiar posting and the reply suggested using a one-line subform. If this is the best way to accomplish this design, could you please offer a little more information about how to do it? Many thanks. -- Kbelo |
#15
|
|||
|
|||
new recordset entry on top
One of the really nice features of the Subform Control is that it
automatically populates the table in the subform with the appropriate key value from the Mainform (assuming the key field is the field used in the Link Parent/Child properties. Since you are using Chart Number as your linking field, you shouldn't need to worry about assigning it. In fact, you don't even need to show that field in the unbound fields at the top. It will be automatically assigned. If you really want to, you can display it as you're doing, but you should NOT write this value to the bound foreign key field in the subform as you are doing with the other fields. As I said, it will get assigned automatically. -- --Roger Carlson MS Access MVP Access Database Samples: www.rogersaccesslibrary.com Want answers to your Access questions in your Email? Free subscription: http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L "Kathy" wrote in message ... Hello again...thank you for answering as I am still hacking away...I am pretty "green" in this area, so I am not confident if I am doing things correctly, but anyway, here is the latest... The Main form and subform are linked via the Chart Number. The subform is the form with the record on top and has the Add/New button. All is working well. I would like that the chart number from the main form be automatically added to the unbound Chart Number control in the subform. So I guessed around and added this code to the Add/New button code, right after Me.Requery: Me.NewChartNumber = Forms!frmAnemiaManagementMain.Form! txtChartNumber So far this is working, each time the button is clicked, the new record is added, then clears the unbound controls, then fills the current chart number in the first unbound control. The dangerous problem is when the user moves to the next patient record, as the chart number doesn't update to the new record. So if the user doesn't change the chart number, it will be saved with the previous record. Thus I have been trying to work with the Main form with the OnCurrent event. But so far, no luck. I hope I am not totally out in left field, but your help is again greatly appreciated. -- Kbelo "Roger Carlson" wrote: I don't think really want to do this. If chart number is the field linking the form and subform (Parent/Child link properties), then it will automatically be added by the subform control and you can just use a bound form (locked). If you are linking on some other field, then you will just be storing redundant information in your medication table. You can easily get the chart number any time by joining the two tables. Have I misunderstood? -- --Roger Carlson MS Access MVP Access Database Samples: www.rogersaccesslibrary.com Want answers to your Access questions in your Email? Free subscription: http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L "Kathy" wrote in message ... Thank you so much again...I am a big chicken and you are right that if the end-users made a mistake and wanted to change the medication, then it could get "ugly"...I am keeping the combo boxes. I am so close to finishing this form, I hope it will be OK to ask another question. Here goes: The form has a main form (patient information) and a subform (the medication record which now has the record on top). I would like that the patient chart number from the main form fills in the unbound text box on the medication record as the end-user moves from chart to chart. Thus when they entered the next patient record, the chart number would automatically show up in the unbound chart number box for the medication record, then when the Add/New button is clicked, the information goes into the detail section and the unbound controls are nulled. Then moves to the next patient and their chart number would automatically fill in....and etc. I have tried all kinds of combinations of code, but either nothing happens or it says I cannot enter a value in that field....and so on. Anyway, I am up a creek again and any help is gratefully accepted. -- Kbelo "Roger Carlson" wrote: No, this is not a dumb question. It's actually fascinating. Yes, it is possible to do what you want, but you have to be *careful*. You should ONLY do it if your detail section is *locked*, that is if the records cannot me changed. I'll explain why in a minute. First of all, here's how to do it: Instead of having just your main table as the record source for your form, create a query that joins the main table with the lookup table(s), displaying both the medication ID number and the medication name in the field list. (Make sure you add the ID field from the *main table* and not from the linked table.) Leave your unbound combos as they are. Now, instead of displaying the medication ID in the field of the form, just display the medication name field. That ought to do it. Now, here's why you only want to do this if the form records are locked. If you go back and change a medication name, it won't be changed in the main table, but it will be changed in the lookup table! That means that every single instance of that medication ID will be changed to the new name. Not what you want, probably. So if you want your user to be able to change the selection (say if they made a mistake and chose the wrong medication), you should leave them as combo boxes. Does that help? -- --Roger Carlson MS Access MVP Access Database Samples: www.rogersaccesslibrary.com Want answers to your Access questions in your Email? Free subscription: http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L "Kathy" wrote in message ... I'm sorry that I probably did not give a very good explanation. I am pretty sure that I am using the correct combo-box construction and it does work perfectly in the "unbound controls Record on Top" model. Also in the detail section when the AddNew button is clicked, the corresponding bound control shows the correct Medication name as long as I keep the control as a combo box. Since once the medication is given, there would no longer be any choice of medication, I wanted to make the corresponding box just show the Medication name that was selected in the unbound control. However when I changed it to a plain text box, then I only get the value or Medication ID. Is there a way to change it to text or should I just leave it as a combo box? Sorry if this is really dumb. And again your help is much appreciated. -- Kbelo "Roger Carlson" wrote: I don't have a lot of experience with lookup fields. We (MVPs and other experienced Access users) discourage their use. The reason is that lookup fields mask what is really happening in the table. See this http://www.mvps.org/access/lookupfields.htm for more information about the evils of lookup fields. A better way would be get rid of the lookup field property (in table design view, change Lookup to text) and then use a combo box on your form to create the lookup. This would work with my sample. -- --Roger Carlson MS Access MVP Access Database Samples: www.rogersaccesslibrary.com Want answers to your Access questions in your Email? Free subscription: http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L "Kathy" wrote in message ... Yes. I have two tables...one tblMedication and the other tblDoses which are linked by the Medication ID. Thank you for your response. -- Kbelo "Roger Carlson" wrote: Are you using Lookup fields in your table? -- --Roger Carlson MS Access MVP Access Database Samples: www.rogersaccesslibrary.com Want answers to your Access questions in your Email? Free subscription: http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L "Kathy" wrote in message ... Hello again...I did download from your library and reconstruct my form according to that model...thank you for your help!...But I have one problem that I have not been able to correct. In the form header that has the unbound controls, I have two combo boxes that are linked so that the selection in the first box determines the selection in the second box. Everything works beautifully except that when I add the new record, the record that appears in the detail section shows the ID of the combo box 1 selection and not the name. (In this example, the first combo box is a list of Medications and the second combo is a list of standard dosages. So when a medication is selected, then only standard doses for this medication shows in the second combo. When saved, the medication ID shows up and not the name of the medication.) Is there some way I can correct this, so the name is shown? Again, many thanks, Kathy -- Kbelo "Roger Carlson" wrote: On my website (www.rogersaccesslibrary.com), is a small Access database sample called "ContinuousFormWithNewRecordOnTop.mdb" which illustrates how to do this. -- --Roger Carlson MS Access MVP Access Database Samples: www.rogersaccesslibrary.com Want answers to your Access questions in your Email? Free subscription: http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L "Kathy" wrote in message ... Hello, I am trying to have a new recordset on a subform open at the top, instead of the bottom. I noticed a similiar posting and the reply suggested using a one-line subform. If this is the best way to accomplish this design, could you please offer a little more information about how to do it? Many thanks. -- Kbelo |
#16
|
|||
|
|||
new recordset entry on top
I know that you are right about this and I can see that it works just like
you explained in another table I am using, but something is missing in this Main/subForm relationship and I just can't locate it. In the relationship table, I can see that the Main and SubForm are connected "one to many" via the ChartNumber. And without the "record on top" structure, it does work automatically. So I am thinking that I messed up something when I restructured for the record on top model...although it works perfectly if I manually add the chart number to the unbound control. Now if I take out the "unbound" Chart Number then the bound chart number stays blank, even tho it is linked to the Main Form chart number. The new generated record goes to the Medication table, but with a blank chart number, so it does not show up with the appropriate patient. I feel like it is probably something simple and basic, but I am just frustrated and hope I am not explaining my situation poorly. Many thanks for your thoughts on this dilemma. -- Kbelo "Roger Carlson" wrote: One of the really nice features of the Subform Control is that it automatically populates the table in the subform with the appropriate key value from the Mainform (assuming the key field is the field used in the Link Parent/Child properties. Since you are using Chart Number as your linking field, you shouldn't need to worry about assigning it. In fact, you don't even need to show that field in the unbound fields at the top. It will be automatically assigned. If you really want to, you can display it as you're doing, but you should NOT write this value to the bound foreign key field in the subform as you are doing with the other fields. As I said, it will get assigned automatically. -- --Roger Carlson MS Access MVP Access Database Samples: www.rogersaccesslibrary.com Want answers to your Access questions in your Email? Free subscription: http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L "Kathy" wrote in message ... Hello again...thank you for answering as I am still hacking away...I am pretty "green" in this area, so I am not confident if I am doing things correctly, but anyway, here is the latest... The Main form and subform are linked via the Chart Number. The subform is the form with the record on top and has the Add/New button. All is working well. I would like that the chart number from the main form be automatically added to the unbound Chart Number control in the subform. So I guessed around and added this code to the Add/New button code, right after Me.Requery: Me.NewChartNumber = Forms!frmAnemiaManagementMain.Form! txtChartNumber So far this is working, each time the button is clicked, the new record is added, then clears the unbound controls, then fills the current chart number in the first unbound control. The dangerous problem is when the user moves to the next patient record, as the chart number doesn't update to the new record. So if the user doesn't change the chart number, it will be saved with the previous record. Thus I have been trying to work with the Main form with the OnCurrent event. But so far, no luck. I hope I am not totally out in left field, but your help is again greatly appreciated. -- Kbelo "Roger Carlson" wrote: I don't think really want to do this. If chart number is the field linking the form and subform (Parent/Child link properties), then it will automatically be added by the subform control and you can just use a bound form (locked). If you are linking on some other field, then you will just be storing redundant information in your medication table. You can easily get the chart number any time by joining the two tables. Have I misunderstood? -- --Roger Carlson MS Access MVP Access Database Samples: www.rogersaccesslibrary.com Want answers to your Access questions in your Email? Free subscription: http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L "Kathy" wrote in message ... Thank you so much again...I am a big chicken and you are right that if the end-users made a mistake and wanted to change the medication, then it could get "ugly"...I am keeping the combo boxes. I am so close to finishing this form, I hope it will be OK to ask another question. Here goes: The form has a main form (patient information) and a subform (the medication record which now has the record on top). I would like that the patient chart number from the main form fills in the unbound text box on the medication record as the end-user moves from chart to chart. Thus when they entered the next patient record, the chart number would automatically show up in the unbound chart number box for the medication record, then when the Add/New button is clicked, the information goes into the detail section and the unbound controls are nulled. Then moves to the next patient and their chart number would automatically fill in....and etc. I have tried all kinds of combinations of code, but either nothing happens or it says I cannot enter a value in that field....and so on. Anyway, I am up a creek again and any help is gratefully accepted. -- Kbelo "Roger Carlson" wrote: No, this is not a dumb question. It's actually fascinating. Yes, it is possible to do what you want, but you have to be *careful*. You should ONLY do it if your detail section is *locked*, that is if the records cannot me changed. I'll explain why in a minute. First of all, here's how to do it: Instead of having just your main table as the record source for your form, create a query that joins the main table with the lookup table(s), displaying both the medication ID number and the medication name in the field list. (Make sure you add the ID field from the *main table* and not from the linked table.) Leave your unbound combos as they are. Now, instead of displaying the medication ID in the field of the form, just display the medication name field. That ought to do it. Now, here's why you only want to do this if the form records are locked. If you go back and change a medication name, it won't be changed in the main table, but it will be changed in the lookup table! That means that every single instance of that medication ID will be changed to the new name. Not what you want, probably. So if you want your user to be able to change the selection (say if they made a mistake and chose the wrong medication), you should leave them as combo boxes. Does that help? -- --Roger Carlson MS Access MVP Access Database Samples: www.rogersaccesslibrary.com Want answers to your Access questions in your Email? Free subscription: http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L "Kathy" wrote in message ... I'm sorry that I probably did not give a very good explanation. I am pretty sure that I am using the correct combo-box construction and it does work perfectly in the "unbound controls Record on Top" model. Also in the detail section when the AddNew button is clicked, the corresponding bound control shows the correct Medication name as long as I keep the control as a combo box. Since once the medication is given, there would no longer be any choice of medication, I wanted to make the corresponding box just show the Medication name that was selected in the unbound control. However when I changed it to a plain text box, then I only get the value or Medication ID. Is there a way to change it to text or should I just leave it as a combo box? Sorry if this is really dumb. And again your help is much appreciated. -- Kbelo "Roger Carlson" wrote: I don't have a lot of experience with lookup fields. We (MVPs and other experienced Access users) discourage their use. The reason is that lookup fields mask what is really happening in the table. See this http://www.mvps.org/access/lookupfields.htm for more information about the evils of lookup fields. A better way would be get rid of the lookup field property (in table design view, change Lookup to text) and then use a combo box on your form to create the lookup. This would work with my sample. -- --Roger Carlson MS Access MVP Access Database Samples: www.rogersaccesslibrary.com Want answers to your Access questions in your Email? Free subscription: http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L "Kathy" wrote in message ... Yes. I have two tables...one tblMedication and the other tblDoses which are linked by the Medication ID. Thank you for your response. -- Kbelo "Roger Carlson" wrote: Are you using Lookup fields in your table? -- --Roger Carlson MS Access MVP Access Database Samples: www.rogersaccesslibrary.com Want answers to your Access questions in your Email? Free subscription: http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L "Kathy" wrote in message ... Hello again...I did download from your library and reconstruct my form according to that model...thank you for your help!...But I have one problem that I have not been able to correct. In the form header that has the unbound controls, I have two combo boxes that are linked so that the selection in the first box determines the selection in the second box. Everything works beautifully except that when I add the new record, the record that appears in the detail section shows the ID of the combo box 1 selection and not the name. (In this example, the first combo box is a list of Medications and the second combo is a list of standard dosages. So when a medication is selected, then only standard doses for this medication shows in the second combo. When saved, the medication ID shows up and not the name of the medication.) Is there some way I can correct this, so the name is shown? Again, many thanks, Kathy -- Kbelo |
#17
|
|||
|
|||
new recordset entry on top
Minor correction in terminology (I used to teach this stuff). You don't see
the main form and subform in the Relationship window, you see the *tables* that these forms are based on. This is an important distinction because this has nothing to do with linking the forms. When I said you "link the form and subform", I'm talking about something different. The subform control has Link Parent and Link Child properties. You need to add the fields that create the relationship in those two properties for the control to automatically add the foreign key value into your subform (ie your Chart Number). If those properties are set correctly, I don't know what could be wrong. -- --Roger Carlson MS Access MVP Access Database Samples: www.rogersaccesslibrary.com Want answers to your Access questions in your Email? Free subscription: http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L "Kathy" wrote in message ... I know that you are right about this and I can see that it works just like you explained in another table I am using, but something is missing in this Main/subForm relationship and I just can't locate it. In the relationship table, I can see that the Main and SubForm are connected "one to many" via the ChartNumber. And without the "record on top" structure, it does work automatically. So I am thinking that I messed up something when I restructured for the record on top model...although it works perfectly if I manually add the chart number to the unbound control. Now if I take out the "unbound" Chart Number then the bound chart number stays blank, even tho it is linked to the Main Form chart number. The new generated record goes to the Medication table, but with a blank chart number, so it does not show up with the appropriate patient. I feel like it is probably something simple and basic, but I am just frustrated and hope I am not explaining my situation poorly. Many thanks for your thoughts on this dilemma. -- Kbelo "Roger Carlson" wrote: One of the really nice features of the Subform Control is that it automatically populates the table in the subform with the appropriate key value from the Mainform (assuming the key field is the field used in the Link Parent/Child properties. Since you are using Chart Number as your linking field, you shouldn't need to worry about assigning it. In fact, you don't even need to show that field in the unbound fields at the top. It will be automatically assigned. If you really want to, you can display it as you're doing, but you should NOT write this value to the bound foreign key field in the subform as you are doing with the other fields. As I said, it will get assigned automatically. -- --Roger Carlson MS Access MVP Access Database Samples: www.rogersaccesslibrary.com Want answers to your Access questions in your Email? Free subscription: http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L "Kathy" wrote in message ... Hello again...thank you for answering as I am still hacking away...I am pretty "green" in this area, so I am not confident if I am doing things correctly, but anyway, here is the latest... The Main form and subform are linked via the Chart Number. The subform is the form with the record on top and has the Add/New button. All is working well. I would like that the chart number from the main form be automatically added to the unbound Chart Number control in the subform. So I guessed around and added this code to the Add/New button code, right after Me.Requery: Me.NewChartNumber = Forms!frmAnemiaManagementMain.Form! txtChartNumber So far this is working, each time the button is clicked, the new record is added, then clears the unbound controls, then fills the current chart number in the first unbound control. The dangerous problem is when the user moves to the next patient record, as the chart number doesn't update to the new record. So if the user doesn't change the chart number, it will be saved with the previous record. Thus I have been trying to work with the Main form with the OnCurrent event. But so far, no luck. I hope I am not totally out in left field, but your help is again greatly appreciated. -- Kbelo "Roger Carlson" wrote: I don't think really want to do this. If chart number is the field linking the form and subform (Parent/Child link properties), then it will automatically be added by the subform control and you can just use a bound form (locked). If you are linking on some other field, then you will just be storing redundant information in your medication table. You can easily get the chart number any time by joining the two tables. Have I misunderstood? -- --Roger Carlson MS Access MVP Access Database Samples: www.rogersaccesslibrary.com Want answers to your Access questions in your Email? Free subscription: http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L "Kathy" wrote in message ... Thank you so much again...I am a big chicken and you are right that if the end-users made a mistake and wanted to change the medication, then it could get "ugly"...I am keeping the combo boxes. I am so close to finishing this form, I hope it will be OK to ask another question. Here goes: The form has a main form (patient information) and a subform (the medication record which now has the record on top). I would like that the patient chart number from the main form fills in the unbound text box on the medication record as the end-user moves from chart to chart. Thus when they entered the next patient record, the chart number would automatically show up in the unbound chart number box for the medication record, then when the Add/New button is clicked, the information goes into the detail section and the unbound controls are nulled. Then moves to the next patient and their chart number would automatically fill in....and etc. I have tried all kinds of combinations of code, but either nothing happens or it says I cannot enter a value in that field....and so on. Anyway, I am up a creek again and any help is gratefully accepted. -- Kbelo "Roger Carlson" wrote: No, this is not a dumb question. It's actually fascinating. Yes, it is possible to do what you want, but you have to be *careful*. You should ONLY do it if your detail section is *locked*, that is if the records cannot me changed. I'll explain why in a minute. First of all, here's how to do it: Instead of having just your main table as the record source for your form, create a query that joins the main table with the lookup table(s), displaying both the medication ID number and the medication name in the field list. (Make sure you add the ID field from the *main table* and not from the linked table.) Leave your unbound combos as they are. Now, instead of displaying the medication ID in the field of the form, just display the medication name field. That ought to do it. Now, here's why you only want to do this if the form records are locked. If you go back and change a medication name, it won't be changed in the main table, but it will be changed in the lookup table! That means that every single instance of that medication ID will be changed to the new name. Not what you want, probably. So if you want your user to be able to change the selection (say if they made a mistake and chose the wrong medication), you should leave them as combo boxes. Does that help? -- --Roger Carlson MS Access MVP Access Database Samples: www.rogersaccesslibrary.com Want answers to your Access questions in your Email? Free subscription: http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L "Kathy" wrote in message ... I'm sorry that I probably did not give a very good explanation. I am pretty sure that I am using the correct combo-box construction and it does work perfectly in the "unbound controls Record on Top" model. Also in the detail section when the AddNew button is clicked, the corresponding bound control shows the correct Medication name as long as I keep the control as a combo box. Since once the medication is given, there would no longer be any choice of medication, I wanted to make the corresponding box just show the Medication name that was selected in the unbound control. However when I changed it to a plain text box, then I only get the value or Medication ID. Is there a way to change it to text or should I just leave it as a combo box? Sorry if this is really dumb. And again your help is much appreciated. -- Kbelo "Roger Carlson" wrote: I don't have a lot of experience with lookup fields. We (MVPs and other experienced Access users) discourage their use. The reason is that lookup fields mask what is really happening in the table. See this http://www.mvps.org/access/lookupfields.htm for more information about the evils of lookup fields. A better way would be get rid of the lookup field property (in table design view, change Lookup to text) and then use a combo box on your form to create the lookup. This would work with my sample. -- --Roger Carlson MS Access MVP Access Database Samples: www.rogersaccesslibrary.com Want answers to your Access questions in your Email? Free subscription: http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L "Kathy" wrote in message ... Yes. I have two tables...one tblMedication and the other tblDoses which are linked by the Medication ID. Thank you for your response. -- Kbelo "Roger Carlson" wrote: Are you using Lookup fields in your table? -- --Roger Carlson MS Access MVP Access Database Samples: www.rogersaccesslibrary.com Want answers to your Access questions in your Email? Free subscription: http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L "Kathy" wrote in message ... Hello again...I did download from your library and reconstruct my form according to that model...thank you for your help!...But I have one problem that I have not been able to correct. In the form header that has the unbound controls, I have two combo boxes that are linked so that the selection in the first box determines the selection in the second box. Everything works beautifully except that when I add the new record, the record that appears in the detail section shows the ID of the combo box 1 selection and not the name. (In this example, the first combo box is a list of Medications and the second combo is a list of standard dosages. So when a medication is selected, then only standard doses for this medication shows in the second combo. When saved, the medication ID shows up and not the name of the medication.) Is there some way I can correct this, so the name is shown? Again, many thanks, Kathy -- Kbelo |
#18
|
|||
|
|||
new recordset entry on top
Gosh, I appreciate your patience so much. I went into the Main form and
subForm and looked under properitiesData, but could see no place where I would enter child or parent link. (I can see the table relationships are correct and the table properties has a place for child link/parent link, but they were empty.) I just feel that I am not getting the forms correct. Meanwhile, I did change the code on the Add/New button to this: rs!intChartNumber = Me.intChartNumber (instead of rs!intChartNumber = Me.NewChartNumber) And they now appear to be working correctly...adding new records and correctly linked. Dare I be optomistic? I still feel uncomfortable that I am not getting the form correct. Feel really "thick" in the head. Your thoughts are my only comfort. -- Kbelo "Roger Carlson" wrote: Minor correction in terminology (I used to teach this stuff). You don't see the main form and subform in the Relationship window, you see the *tables* that these forms are based on. This is an important distinction because this has nothing to do with linking the forms. When I said you "link the form and subform", I'm talking about something different. The subform control has Link Parent and Link Child properties. You need to add the fields that create the relationship in those two properties for the control to automatically add the foreign key value into your subform (ie your Chart Number). If those properties are set correctly, I don't know what could be wrong. -- --Roger Carlson MS Access MVP Access Database Samples: www.rogersaccesslibrary.com Want answers to your Access questions in your Email? Free subscription: http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L "Kathy" wrote in message ... I know that you are right about this and I can see that it works just like you explained in another table I am using, but something is missing in this Main/subForm relationship and I just can't locate it. In the relationship table, I can see that the Main and SubForm are connected "one to many" via the ChartNumber. And without the "record on top" structure, it does work automatically. So I am thinking that I messed up something when I restructured for the record on top model...although it works perfectly if I manually add the chart number to the unbound control. Now if I take out the "unbound" Chart Number then the bound chart number stays blank, even tho it is linked to the Main Form chart number. The new generated record goes to the Medication table, but with a blank chart number, so it does not show up with the appropriate patient. I feel like it is probably something simple and basic, but I am just frustrated and hope I am not explaining my situation poorly. Many thanks for your thoughts on this dilemma. -- Kbelo "Roger Carlson" wrote: One of the really nice features of the Subform Control is that it automatically populates the table in the subform with the appropriate key value from the Mainform (assuming the key field is the field used in the Link Parent/Child properties. Since you are using Chart Number as your linking field, you shouldn't need to worry about assigning it. In fact, you don't even need to show that field in the unbound fields at the top. It will be automatically assigned. If you really want to, you can display it as you're doing, but you should NOT write this value to the bound foreign key field in the subform as you are doing with the other fields. As I said, it will get assigned automatically. -- --Roger Carlson MS Access MVP Access Database Samples: www.rogersaccesslibrary.com Want answers to your Access questions in your Email? Free subscription: http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L "Kathy" wrote in message ... Hello again...thank you for answering as I am still hacking away...I am pretty "green" in this area, so I am not confident if I am doing things correctly, but anyway, here is the latest... The Main form and subform are linked via the Chart Number. The subform is the form with the record on top and has the Add/New button. All is working well. I would like that the chart number from the main form be automatically added to the unbound Chart Number control in the subform. So I guessed around and added this code to the Add/New button code, right after Me.Requery: Me.NewChartNumber = Forms!frmAnemiaManagementMain.Form! txtChartNumber So far this is working, each time the button is clicked, the new record is added, then clears the unbound controls, then fills the current chart number in the first unbound control. The dangerous problem is when the user moves to the next patient record, as the chart number doesn't update to the new record. So if the user doesn't change the chart number, it will be saved with the previous record. Thus I have been trying to work with the Main form with the OnCurrent event. But so far, no luck. I hope I am not totally out in left field, but your help is again greatly appreciated. -- Kbelo "Roger Carlson" wrote: I don't think really want to do this. If chart number is the field linking the form and subform (Parent/Child link properties), then it will automatically be added by the subform control and you can just use a bound form (locked). If you are linking on some other field, then you will just be storing redundant information in your medication table. You can easily get the chart number any time by joining the two tables. Have I misunderstood? -- --Roger Carlson MS Access MVP Access Database Samples: www.rogersaccesslibrary.com Want answers to your Access questions in your Email? Free subscription: http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L "Kathy" wrote in message ... Thank you so much again...I am a big chicken and you are right that if the end-users made a mistake and wanted to change the medication, then it could get "ugly"...I am keeping the combo boxes. I am so close to finishing this form, I hope it will be OK to ask another question. Here goes: The form has a main form (patient information) and a subform (the medication record which now has the record on top). I would like that the patient chart number from the main form fills in the unbound text box on the medication record as the end-user moves from chart to chart. Thus when they entered the next patient record, the chart number would automatically show up in the unbound chart number box for the medication record, then when the Add/New button is clicked, the information goes into the detail section and the unbound controls are nulled. Then moves to the next patient and their chart number would automatically fill in....and etc. I have tried all kinds of combinations of code, but either nothing happens or it says I cannot enter a value in that field....and so on. Anyway, I am up a creek again and any help is gratefully accepted. -- Kbelo "Roger Carlson" wrote: No, this is not a dumb question. It's actually fascinating. Yes, it is possible to do what you want, but you have to be *careful*. You should ONLY do it if your detail section is *locked*, that is if the records cannot me changed. I'll explain why in a minute. First of all, here's how to do it: Instead of having just your main table as the record source for your form, create a query that joins the main table with the lookup table(s), displaying both the medication ID number and the medication name in the field list. (Make sure you add the ID field from the *main table* and not from the linked table.) Leave your unbound combos as they are. Now, instead of displaying the medication ID in the field of the form, just display the medication name field. That ought to do it. Now, here's why you only want to do this if the form records are locked. If you go back and change a medication name, it won't be changed in the main table, but it will be changed in the lookup table! That means that every single instance of that medication ID will be changed to the new name. Not what you want, probably. So if you want your user to be able to change the selection (say if they made a mistake and chose the wrong medication), you should leave them as combo boxes. Does that help? -- --Roger Carlson MS Access MVP Access Database Samples: www.rogersaccesslibrary.com Want answers to your Access questions in your Email? Free subscription: http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L "Kathy" wrote in message ... I'm sorry that I probably did not give a very good explanation. I am pretty sure that I am using the correct combo-box construction and it does work perfectly in the "unbound controls Record on Top" model. Also in the detail section when the AddNew button is clicked, the corresponding bound control shows the correct Medication name as long as I keep the control as a combo box. Since once the medication is given, there would no longer be any choice of medication, I wanted to make the corresponding box just show the Medication name that was selected in the unbound control. However when I changed it to a plain text box, then I only get the value or Medication ID. Is there a way to change it to text or should I just leave it as a combo box? Sorry if this is really dumb. And again your help is much appreciated. -- Kbelo "Roger Carlson" wrote: I don't have a lot of experience with lookup fields. We (MVPs and other experienced Access users) discourage their use. The reason is that lookup fields mask what is really happening in the table. See this http://www.mvps.org/access/lookupfields.htm for more information about the evils of lookup fields. A better way would be get rid of the lookup field property (in table design view, change Lookup to text) and then use a combo box on your form |
#19
|
|||
|
|||
new recordset entry on top
PS: I finally did find the parent/child fields on the subForm (Medication
Record) and they seem correctly linked. The form just does not want to add another record via the Add/New button unless I have the linking field included. Otherwise if I remove the chart number field from the subform, then there is a new record added to the Medication Record table (with blank chart number), but no new record added in the current form in the subform detail section. I feel like it is "right there" but I just can't see it...or I am explaining poorly. Meanwhile, everything seemed to be working after I changed the Add/New button code to rs!intChartNumber = Me.intChartNumber It does work, until I added a brand new patient with no previous records. Then nothing happend, so I changed it to : rs!intChartNumber = Forms!frmAnemiaManagementMain.Forms!intChartNumber So it is working again, but is what I am doing completely crazy? Many thanks, -- Kbelo "Kathy" wrote: Gosh, I appreciate your patience so much. I went into the Main form and subForm and looked under properitiesData, but could see no place where I would enter child or parent link. (I can see the table relationships are correct and the table properties has a place for child link/parent link, but they were empty.) I just feel that I am not getting the forms correct. Meanwhile, I did change the code on the Add/New button to this: rs!intChartNumber = Me.intChartNumber (instead of rs!intChartNumber = Me.NewChartNumber) And they now appear to be working correctly...adding new records and correctly linked. Dare I be optomistic? I still feel uncomfortable that I am not getting the form correct. Feel really "thick" in the head. Your thoughts are my only comfort. -- Kbelo "Roger Carlson" wrote: Minor correction in terminology (I used to teach this stuff). You don't see the main form and subform in the Relationship window, you see the *tables* that these forms are based on. This is an important distinction because this has nothing to do with linking the forms. When I said you "link the form and subform", I'm talking about something different. The subform control has Link Parent and Link Child properties. You need to add the fields that create the relationship in those two properties for the control to automatically add the foreign key value into your subform (ie your Chart Number). If those properties are set correctly, I don't know what could be wrong. -- --Roger Carlson MS Access MVP Access Database Samples: www.rogersaccesslibrary.com Want answers to your Access questions in your Email? Free subscription: http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L "Kathy" wrote in message ... I know that you are right about this and I can see that it works just like you explained in another table I am using, but something is missing in this Main/subForm relationship and I just can't locate it. In the relationship table, I can see that the Main and SubForm are connected "one to many" via the ChartNumber. And without the "record on top" structure, it does work automatically. So I am thinking that I messed up something when I restructured for the record on top model...although it works perfectly if I manually add the chart number to the unbound control. Now if I take out the "unbound" Chart Number then the bound chart number stays blank, even tho it is linked to the Main Form chart number. The new generated record goes to the Medication table, but with a blank chart number, so it does not show up with the appropriate patient. I feel like it is probably something simple and basic, but I am just frustrated and hope I am not explaining my situation poorly. Many thanks for your thoughts on this dilemma. -- Kbelo "Roger Carlson" wrote: One of the really nice features of the Subform Control is that it automatically populates the table in the subform with the appropriate key value from the Mainform (assuming the key field is the field used in the Link Parent/Child properties. Since you are using Chart Number as your linking field, you shouldn't need to worry about assigning it. In fact, you don't even need to show that field in the unbound fields at the top. It will be automatically assigned. If you really want to, you can display it as you're doing, but you should NOT write this value to the bound foreign key field in the subform as you are doing with the other fields. As I said, it will get assigned automatically. -- --Roger Carlson MS Access MVP Access Database Samples: www.rogersaccesslibrary.com Want answers to your Access questions in your Email? Free subscription: http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L "Kathy" wrote in message ... Hello again...thank you for answering as I am still hacking away...I am pretty "green" in this area, so I am not confident if I am doing things correctly, but anyway, here is the latest... The Main form and subform are linked via the Chart Number. The subform is the form with the record on top and has the Add/New button. All is working well. I would like that the chart number from the main form be automatically added to the unbound Chart Number control in the subform. So I guessed around and added this code to the Add/New button code, right after Me.Requery: Me.NewChartNumber = Forms!frmAnemiaManagementMain.Form! txtChartNumber So far this is working, each time the button is clicked, the new record is added, then clears the unbound controls, then fills the current chart number in the first unbound control. The dangerous problem is when the user moves to the next patient record, as the chart number doesn't update to the new record. So if the user doesn't change the chart number, it will be saved with the previous record. Thus I have been trying to work with the Main form with the OnCurrent event. But so far, no luck. I hope I am not totally out in left field, but your help is again greatly appreciated. -- Kbelo "Roger Carlson" wrote: I don't think really want to do this. If chart number is the field linking the form and subform (Parent/Child link properties), then it will automatically be added by the subform control and you can just use a bound form (locked). If you are linking on some other field, then you will just be storing redundant information in your medication table. You can easily get the chart number any time by joining the two tables. Have I misunderstood? -- --Roger Carlson MS Access MVP Access Database Samples: www.rogersaccesslibrary.com Want answers to your Access questions in your Email? Free subscription: http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L "Kathy" wrote in message ... Thank you so much again...I am a big chicken and you are right that if the end-users made a mistake and wanted to change the medication, then it could get "ugly"...I am keeping the combo boxes. I am so close to finishing this form, I hope it will be OK to ask another question. Here goes: The form has a main form (patient information) and a subform (the medication record which now has the record on top). I would like that the patient chart number from the main form fills in the unbound text box on the medication record as the end-user moves from chart to chart. Thus when they entered the next patient record, the chart number would automatically show up in the unbound chart number box for the medication record, then when the Add/New button is clicked, the information goes into the detail section and the unbound controls are nulled. Then moves to the next patient and their chart number would automatically fill in....and etc. I have tried all kinds of combinations of code, but either nothing happens or it says I cannot enter a value in that field....and so on. Anyway, I am up a creek again and any help is gratefully accepted. -- Kbelo "Roger Carlson" wrote: No, this is not a dumb question. It's actually fascinating. Yes, it is possible to do what you want, but you have to be *careful*. You should ONLY do it if your detail section is *locked*, that is if the records cannot me changed. I'll explain why in a minute. First of all, here's how to do it: Instead of having just your main table as the record source for your form, create a query that joins the main table with the lookup table(s), displaying both the medication ID number and the medication name in the field list. (Make sure you add the ID field from the *main table* and not from the linked table.) Leave your unbound combos as they are. Now, instead of displaying the medication ID in the field of the form, just display the medication name field. That ought to do it. Now, here's why you only want to do this if the form records are locked. If you go back and change a medication name, it won't be changed in the main table, but it will be changed in the lookup table! That means that every single instance of that medication ID will be changed to the new name. Not what you want, probably. So if you want your user to be able to change the selection (say if they made a mistake and chose the wrong medication), you should leave them as combo boxes. Does that help? -- --Roger Carlson MS Access MVP Access Database Samples: www.rogersaccesslibrary.com Want answers to your Access questions in your Email? Free subscription: http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L "Kathy" wrote in message ... I'm sorry that I probably did not give a very good explanation. I am pretty sure that I am using the correct combo-box construction and it does work perfectly in the "unbound controls Record on Top" model. Also in the detail section when the AddNew button is clicked, the corresponding bound control shows the correct Medication name as long as I keep the control as a combo box. Since once the medication is given, there would no longer be any choice of medication, I wanted to make the corresponding box just show the Medication name that was selected in the unbound control. However when I changed it to a plain text box, then I only get the value or Medication ID. Is there a way to change it to text or should I just leave it as a combo box? Sorry if this is really dumb. And again your help is much appreciated. -- Kbelo |
#20
|
|||
|
|||
new recordset entry on top
Kathy,
I'm stumped. But as long as it's working, then I wouldn't worry about it. Sometime in the future, you'll undoubted come across a solution when you least expect it. This has happened to me over and over again. -- --Roger Carlson MS Access MVP Access Database Samples: www.rogersaccesslibrary.com Want answers to your Access questions in your Email? Free subscription: http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L "Kathy" wrote in message ... PS: I finally did find the parent/child fields on the subForm (Medication Record) and they seem correctly linked. The form just does not want to add another record via the Add/New button unless I have the linking field included. Otherwise if I remove the chart number field from the subform, then there is a new record added to the Medication Record table (with blank chart number), but no new record added in the current form in the subform detail section. I feel like it is "right there" but I just can't see it...or I am explaining poorly. Meanwhile, everything seemed to be working after I changed the Add/New button code to rs!intChartNumber = Me.intChartNumber It does work, until I added a brand new patient with no previous records. Then nothing happend, so I changed it to : rs!intChartNumber = Forms!frmAnemiaManagementMain.Forms!intChartNumber So it is working again, but is what I am doing completely crazy? Many thanks, -- Kbelo "Kathy" wrote: Gosh, I appreciate your patience so much. I went into the Main form and subForm and looked under properitiesData, but could see no place where I would enter child or parent link. (I can see the table relationships are correct and the table properties has a place for child link/parent link, but they were empty.) I just feel that I am not getting the forms correct. Meanwhile, I did change the code on the Add/New button to this: rs!intChartNumber = Me.intChartNumber (instead of rs!intChartNumber = Me.NewChartNumber) And they now appear to be working correctly...adding new records and correctly linked. Dare I be optomistic? I still feel uncomfortable that I am not getting the form correct. Feel really "thick" in the head. Your thoughts are my only comfort. -- Kbelo "Roger Carlson" wrote: Minor correction in terminology (I used to teach this stuff). You don't see the main form and subform in the Relationship window, you see the *tables* that these forms are based on. This is an important distinction because this has nothing to do with linking the forms. When I said you "link the form and subform", I'm talking about something different. The subform control has Link Parent and Link Child properties. You need to add the fields that create the relationship in those two properties for the control to automatically add the foreign key value into your subform (ie your Chart Number). If those properties are set correctly, I don't know what could be wrong. -- --Roger Carlson MS Access MVP Access Database Samples: www.rogersaccesslibrary.com Want answers to your Access questions in your Email? Free subscription: http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L "Kathy" wrote in message ... I know that you are right about this and I can see that it works just like you explained in another table I am using, but something is missing in this Main/subForm relationship and I just can't locate it. In the relationship table, I can see that the Main and SubForm are connected "one to many" via the ChartNumber. And without the "record on top" structure, it does work automatically. So I am thinking that I messed up something when I restructured for the record on top model...although it works perfectly if I manually add the chart number to the unbound control. Now if I take out the "unbound" Chart Number then the bound chart number stays blank, even tho it is linked to the Main Form chart number. The new generated record goes to the Medication table, but with a blank chart number, so it does not show up with the appropriate patient. I feel like it is probably something simple and basic, but I am just frustrated and hope I am not explaining my situation poorly. Many thanks for your thoughts on this dilemma. -- Kbelo "Roger Carlson" wrote: One of the really nice features of the Subform Control is that it automatically populates the table in the subform with the appropriate key value from the Mainform (assuming the key field is the field used in the Link Parent/Child properties. Since you are using Chart Number as your linking field, you shouldn't need to worry about assigning it. In fact, you don't even need to show that field in the unbound fields at the top. It will be automatically assigned. If you really want to, you can display it as you're doing, but you should NOT write this value to the bound foreign key field in the subform as you are doing with the other fields. As I said, it will get assigned automatically. -- --Roger Carlson MS Access MVP Access Database Samples: www.rogersaccesslibrary.com Want answers to your Access questions in your Email? Free subscription: http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L "Kathy" wrote in message ... Hello again...thank you for answering as I am still hacking away...I am pretty "green" in this area, so I am not confident if I am doing things correctly, but anyway, here is the latest... The Main form and subform are linked via the Chart Number. The subform is the form with the record on top and has the Add/New button. All is working well. I would like that the chart number from the main form be automatically added to the unbound Chart Number control in the subform. So I guessed around and added this code to the Add/New button code, right after Me.Requery: Me.NewChartNumber = Forms!frmAnemiaManagementMain.Form! txtChartNumber So far this is working, each time the button is clicked, the new record is added, then clears the unbound controls, then fills the current chart number in the first unbound control. The dangerous problem is when the user moves to the next patient record, as the chart number doesn't update to the new record. So if the user doesn't change the chart number, it will be saved with the previous record. Thus I have been trying to work with the Main form with the OnCurrent event. But so far, no luck. I hope I am not totally out in left field, but your help is again greatly appreciated. -- Kbelo "Roger Carlson" wrote: I don't think really want to do this. If chart number is the field linking the form and subform (Parent/Child link properties), then it will automatically be added by the subform control and you can just use a bound form (locked). If you are linking on some other field, then you will just be storing redundant information in your medication table. You can easily get the chart number any time by joining the two tables. Have I misunderstood? -- --Roger Carlson MS Access MVP Access Database Samples: www.rogersaccesslibrary.com Want answers to your Access questions in your Email? Free subscription: http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L "Kathy" wrote in message ... Thank you so much again...I am a big chicken and you are right that if the end-users made a mistake and wanted to change the medication, then it could get "ugly"...I am keeping the combo boxes. I am so close to finishing this form, I hope it will be OK to ask another question. Here goes: The form has a main form (patient information) and a subform (the medication record which now has the record on top). I would like that the patient chart number from the main form fills in the unbound text box on the medication record as the end-user moves from chart to chart. Thus when they entered the next patient record, the chart number would automatically show up in the unbound chart number box for the medication record, then when the Add/New button is clicked, the information goes into the detail section and the unbound controls are nulled. Then moves to the next patient and their chart number would automatically fill in....and etc. I have tried all kinds of combinations of code, but either nothing happens or it says I cannot enter a value in that field....and so on. Anyway, I am up a creek again and any help is gratefully accepted. -- Kbelo "Roger Carlson" wrote: No, this is not a dumb question. It's actually fascinating. Yes, it is possible to do what you want, but you have to be *careful*. You should ONLY do it if your detail section is *locked*, that is if the records cannot me changed. I'll explain why in a minute. First of all, here's how to do it: Instead of having just your main table as the record source for your form, create a query that joins the main table with the lookup table(s), displaying both the medication ID number and the medication name in the field list. (Make sure you add the ID field from the *main table* and not from the linked table.) Leave your unbound combos as they are. Now, instead of displaying the medication ID in the field of the form, just display the medication name field. That ought to do it. Now, here's why you only want to do this if the form records are locked. If you go back and change a medication name, it won't be changed in the main table, but it will be changed in the lookup table! That means that every single instance of that medication ID will be changed to the new name. Not what you want, probably. So if you want your user to be able to change the selection (say if they made a mistake and chose the wrong medication), you should leave them as combo boxes. Does that help? -- --Roger Carlson MS Access MVP Access Database Samples: www.rogersaccesslibrary.com Want answers to your Access questions in your Email? Free subscription: http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L "Kathy" wrote in message ... I'm sorry that I probably did not give a very good explanation. I am pretty sure that I am using the correct combo-box construction and it does work perfectly in the "unbound controls Record on Top" model. Also in the detail section when the AddNew button is clicked, the corresponding bound control shows the correct Medication name as long as I keep the control as a combo box. Since once the medication is given, there would no longer be any choice of medication, I wanted to make the corresponding box just show the Medication name that was selected in the unbound control. However when I changed it to a plain text box, then I only get the value or Medication ID. Is there a way to change it to text or should I just leave it as a combo box? Sorry if this is really dumb. And again your help is much appreciated. -- Kbelo |
Thread Tools | |
Display Modes | |
|
|