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 |
|
#1
|
|||
|
|||
returning two linked values from a table
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. Specifically, if I have a list of text (diagnoses) and associated with each diagnosis is a single numerical code (ICD-9), two fields in the table "diagnoses". On my form I can get it to return the diagnosis name to the field on my form. But I want it to automatically fill in another box on the form with the ICD-9 code number associated with that diagnosis. |
#2
|
|||
|
|||
returning two linked values from a table
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. Specifically, if I have a list of text (diagnoses) and associated with each diagnosis is a single numerical code (ICD-9), two fields in the table "diagnoses". On my form I can get it to return the diagnosis name to the field on my form. But I want it to automatically fill in another box on the form with the ICD-9 code number associated with that diagnosis. |
#3
|
|||
|
|||
returning two linked values from a table
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. Specifically, if I have a list of text (diagnoses) and associated with each diagnosis is a single numerical code (ICD-9), two fields in the table "diagnoses". On my form I can get it to return the diagnosis name to the field on my form. But I want it to automatically fill in another box on the form with the ICD-9 code number associated with that diagnosis. |
#4
|
|||
|
|||
returning two linked values from a table
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. Specifically, if I have a list of text (diagnoses) and associated with each diagnosis is a single numerical code (ICD-9), two fields in the table "diagnoses". On my form I can get it to return the diagnosis name to the field on my form. But I want it to automatically fill in another box on the form with the ICD-9 code number associated with that diagnosis. |
#5
|
|||
|
|||
returning two linked values from a table
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. Specifically, if I have a list of text (diagnoses) and associated with each diagnosis is a single numerical code (ICD-9), two fields in the table "diagnoses". On my form I can get it to return the diagnosis name to the field on my form. But I want it to automatically fill in another box on the form with the ICD-9 code number associated with that diagnosis. |
#6
|
|||
|
|||
returning two linked values from a table
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. Specifically, if I have a list of text (diagnoses) and associated with each diagnosis is a single numerical code (ICD-9), two fields in the table "diagnoses". On my form I can get it to return the diagnosis name to the field on my form. But I want it to automatically fill in another box on the form with the ICD-9 code number associated with that diagnosis. |
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 03: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 |