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
|
|||
|
|||
returning two linked values from a table
|
#13
|
|||
|
|||
returning two linked values from a table
I will try again
where do I find the Northwind example please? "Duane Hookom" wrote: I would rather you try: You can use a combo box with a properties like: Name: cboProcedure Row Source: SELECT [Procedure], [CPT Code] FROM [tblProcCodes] ORDER BY [Procedure]; Then add a text box to your form with the Name: txtCPTCode Control Source: [CPT Code] If you really have a need to store the CPT Code in your operative log table, you will need code in the After Update event of cboProcedure like Me.txtCPTCode = Me.cboProcedure.Column(1) -- Duane Hookom MS Access MVP -- "DrTominRI" wrote in message ... Duane, where/how do I find that example? and I appreciate that it is nothing really short of stupid to store both "red" and "apple" when red=apple, but trust me, if I need to search for the procedure and I don't know the code, or the code and I don't know the procedure I need them both. I am trying to make this so people who are less acess literate than can use it. May I mail you a copy of my test DB to ? Thanks for hanging with me on this Duane Tom "Duane Hookom" wrote: You might want to check the example in the Northwind database. Check the Orders Subform and the ProductID after update. I don't like the use of DLookup() but it should work. I still think you would be better of NOT storing the duplicate values. -- Duane Hookom MS Access MVP -- "DrTominRI" wrote in message ... well duanne it is getting closer, but wierder I hadn't seen the Vbasic box before so that explains why I didn't recognize some of what you were saying. I wrote in the subroutine exactly as you wrote me in a little test database and I got tow things now. I get an "enter paramter value" dialogue box with the name of my procedure_table.ID. and then when I get to the form and check the procedure I want it enters that name in the other box, not the code number. nothing shows up in the box that I chose the procedure from. Tom "Duane Hookom" wrote: You can filter a query with the two tables rather than the one table by itself. If you still want to store the value then create a combo box to selet the Procedure as I noted previously. Add a text box to your form that has a control source of your CPT code field. Name this text box txtCPTCode. Then find the after update event property of your combo box "cboProcedure" and set the property to [Event Procedure] Then click on the builder button on the right [...] and change your code to look like: Private Sub cboProcedure_AfterUpdate() Me.txtCPTCode = Me.cboProcedure.Column(1) End Sub -- Duane Hookom MS Access MVP -- "DrTominRI" wrote in message ... I really think i want to store the data returned from procedure and CPT in the main table so that when I ultimately search my main table I can filter records by either CPT or the procedure name. My problem is that I don't speak enough "access", or at least not fluently I do have a fair idea about what your describing but I am not sure what and where to type it. What part of what your writing should be copied verbatum and what is meant to represent something else? Is there away to create this relationship through a wizard or the event builder? Is there a way I can send you pieced of the DB or a screenshot or something? "Duane Hookom" wrote: Is there a good reason to store both the Procedure and the CPT Code in the Operative Log table? You can use a combo box with a properties like: Name: cboProcedure Row Source: SELECT [Procedure], [CPT Code] FROM [tblProcCodes] ORDER BY [Procedure]; Then add a text box to your form with Control Source: =cboProcedure.Column(1) This will display the [CPT Code] value. If you really have a need to store the CPT Code in your operative log table, you will need code in the After Update event of cboProcedure like Me.txtCPTCode = Me.cboProcedure.Column(1) -- Duane Hookom MS Access MVP -- "DrTominRI" wrote in message ... OK, here we go, I hope this is what you mean the main table is called "operative log" It is meant to be a record of all the operations done by the myself and the three surgeons I work with. the fields in this table include patient name surgeon name medical record # Procedure done CPT Code The table is associated with a form named op log form on the form there are text boxes to fill in typed information the physicians name is linked to a table named physicians and that works perfectly fine, you click on it and the four names pop down and you pick one and it successfully returns it to the table. Now here is the question. I have another table with two fields named procedure and CPT code in there I have about 30 of the most common operations and a code number associated with each operation name. I want to create two boxes on my op log form so that when I click on the combo box named "procedure done" I see my list of operations and when I choose the one I want it is returned to that field on the form AND the CPT code next to it in the table fills the CPT code box on the form and in the main table. I know that is not specifically what you asked, but the data base is at work and I am answering this from home Thanks for your help "Duane Hookom" wrote: Can you provide these properties Combo box: Name: Control Source: Bound Column: Column Count: Row Source: Column Widths: Other text box that you might want to display something: Name: Control Source: what do you want to display here? -- Duane Hookom MS Access MVP -- "DrTominRI" wrote in message ... I have a feeling if I knew what that meant I would also know how to do what I am trying to do - I am smiling while I write that. But honestly I have no idea what that means. When/where do I write the line below? If I have tow separate data fields, [Diagnosis] and [ICD-9] Will it then return both the text and the number to [diagnosis]on my form? the objective is to choose the diagnosis from the combo box and when I click on "appendicitis" the ICD-9 box automatically fills with the nuber next to it on the source table. "Duane Hookom" wrote: You can display additional columns from your combo box with syntax like: =cboDiagnosis.Column(x) "x" is the zero-based column number from the Row Source property of the combo box. A value of 2 will display the 3rd column. This will not work with storing the other column in your form's record source. This would normally not be good practice anyway. -- Duane Hookom MS Access MVP -- "DrTominRI" wrote in message ... I have a very limited database but in it I want to put in a combo box that will look up a piece of information from another table and return not only that bit of data but other parts of the record on that other table. |
#14
|
|||
|
|||
returning two linked values from a table
It normally gets installed in a samples directory depending on your version
of Office. On my PC it is: C:\Program Files\Microsoft Office\Office10\Samples\Northwind.mdb -- Duane Hookom MS Access MVP -- "DrTominRI" wrote in message ... I will try again where do I find the Northwind example please? "Duane Hookom" wrote: I would rather you try: You can use a combo box with a properties like: Name: cboProcedure Row Source: SELECT [Procedure], [CPT Code] FROM [tblProcCodes] ORDER BY [Procedure]; Then add a text box to your form with the Name: txtCPTCode Control Source: [CPT Code] If you really have a need to store the CPT Code in your operative log table, you will need code in the After Update event of cboProcedure like Me.txtCPTCode = Me.cboProcedure.Column(1) -- Duane Hookom MS Access MVP -- "DrTominRI" wrote in message ... Duane, where/how do I find that example? and I appreciate that it is nothing really short of stupid to store both "red" and "apple" when red=apple, but trust me, if I need to search for the procedure and I don't know the code, or the code and I don't know the procedure I need them both. I am trying to make this so people who are less acess literate than can use it. May I mail you a copy of my test DB to ? Thanks for hanging with me on this Duane Tom "Duane Hookom" wrote: You might want to check the example in the Northwind database. Check the Orders Subform and the ProductID after update. I don't like the use of DLookup() but it should work. I still think you would be better of NOT storing the duplicate values. -- Duane Hookom MS Access MVP -- "DrTominRI" wrote in message ... well duanne it is getting closer, but wierder I hadn't seen the Vbasic box before so that explains why I didn't recognize some of what you were saying. I wrote in the subroutine exactly as you wrote me in a little test database and I got tow things now. I get an "enter paramter value" dialogue box with the name of my procedure_table.ID. and then when I get to the form and check the procedure I want it enters that name in the other box, not the code number. nothing shows up in the box that I chose the procedure from. Tom "Duane Hookom" wrote: You can filter a query with the two tables rather than the one table by itself. If you still want to store the value then create a combo box to selet the Procedure as I noted previously. Add a text box to your form that has a control source of your CPT code field. Name this text box txtCPTCode. Then find the after update event property of your combo box "cboProcedure" and set the property to [Event Procedure] Then click on the builder button on the right [...] and change your code to look like: Private Sub cboProcedure_AfterUpdate() Me.txtCPTCode = Me.cboProcedure.Column(1) End Sub -- Duane Hookom MS Access MVP -- "DrTominRI" wrote in message ... I really think i want to store the data returned from procedure and CPT in the main table so that when I ultimately search my main table I can filter records by either CPT or the procedure name. My problem is that I don't speak enough "access", or at least not fluently I do have a fair idea about what your describing but I am not sure what and where to type it. What part of what your writing should be copied verbatum and what is meant to represent something else? Is there away to create this relationship through a wizard or the event builder? Is there a way I can send you pieced of the DB or a screenshot or something? "Duane Hookom" wrote: Is there a good reason to store both the Procedure and the CPT Code in the Operative Log table? You can use a combo box with a properties like: Name: cboProcedure Row Source: SELECT [Procedure], [CPT Code] FROM [tblProcCodes] ORDER BY [Procedure]; Then add a text box to your form with Control Source: =cboProcedure.Column(1) This will display the [CPT Code] value. If you really have a need to store the CPT Code in your operative log table, you will need code in the After Update event of cboProcedure like Me.txtCPTCode = Me.cboProcedure.Column(1) -- Duane Hookom MS Access MVP -- "DrTominRI" wrote in message ... OK, here we go, I hope this is what you mean the main table is called "operative log" It is meant to be a record of all the operations done by the myself and the three surgeons I work with. the fields in this table include patient name surgeon name medical record # Procedure done CPT Code The table is associated with a form named op log form on the form there are text boxes to fill in typed information the physicians name is linked to a table named physicians and that works perfectly fine, you click on it and the four names pop down and you pick one and it successfully returns it to the table. Now here is the question. I have another table with two fields named procedure and CPT code in there I have about 30 of the most common operations and a code number associated with each operation name. I want to create two boxes on my op log form so that when I click on the combo box named "procedure done" I see my list of operations and when I choose the one I want it is returned to that field on the form AND the CPT code next to it in the table fills the CPT code box on the form and in the main table. I know that is not specifically what you asked, but the data base is at work and I am answering this from home Thanks for your help "Duane Hookom" wrote: Can you provide these properties Combo box: Name: Control Source: Bound Column: Column Count: Row Source: Column Widths: Other text box that you might want to display something: Name: Control Source: what do you want to display here? -- Duane Hookom MS Access MVP -- "DrTominRI" wrote in message ... I have a feeling if I knew what that meant I would also know how to do what I am trying to do - I am smiling while I write that. But honestly I have no idea what that means. When/where do I write the line below? If I have tow separate data fields, [Diagnosis] and [ICD-9] Will it then return both the text and the number to [diagnosis]on my form? the objective is to choose the diagnosis from the combo box and when I click on "appendicitis" the ICD-9 box automatically fills with the nuber next to it on the source table. "Duane Hookom" wrote: You can display additional columns from your combo box with syntax like: =cboDiagnosis.Column(x) "x" is the zero-based column number from the Row Source property of the combo box. A value of 2 will display the 3rd column. This will not work with storing the other column in your form's record source. This would normally not be good practice anyway. -- Duane Hookom MS Access MVP -- "DrTominRI" wrote in message ... I have a very limited database but in it I want to put in a combo box that will look up a piece of information from another table and return not only that bit of data but other parts of the record on that other table. |
#15
|
|||
|
|||
returning two linked values from a table
OK Duane. I found the example and that is EXACTLY what I want it to do.
you have warn me down and I wont try to store the secondary value, but I might come back to you when I am building my queries. BUT I am still not getting it to work. I think I don't know enough about the "Me" thing. Is the syntax wrong. In the Northwind example it is Me! not Me. does that matter? I tried to search help for Me but I got nothing. Please from scratch, as Danzel Washington said in the movie Philadelphia " like I am a three year old, take me through this one thing and I will leave you alone Thanks for hanging with me Tom "Duane Hookom" wrote: It normally gets installed in a samples directory depending on your version of Office. On my PC it is: C:\Program Files\Microsoft Office\Office10\Samples\Northwind.mdb -- Duane Hookom MS Access MVP -- "DrTominRI" wrote in message ... I will try again where do I find the Northwind example please? "Duane Hookom" wrote: I would rather you try: You can use a combo box with a properties like: Name: cboProcedure Row Source: SELECT [Procedure], [CPT Code] FROM [tblProcCodes] ORDER BY [Procedure]; Then add a text box to your form with the Name: txtCPTCode Control Source: [CPT Code] If you really have a need to store the CPT Code in your operative log table, you will need code in the After Update event of cboProcedure like Me.txtCPTCode = Me.cboProcedure.Column(1) -- Duane Hookom MS Access MVP -- "DrTominRI" wrote in message ... Duane, where/how do I find that example? and I appreciate that it is nothing really short of stupid to store both "red" and "apple" when red=apple, but trust me, if I need to search for the procedure and I don't know the code, or the code and I don't know the procedure I need them both. I am trying to make this so people who are less acess literate than can use it. May I mail you a copy of my test DB to ? Thanks for hanging with me on this Duane Tom "Duane Hookom" wrote: You might want to check the example in the Northwind database. Check the Orders Subform and the ProductID after update. I don't like the use of DLookup() but it should work. I still think you would be better of NOT storing the duplicate values. -- Duane Hookom MS Access MVP -- "DrTominRI" wrote in message ... well duanne it is getting closer, but wierder I hadn't seen the Vbasic box before so that explains why I didn't recognize some of what you were saying. I wrote in the subroutine exactly as you wrote me in a little test database and I got tow things now. I get an "enter paramter value" dialogue box with the name of my procedure_table.ID. and then when I get to the form and check the procedure I want it enters that name in the other box, not the code number. nothing shows up in the box that I chose the procedure from. Tom "Duane Hookom" wrote: You can filter a query with the two tables rather than the one table by itself. If you still want to store the value then create a combo box to selet the Procedure as I noted previously. Add a text box to your form that has a control source of your CPT code field. Name this text box txtCPTCode. Then find the after update event property of your combo box "cboProcedure" and set the property to [Event Procedure] Then click on the builder button on the right [...] and change your code to look like: Private Sub cboProcedure_AfterUpdate() Me.txtCPTCode = Me.cboProcedure.Column(1) End Sub -- Duane Hookom MS Access MVP -- "DrTominRI" wrote in message ... I really think i want to store the data returned from procedure and CPT in the main table so that when I ultimately search my main table I can filter records by either CPT or the procedure name. My problem is that I don't speak enough "access", or at least not fluently I do have a fair idea about what your describing but I am not sure what and where to type it. What part of what your writing should be copied verbatum and what is meant to represent something else? Is there away to create this relationship through a wizard or the event builder? Is there a way I can send you pieced of the DB or a screenshot or something? "Duane Hookom" wrote: Is there a good reason to store both the Procedure and the CPT Code in the Operative Log table? You can use a combo box with a properties like: Name: cboProcedure Row Source: SELECT [Procedure], [CPT Code] FROM [tblProcCodes] ORDER BY [Procedure]; Then add a text box to your form with Control Source: =cboProcedure.Column(1) This will display the [CPT Code] value. If you really have a need to store the CPT Code in your operative log table, you will need code in the After Update event of cboProcedure like Me.txtCPTCode = Me.cboProcedure.Column(1) -- Duane Hookom MS Access MVP -- "DrTominRI" wrote in message ... OK, here we go, I hope this is what you mean the main table is called "operative log" It is meant to be a record of all the operations done by the myself and the three surgeons I work with. the fields in this table include patient name surgeon name medical record # Procedure done CPT Code The table is associated with a form named op log form on the form there are text boxes to fill in typed information the physicians name is linked to a table named physicians and that works perfectly fine, you click on it and the four names pop down and you pick one and it successfully returns it to the table. Now here is the question. I have another table with two fields named procedure and CPT code in there I have about 30 of the most common operations and a code number associated with each operation name. I want to create two boxes on my op log form so that when I click on the combo box named "procedure done" I see my list of operations and when I choose the one I want it is returned to that field on the form AND the CPT code next to it in the table fills the CPT code box on the form and in the main table. I know that is not specifically what you asked, but the data base is at work and I am answering this from home Thanks for your help "Duane Hookom" wrote: Can you provide these properties Combo box: Name: Control Source: Bound Column: Column Count: Row Source: Column Widths: Other text box that you might want to display something: Name: Control Source: what do you want to display here? -- Duane Hookom MS Access MVP -- "DrTominRI" wrote in message ... I have a feeling if I knew what that meant I would also know how to do what I am trying to do - I am smiling while I write that. But honestly I have no idea what that means. When/where do I write the line below? If I have tow separate data fields, [Diagnosis] and [ICD-9] Will it then return both the text and the number to [diagnosis]on my form? the objective is to choose the |
#16
|
|||
|
|||
returning two linked values from a table
If you really still need help, please come back with this information
First Table Name: Field Names: Second Table Name: Field Names: Combo box: Name: Control Source: Bound Column: Column Count: Row Source: Column Widths: Other text box that you might want to display something: Name: Control Source: what do you want to display here? -- Duane Hookom MS Access MVP -- "DrTominRI" wrote in message ... OK Duane. I found the example and that is EXACTLY what I want it to do. you have warn me down and I wont try to store the secondary value, but I might come back to you when I am building my queries. BUT I am still not getting it to work. I think I don't know enough about the "Me" thing. Is the syntax wrong. In the Northwind example it is Me! not Me. does that matter? I tried to search help for Me but I got nothing. Please from scratch, as Danzel Washington said in the movie Philadelphia " like I am a three year old, take me through this one thing and I will leave you alone Thanks for hanging with me Tom "Duane Hookom" wrote: It normally gets installed in a samples directory depending on your version of Office. On my PC it is: C:\Program Files\Microsoft Office\Office10\Samples\Northwind.mdb -- Duane Hookom MS Access MVP -- "DrTominRI" wrote in message ... I will try again where do I find the Northwind example please? "Duane Hookom" wrote: I would rather you try: You can use a combo box with a properties like: Name: cboProcedure Row Source: SELECT [Procedure], [CPT Code] FROM [tblProcCodes] ORDER BY [Procedure]; Then add a text box to your form with the Name: txtCPTCode Control Source: [CPT Code] If you really have a need to store the CPT Code in your operative log table, you will need code in the After Update event of cboProcedure like Me.txtCPTCode = Me.cboProcedure.Column(1) -- Duane Hookom MS Access MVP -- "DrTominRI" wrote in message ... Duane, where/how do I find that example? and I appreciate that it is nothing really short of stupid to store both "red" and "apple" when red=apple, but trust me, if I need to search for the procedure and I don't know the code, or the code and I don't know the procedure I need them both. I am trying to make this so people who are less acess literate than can use it. May I mail you a copy of my test DB to ? Thanks for hanging with me on this Duane Tom "Duane Hookom" wrote: You might want to check the example in the Northwind database. Check the Orders Subform and the ProductID after update. I don't like the use of DLookup() but it should work. I still think you would be better of NOT storing the duplicate values. -- Duane Hookom MS Access MVP -- "DrTominRI" wrote in message ... well duanne it is getting closer, but wierder I hadn't seen the Vbasic box before so that explains why I didn't recognize some of what you were saying. I wrote in the subroutine exactly as you wrote me in a little test database and I got tow things now. I get an "enter paramter value" dialogue box with the name of my procedure_table.ID. and then when I get to the form and check the procedure I want it enters that name in the other box, not the code number. nothing shows up in the box that I chose the procedure from. Tom "Duane Hookom" wrote: You can filter a query with the two tables rather than the one table by itself. If you still want to store the value then create a combo box to selet the Procedure as I noted previously. Add a text box to your form that has a control source of your CPT code field. Name this text box txtCPTCode. Then find the after update event property of your combo box "cboProcedure" and set the property to [Event Procedure] Then click on the builder button on the right [...] and change your code to look like: Private Sub cboProcedure_AfterUpdate() Me.txtCPTCode = Me.cboProcedure.Column(1) End Sub -- Duane Hookom MS Access MVP -- "DrTominRI" wrote in message ... I really think i want to store the data returned from procedure and CPT in the main table so that when I ultimately search my main table I can filter records by either CPT or the procedure name. My problem is that I don't speak enough "access", or at least not fluently I do have a fair idea about what your describing but I am not sure what and where to type it. What part of what your writing should be copied verbatum and what is meant to represent something else? Is there away to create this relationship through a wizard or the event builder? Is there a way I can send you pieced of the DB or a screenshot or something? "Duane Hookom" wrote: Is there a good reason to store both the Procedure and the CPT Code in the Operative Log table? You can use a combo box with a properties like: Name: cboProcedure Row Source: SELECT [Procedure], [CPT Code] FROM [tblProcCodes] ORDER BY [Procedure]; Then add a text box to your form with Control Source: =cboProcedure.Column(1) This will display the [CPT Code] value. If you really have a need to store the CPT Code in your operative log table, you will need code in the After Update event of cboProcedure like Me.txtCPTCode = Me.cboProcedure.Column(1) -- Duane Hookom MS Access MVP -- "DrTominRI" wrote in message ... OK, here we go, I hope this is what you mean the main table is called "operative log" It is meant to be a record of all the operations done by the myself and the three surgeons I work with. the fields in this table include patient name surgeon name medical record # Procedure done CPT Code The table is associated with a form named op log form on the form there are text boxes to fill in typed information the physicians name is linked to a table named physicians and that works perfectly fine, you click on it and the four names pop down and you pick one and it successfully returns it to the table. Now here is the question. I have another table with two fields named procedure and CPT code in there I have about 30 of the most common operations and a code number associated with each operation name. I want to create two boxes on my op log form so that when I click on the combo box named "procedure done" I see my list of operations and when I choose the one I want it is returned to that field on the form AND the CPT code next to it in the table fills the CPT code box on the form and in the main table. I know that is not specifically what you asked, but the data base is at work and I am answering this from home Thanks for your help "Duane Hookom" wrote: Can you provide these properties Combo box: Name: Control Source: Bound Column: Column Count: Row Source: Column Widths: Other text box that you might want to display something: Name: Control Source: what do you want to display here? -- Duane Hookom MS Access MVP -- "DrTominRI" wrote in message ... I have a feeling if I knew what that meant I would also know how to do what I am trying to do - I am smiling while I write that. But honestly I have no idea what that means. When/where do I write the line below? If I have tow separate data fields, [Diagnosis] and [ICD-9] Will it then return both the text and the number to [diagnosis]on my form? the objective is to choose the |
#17
|
|||
|
|||
returning two linked values from a table
Duane,
here is the info you requested, copied verbaitim First Table Name: Main Table Field Names: ID, auto number Last Name, text First Name,text Date of Birth,date Med Rec #, integer Date of Surgery, Date Procedure, text CPT Code, integer Diagnosis, text ICD-9, decimal Location of Procedure, text Attending Surgeon, text Fellow performed, yes/no Date Billed, date Comments, text Second Table Name: Procedure Field Names: ID, autonumber Procedure, text CPT Code, integer Combo box:in form [case log form] Name: procedure Control Source: procedure Bound Column:1 Column Count:3 Row Source: {i originally built this using the wizard but I may have changed it around} SELECT Procedure.ID, Procedure.Procedure, Procedure.[CPT code] FROM [Procedure] ORDER BY Procedure.Procedure; Column Widths: 0";1";0.5625" Other text box that you might want to display something: Name: CPT Code Control Source:right now, blank what do you want to display here? I want it to show the unique CPT code from column 3 of the table "procedure" that is associated with a procedure in column 2 of that table. The user chooses the procedure from the pull down list and the CPT fills in - just like in that Northwind example. Thanks, Tom -- Duane Hookom MS Access MVP -- "DrTominRI" wrote in message ... OK Duane. I found the example and that is EXACTLY what I want it to do. you have warn me down and I wont try to store the secondary value, but I might come back to you when I am building my queries. BUT I am still not getting it to work. I think I don't know enough about the "Me" thing. Is the syntax wrong. In the Northwind example it is Me! not Me. does that matter? I tried to search help for Me but I got nothing. Please from scratch, as Danzel Washington said in the movie Philadelphia " like I am a three year old, take me through this one thing and I will leave you alone Thanks for hanging with me Tom "Duane Hookom" wrote: It normally gets installed in a samples directory depending on your version of Office. On my PC it is: C:\Program Files\Microsoft Office\Office10\Samples\Northwind.mdb -- Duane Hookom MS Access MVP -- "DrTominRI" wrote in message ... I will try again where do I find the Northwind example please? "Duane Hookom" wrote: I would rather you try: You can use a combo box with a properties like: Name: cboProcedure Row Source: SELECT [Procedure], [CPT Code] FROM [tblProcCodes] ORDER BY [Procedure]; Then add a text box to your form with the Name: txtCPTCode Control Source: [CPT Code] If you really have a need to store the CPT Code in your operative log table, you will need code in the After Update event of cboProcedure like Me.txtCPTCode = Me.cboProcedure.Column(1) -- Duane Hookom MS Access MVP -- "DrTominRI" wrote in message ... Duane, where/how do I find that example? and I appreciate that it is nothing really short of stupid to store both "red" and "apple" when red=apple, but trust me, if I need to search for the procedure and I don't know the code, or the code and I don't know the procedure I need them both. I am trying to make this so people who are less acess literate than can use it. May I mail you a copy of my test DB to ? Thanks for hanging with me on this Duane Tom "Duane Hookom" wrote: You might want to check the example in the Northwind database. Check the Orders Subform and the ProductID after update. I don't like the use of DLookup() but it should work. I still think you would be better of NOT storing the duplicate values. -- Duane Hookom MS Access MVP -- "DrTominRI" wrote in message ... well duanne it is getting closer, but wierder I hadn't seen the Vbasic box before so that explains why I didn't recognize some of what you were saying. I wrote in the subroutine exactly as you wrote me in a little test database and I got tow things now. I get an "enter paramter value" dialogue box with the name of my procedure_table.ID. and then when I get to the form and check the procedure I want it enters that name in the other box, not the code number. nothing shows up in the box that I chose the procedure from. Tom "Duane Hookom" wrote: You can filter a query with the two tables rather than the one table by itself. If you still want to store the value then create a combo box to selet the Procedure as I noted previously. Add a text box to your form that has a control source of your CPT code field. Name this text box txtCPTCode. Then find the after update event property of your combo box "cboProcedure" and set the property to [Event Procedure] Then click on the builder button on the right [...] and change your code to look like: Private Sub cboProcedure_AfterUpdate() Me.txtCPTCode = Me.cboProcedure.Column(1) End Sub -- Duane Hookom MS Access MVP -- "DrTominRI" wrote in message ... I really think i want to store the data returned from procedure and CPT in the main table so that when I ultimately search my main table I can filter records by either CPT or the procedure name. My problem is that I don't speak enough "access", or at least not fluently I do have a fair idea about what your describing but I am not sure what and where to type it. What part of what your writing should be copied verbatum and what is meant to represent something else? Is there away to create this relationship through a wizard or the event builder? Is there a way I can send you pieced of the DB or a screenshot or something? "Duane Hookom" wrote: Is there a good reason to store both the Procedure and the CPT Code in the Operative Log table? You can use a combo box with a properties like: Name: cboProcedure Row Source: SELECT [Procedure], [CPT Code] FROM [tblProcCodes] ORDER BY [Procedure]; Then add a text box to your form with Control Source: =cboProcedure.Column(1) This will display the [CPT Code] value. If you really have a need to store the CPT Code in your operative log table, you will need code in the After Update event of cboProcedure like Me.txtCPTCode = Me.cboProcedure.Column(1) -- Duane Hookom MS Access MVP -- "DrTominRI" wrote in message ... OK, here we go, I hope this is what you mean the main table is called "operative log" It is meant to be a record of all the operations done by the myself and the three surgeons I work with. the fields in this table include patient name surgeon name medical record # Procedure done CPT Code The table is associated with a form named op log form on the form there are text boxes to fill in typed information the physicians name is linked to a table named physicians and that works perfectly fine, you click on it and the four names pop down and you pick one and it successfully returns it to the table. Now here is the question. I have another table with two fields named procedure and CPT code in there I have about 30 of the most common operations and a code number associated with each operation name. |
#18
|
|||
|
|||
returning two linked values from a table
Tom,
I believe you have an issue that you might be storing the ID from the Procedure table in the Main Table. Your combo box is bound to the first column of the Row Source which is the ID (numeric) while the Procedure field in the Main Table is text. Try add a text box to your form and set the control source to [Procedure]. Do you see text or a number? The way you seem to want your application to behave suggests you want to store the Procedure field from the Procedure table in the Procedure field of the Main Table. I don't think you even want to use an ID field in your Procedure table. If you need to store the Procedure and the CPT Code in the first table, make these changes to your controls: Combo box:in form [case log form] Name: cboProcedure Row Source: SELECT Procedure, [CPT code] FROM [Procedure] ORDER BY Procedure; Column Count:2 Bound Column:1 Column Widths: 1";0.5625" Other text box that you might want to display something: Name: txtCPTCode Control Source:[CPT Code] While in design view of your form, select the combo box (cboProcedure) and find the After Update event property. 1) Click the builder button [...] 2) select "Code Builder" and click "OK" 3) change the code to read Private Sub cboProcedure_AfterUpdate() Me.txtCPTCode = Me.cboProcedure.Column(1) End Sub -- Duane Hookom MS Access MVP -- "DrTominRI" wrote in message news Duane, here is the info you requested, copied verbaitim First Table Name: Main Table Field Names: ID, auto number Last Name, text First Name,text Date of Birth,date Med Rec #, integer Date of Surgery, Date Procedure, text CPT Code, integer Diagnosis, text ICD-9, decimal Location of Procedure, text Attending Surgeon, text Fellow performed, yes/no Date Billed, date Comments, text Second Table Name: Procedure Field Names: ID, autonumber Procedure, text CPT Code, integer Combo box:in form [case log form] Name: procedure Control Source: procedure Bound Column:1 Column Count:3 Row Source: {i originally built this using the wizard but I may have changed it around} SELECT Procedure.ID, Procedure.Procedure, Procedure.[CPT code] FROM [Procedure] ORDER BY Procedure.Procedure; Column Widths: 0";1";0.5625" Other text box that you might want to display something: Name: CPT Code Control Source:right now, blank what do you want to display here? I want it to show the unique CPT code from column 3 of the table "procedure" that is associated with a procedure in column 2 of that table. The user chooses the procedure from the pull down list and the CPT fills in - just like in that Northwind example. Thanks, Tom -- Duane Hookom MS Access MVP -- "DrTominRI" wrote in message ... OK Duane. I found the example and that is EXACTLY what I want it to do. you have warn me down and I wont try to store the secondary value, but I might come back to you when I am building my queries. BUT I am still not getting it to work. I think I don't know enough about the "Me" thing. Is the syntax wrong. In the Northwind example it is Me! not Me. does that matter? I tried to search help for Me but I got nothing. Please from scratch, as Danzel Washington said in the movie Philadelphia " like I am a three year old, take me through this one thing and I will leave you alone Thanks for hanging with me Tom "Duane Hookom" wrote: It normally gets installed in a samples directory depending on your version of Office. On my PC it is: C:\Program Files\Microsoft Office\Office10\Samples\Northwind.mdb -- Duane Hookom MS Access MVP -- "DrTominRI" wrote in message ... I will try again where do I find the Northwind example please? "Duane Hookom" wrote: I would rather you try: You can use a combo box with a properties like: Name: cboProcedure Row Source: SELECT [Procedure], [CPT Code] FROM [tblProcCodes] ORDER BY [Procedure]; Then add a text box to your form with the Name: txtCPTCode Control Source: [CPT Code] If you really have a need to store the CPT Code in your operative log table, you will need code in the After Update event of cboProcedure like Me.txtCPTCode = Me.cboProcedure.Column(1) -- Duane Hookom MS Access MVP -- "DrTominRI" wrote in message ... Duane, where/how do I find that example? and I appreciate that it is nothing really short of stupid to store both "red" and "apple" when red=apple, but trust me, if I need to search for the procedure and I don't know the code, or the code and I don't know the procedure I need them both. I am trying to make this so people who are less acess literate than can use it. May I mail you a copy of my test DB to ? Thanks for hanging with me on this Duane Tom "Duane Hookom" wrote: You might want to check the example in the Northwind database. Check the Orders Subform and the ProductID after update. I don't like the use of DLookup() but it should work. I still think you would be better of NOT storing the duplicate values. -- Duane Hookom MS Access MVP -- "DrTominRI" wrote in message ... well duanne it is getting closer, but wierder I hadn't seen the Vbasic box before so that explains why I didn't recognize some of what you were saying. I wrote in the subroutine exactly as you wrote me in a little test database and I got tow things now. I get an "enter paramter value" dialogue box with the name of my procedure_table.ID. and then when I get to the form and check the procedure I want it enters that name in the other box, not the code number. nothing shows up in the box that I chose the procedure from. Tom "Duane Hookom" wrote: You can filter a query with the two tables rather than the one table by itself. If you still want to store the value then create a combo box to selet the Procedure as I noted previously. Add a text box to your form that has a control source of your CPT code field. Name this text box txtCPTCode. Then find the after update event property of your combo box "cboProcedure" and set the property to [Event Procedure] Then click on the builder button on the right [...] and change your code to look like: Private Sub cboProcedure_AfterUpdate() Me.txtCPTCode = Me.cboProcedure.Column(1) End Sub -- Duane Hookom MS Access MVP -- "DrTominRI" wrote in message ... I really think i want to store the data returned from procedure and CPT in the main table so that when I ultimately search my main table I can filter records by either CPT or the procedure name. My problem is that I don't speak enough "access", or at least not fluently I do have a fair idea about what your describing but I am not sure what and where to type it. What part of what your writing should be copied verbatum and what is meant to represent something else? Is there away to create this relationship through a wizard or the event builder? Is there a way I can send you pieced of the DB or a screenshot or something? "Duane Hookom" wrote: Is there a good reason to store both the Procedure and the CPT Code in the Operative Log table? You can use a combo box with a properties like: Name: cboProcedure Row Source: SELECT [Procedure], [CPT Code] FROM [tblProcCodes] ORDER BY [Procedure]; Then add a text box to your form with Control Source: =cboProcedure.Column(1) This will display the [CPT Code] value. If you really have a need to store the CPT Code in your operative log table, you will need code in the After Update event of cboProcedure like Me.txtCPTCode = Me.cboProcedure.Column(1) -- Duane Hookom MS Access MVP -- "DrTominRI" wrote in message ... OK, here we go, I hope this is what you mean the main table is called "operative log" It is meant to be a record of all the operations done by the myself and the three surgeons I work with. the fields in this table include patient name surgeon name medical record # Procedure done CPT Code The table is associated with a form named op log form on the form there are text boxes to fill in typed information the physicians name is linked to a table named physicians and that works perfectly fine, you click on it and the four names pop down and you pick one and it successfully returns it to the table. Now here is the question. I have another table with two fields named procedure and CPT code in there I have about 30 of the most common operations and a code number associated with each operation name. |
#19
|
|||
|
|||
returning two linked values from a table
Duanne,
100% unmitigated success! Thank you! Tom "Duane Hookom" wrote: Tom, I believe you have an issue that you might be storing the ID from the Procedure table in the Main Table. Your combo box is bound to the first column of the Row Source which is the ID (numeric) while the Procedure field in the Main Table is text. Try add a text box to your form and set the control source to [Procedure]. Do you see text or a number? The way you seem to want your application to behave suggests you want to store the Procedure field from the Procedure table in the Procedure field of the Main Table. I don't think you even want to use an ID field in your Procedure table. If you need to store the Procedure and the CPT Code in the first table, make these changes to your controls: Combo box:in form [case log form] Name: cboProcedure Row Source: SELECT Procedure, [CPT code] FROM [Procedure] ORDER BY Procedure; Column Count:2 Bound Column:1 Column Widths: 1";0.5625" Other text box that you might want to display something: Name: txtCPTCode Control Source:[CPT Code] While in design view of your form, select the combo box (cboProcedure) and find the After Update event property. 1) Click the builder button [...] 2) select "Code Builder" and click "OK" 3) change the code to read Private Sub cboProcedure_AfterUpdate() Me.txtCPTCode = Me.cboProcedure.Column(1) End Sub -- Duane Hookom MS Access MVP -- "DrTominRI" wrote in message news Duane, here is the info you requested, copied verbaitim First Table Name: Main Table Field Names: ID, auto number Last Name, text First Name,text Date of Birth,date Med Rec #, integer Date of Surgery, Date Procedure, text CPT Code, integer Diagnosis, text ICD-9, decimal Location of Procedure, text Attending Surgeon, text Fellow performed, yes/no Date Billed, date Comments, text Second Table Name: Procedure Field Names: ID, autonumber Procedure, text CPT Code, integer Combo box:in form [case log form] Name: procedure Control Source: procedure Bound Column:1 Column Count:3 Row Source: {i originally built this using the wizard but I may have changed it around} SELECT Procedure.ID, Procedure.Procedure, Procedure.[CPT code] FROM [Procedure] ORDER BY Procedure.Procedure; Column Widths: 0";1";0.5625" Other text box that you might want to display something: Name: CPT Code Control Source:right now, blank what do you want to display here? I want it to show the unique CPT code from column 3 of the table "procedure" that is associated with a procedure in column 2 of that table. The user chooses the procedure from the pull down list and the CPT fills in - just like in that Northwind example. Thanks, Tom -- Duane Hookom MS Access MVP -- "DrTominRI" wrote in message ... OK Duane. I found the example and that is EXACTLY what I want it to do. you have warn me down and I wont try to store the secondary value, but I might come back to you when I am building my queries. BUT I am still not getting it to work. I think I don't know enough about the "Me" thing. Is the syntax wrong. In the Northwind example it is Me! not Me. does that matter? I tried to search help for Me but I got nothing. Please from scratch, as Danzel Washington said in the movie Philadelphia " like I am a three year old, take me through this one thing and I will leave you alone Thanks for hanging with me Tom "Duane Hookom" wrote: It normally gets installed in a samples directory depending on your version of Office. On my PC it is: C:\Program Files\Microsoft Office\Office10\Samples\Northwind.mdb -- Duane Hookom MS Access MVP -- "DrTominRI" wrote in message ... I will try again where do I find the Northwind example please? "Duane Hookom" wrote: I would rather you try: You can use a combo box with a properties like: Name: cboProcedure Row Source: SELECT [Procedure], [CPT Code] FROM [tblProcCodes] ORDER BY [Procedure]; Then add a text box to your form with the Name: txtCPTCode Control Source: [CPT Code] If you really have a need to store the CPT Code in your operative log table, you will need code in the After Update event of cboProcedure like Me.txtCPTCode = Me.cboProcedure.Column(1) -- Duane Hookom MS Access MVP -- "DrTominRI" wrote in message ... Duane, where/how do I find that example? and I appreciate that it is nothing really short of stupid to store both "red" and "apple" when red=apple, but trust me, if I need to search for the procedure and I don't know the code, or the code and I don't know the procedure I need them both. I am trying to make this so people who are less acess literate than can use it. May I mail you a copy of my test DB to ? Thanks for hanging with me on this Duane Tom "Duane Hookom" wrote: You might want to check the example in the Northwind database. Check the Orders Subform and the ProductID after update. I don't like the use of DLookup() but it should work. I still think you would be better of NOT storing the duplicate values. -- Duane Hookom MS Access MVP -- "DrTominRI" wrote in message ... well duanne it is getting closer, but wierder I hadn't seen the Vbasic box before so that explains why I didn't recognize some of what you were saying. I wrote in the subroutine exactly as you wrote me in a little test database and I got tow things now. I get an "enter paramter value" dialogue box with the name of my procedure_table.ID. and then when I get to the form and check the procedure I want it enters that name in the other box, not the code number. nothing shows up in the box that I chose the procedure from. Tom "Duane Hookom" wrote: You can filter a query with the two tables rather than the one table by itself. If you still want to store the value then create a combo box to selet the Procedure as I noted previously. Add a text box to your form that has a control source of your CPT code field. Name this text box txtCPTCode. Then find the after update event property of your combo box "cboProcedure" and set the property to [Event Procedure] Then click on the builder button on the right [...] and change your code to look like: Private Sub cboProcedure_AfterUpdate() Me.txtCPTCode = Me.cboProcedure.Column(1) End Sub -- Duane Hookom MS Access MVP -- "DrTominRI" wrote in message ... I really think i want to store the data returned from procedure and CPT in the main table so that when I ultimately search my main table I |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Need to Improve Code Copying/Pasting Between Workbooks | David | General Discussion | 1 | January 6th, 2006 04:56 AM |
Add New Field to DB | Karen | Database Design | 7 | October 19th, 2005 08:03 PM |
Help again from Ken Snell (Query) | Randy | Running & Setting Up Queries | 22 | August 29th, 2005 08:15 PM |
Formula to Sum values in word table | Craig | Mailmerge | 1 | August 29th, 2005 06:19 PM |
Complicated Databse w/many relationships | Søren | Database Design | 7 | July 13th, 2004 05:41 AM |