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
|
|||
|
|||
I missed the part where you said that you select the ChartNumber from a combo
box, if so add to the rowsource of the combo another field, the [name] field, that way you wont need to access the table again. On the after update event you can write the code Me.[Name] = Me.[ChartNumber].column(1) the column numbers of the combo start from 0, by the position of each column in the recordset, assuming that the name is the second column. Or you can write in the control source of the name field =[ChartNumber].column(1) "shep" wrote: That wants to work, but I am not entering something correctly. Here is code I entered: Private Sub ChartNumber_AfterUpdate() Me.[Name] = DLookup("[Name]", "[tblAppointmentsV1]", " ChartNumber=" & Me.ChartNumber & "") Me.[Name].Requery End Sub When I select a ChartNumber from combo box, I get this msg: Invalid qualifier Qualifiers are used for disambiguation. This error has the following cause and solution: • The qualifier does not identify a project, module, object, or a variable of user-defined type within the current scope. Check the spelling of the qualifier. Make sure that the qualifying identifier is within the current scope. For example, a variable of user-defined type in a Private module is visible only within that module. The dot after Me and [Name] before .Requery are highlighted I did read Gunny's response and will try what he recommended. I would like to get your instruction working first. Obviously I am not a programmer, just an old retired guy trying to help the Epilepsy Foundation in San Antonio - pro bono. So your effort to help me is for a good cause. Thanks "Ofer" wrote: When you look at the poperties box, in the after update event, dont put the code in this line, on the right side there is a button, with three dots, click on it, and select code. In the code section insert the code. Now I'm not sure if you got your answer from Gunny already. "shep" wrote: Thanks again, I think I am getting close. I entered the code you provided on the After Update event of ChartNumber. I deleted the row source in the Name properties. When entered new record, Chartnumber enters but I get msg, " Can't find Macro Me. Doesn't exist or has not been saved. I did save changes to form. How should I save the Macro? Thanks for your help and your patience! "Ofer" wrote: On the after update event of the ChartNumber field write the code Me.[Name] = Dlookup("[Name]", "[tblAppointmentsV1]","ChartNumber="&Me.ChartNumber &"") Or on the ControlSource Property of the [Name] field write =Dlookup("[Name]", "[tblAppointmentsV1]","ChartNumber="& [ChartNumber] &"") On the after update of the ChartNumber field write the code (in the code section) Me.[Name].Requery "shep" wrote: Thanks for your response. Here is what I put in Row Source for Name: Me.[Name]=Dlookup("[Name]","[tblAppointmentsV1]","ChartNumber="&Me.ChartNumber &"") I get msg that source does not exist. I probably have not entered correctly. Can you please advise me further? Thanks "Ofer" wrote: Use Dlookup ' If ChartNumber is Number Me.[Patient name] = Dlookup("[Patient name]","[TableName]","ChartNumber = " & Me.ChartNumber) ' If ChartNumber is String Me.[Patient name] = Dlookup("[Patient name]","[TableName]","ChartNumber = '" & Me.ChartNumber & "'") "shep" wrote: I am running WIN XP Pro and Office 2003 Pro I have I have a table with patients info. I created a query to create Name such as Joe J. Doe from the table's LastName, FirstName, MI and included ChartNumber from the table in the query. ChartNumber is unique to each patient. I have a table to management appointments. It includes ChartNumber and Name and they are lookups from the query. I created a form to enter appointment data into the appointment table. Problem: On the form, I have to pick ChartNumber and then pick Name from the lookup list. Need: How can I get the Patient name to fill in when I select ChartNumber? Thanks |
#12
|
|||
|
|||
Thanks again for all your help. Both of you have given me good advice. I
think I need to redo a couple of tables in a separate dBase to work with and successfully implement what y'all have taught me. Then I can implement it in the Patient Care dBase. I'll advise in a day or two on my progress. Thanks to all of you who give of your time and talent to help others - you will be rewarded. "'69 Camaro" wrote: You're welcome. I didn't mention it explicitly, but the tutorial shows how to create the query, then create a form based on it, but you don't need to create an entirely new form. You could set your form's RecordSource Property to the name of this query (as long as the autolookup query has the same fields included as your former RecordSource Property). And the reason you are having trouble with the code is because you designed your table using a Reserved word, Name. If you don't enjoy banging your head against the wall while screaming, "Why won't this work?!!" then _always_ follow these rules: 1.) Never use Reserved words to name anything (tables, fields, variables, procedures, et cetera). For lists of the Reserved words to avoid, please see the following Web pages: http://support.microsoft.com/default.aspx?id=321266 http://support.microsoft.com/default.aspx?scid=286335 When reading this second page, pay particular attention to the myth on that page because you now know why it's not true: "For existing objects with names that contain reserved words, you can avoid errors by surrounding the object name with brackets ([ ])." 2.) Always rename the controls created with the Form Wizard so that the control's name is different than the field name. For example, LastName becomes txtLastName for the Text Box control bound to the LastName field. 3.) Never use spaces or other non-alphanumeric characters to name things. The one exception is the underscore. If you want things to look aesthetically pleasing, then set the field's Caption Property while the table is in Design View to display whatever you would like, including spaces and special characters. When you create new controls, the Caption Property is used for the attached label. Headers on tables and queries will also show the Caption Property instead of the field name, but only if the Caption Property has been set. 4.) Always refer to controls on the form in VBA code with Me! and not Me. because the dot operator signifies a property of the form, not necessarily the control by the same name. HTH. Gunny See http://www.QBuilt.com for all your database needs. See http://www.Access.QBuilt.com for Microsoft Access tips. (Please remove ZERO_SPAM from my reply E-mail address so that a message will be forwarded to me.) "shep" wrote in message ... Thanks for your help again. I will benefit from your assistance. "'69 Camaro" wrote: Hi, shep. Need: How can I get the Patient name to fill in when I select ChartNumber? If you have primary keys on these tables, then I would recommend an autolookup query to automatically fill in any and all information about the patient (like the phone number, too) on this appointment form which doesn't need any coding. There's a step-by-step tutorial on how to automatically fill in the information on a form on this Web page: http://www.Access.QBuilt.com/html/fo...toCompleteForm Substitute your appointments table for the tblInvoices table in the example and substitute your patients table for the tblContractors table in the example. (I would use the primary key of the patient table instead of the ChartNumber -- if that isn't your primary key -- to be on the safe side, but since it's unique you may get away with using ChartNumber.) When creating the query, make sure that the ChartNumber (or patient's table primary key) is added to the query design grid from your appointments table, not your patients table. When complete, all you have to do when creating a new appointment is to select the patient's name (or the ChartNumber, depending upon which value(s) you want to show) in the combo box and the patient's information will automatically populate all of the patient fields on the form. No code necessary. HTH. Gunny See http://www.QBuilt.com for all your database needs. See http://www.Access.QBuilt.com for Microsoft Access tips. (Please remove ZERO_SPAM from my reply E-mail address so that a message will be forwarded to me.) - - - If my answer has helped you, please sign in and answer yes to the question "Did this post answer your question?" at the bottom of the message, which adds your question and the answers to the database of answers. Remember that questions answered the quickest are often from those who have a history of rewarding the contributors who have taken the time to answer questions correctly. "shep" wrote: I am running WIN XP Pro and Office 2003 Pro I have I have a table with patients info. I created a query to create Name such as Joe J. Doe from the table's LastName, FirstName, MI and included ChartNumber from the table in the query. ChartNumber is unique to each patient. I have a table to management appointments. It includes ChartNumber and Name and they are lookups from the query. I created a form to enter appointment data into the appointment table. Problem: On the form, I have to pick ChartNumber and then pick Name from the lookup list. Need: How can I get the Patient name to fill in when I select ChartNumber? Thanks |
#13
|
|||
|
|||
That did it!! I want to thank you again for giving your time and talent to
help others. And I appreciate your patience. You have made me a happy man today. "Ofer" wrote: I missed the part where you said that you select the ChartNumber from a combo box, if so add to the rowsource of the combo another field, the [name] field, that way you wont need to access the table again. On the after update event you can write the code Me.[Name] = Me.[ChartNumber].column(1) the column numbers of the combo start from 0, by the position of each column in the recordset, assuming that the name is the second column. Or you can write in the control source of the name field =[ChartNumber].column(1) "shep" wrote: That wants to work, but I am not entering something correctly. Here is code I entered: Private Sub ChartNumber_AfterUpdate() Me.[Name] = DLookup("[Name]", "[tblAppointmentsV1]", " ChartNumber=" & Me.ChartNumber & "") Me.[Name].Requery End Sub When I select a ChartNumber from combo box, I get this msg: Invalid qualifier Qualifiers are used for disambiguation. This error has the following cause and solution: • The qualifier does not identify a project, module, object, or a variable of user-defined type within the current scope. Check the spelling of the qualifier. Make sure that the qualifying identifier is within the current scope. For example, a variable of user-defined type in a Private module is visible only within that module. The dot after Me and [Name] before .Requery are highlighted I did read Gunny's response and will try what he recommended. I would like to get your instruction working first. Obviously I am not a programmer, just an old retired guy trying to help the Epilepsy Foundation in San Antonio - pro bono. So your effort to help me is for a good cause. Thanks "Ofer" wrote: When you look at the poperties box, in the after update event, dont put the code in this line, on the right side there is a button, with three dots, click on it, and select code. In the code section insert the code. Now I'm not sure if you got your answer from Gunny already. "shep" wrote: Thanks again, I think I am getting close. I entered the code you provided on the After Update event of ChartNumber. I deleted the row source in the Name properties. When entered new record, Chartnumber enters but I get msg, " Can't find Macro Me. Doesn't exist or has not been saved. I did save changes to form. How should I save the Macro? Thanks for your help and your patience! "Ofer" wrote: On the after update event of the ChartNumber field write the code Me.[Name] = Dlookup("[Name]", "[tblAppointmentsV1]","ChartNumber="&Me.ChartNumber &"") Or on the ControlSource Property of the [Name] field write =Dlookup("[Name]", "[tblAppointmentsV1]","ChartNumber="& [ChartNumber] &"") On the after update of the ChartNumber field write the code (in the code section) Me.[Name].Requery "shep" wrote: Thanks for your response. Here is what I put in Row Source for Name: Me.[Name]=Dlookup("[Name]","[tblAppointmentsV1]","ChartNumber="&Me.ChartNumber &"") I get msg that source does not exist. I probably have not entered correctly. Can you please advise me further? Thanks "Ofer" wrote: Use Dlookup ' If ChartNumber is Number Me.[Patient name] = Dlookup("[Patient name]","[TableName]","ChartNumber = " & Me.ChartNumber) ' If ChartNumber is String Me.[Patient name] = Dlookup("[Patient name]","[TableName]","ChartNumber = '" & Me.ChartNumber & "'") "shep" wrote: I am running WIN XP Pro and Office 2003 Pro I have I have a table with patients info. I created a query to create Name such as Joe J. Doe from the table's LastName, FirstName, MI and included ChartNumber from the table in the query. ChartNumber is unique to each patient. I have a table to management appointments. It includes ChartNumber and Name and they are lookups from the query. I created a form to enter appointment data into the appointment table. Problem: On the form, I have to pick ChartNumber and then pick Name from the lookup list. Need: How can I get the Patient name to fill in when I select ChartNumber? Thanks |
#14
|
|||
|
|||
It works!! Thanks again for your help!
"'69 Camaro" wrote: You're welcome. I didn't mention it explicitly, but the tutorial shows how to create the query, then create a form based on it, but you don't need to create an entirely new form. You could set your form's RecordSource Property to the name of this query (as long as the autolookup query has the same fields included as your former RecordSource Property). And the reason you are having trouble with the code is because you designed your table using a Reserved word, Name. If you don't enjoy banging your head against the wall while screaming, "Why won't this work?!!" then _always_ follow these rules: 1.) Never use Reserved words to name anything (tables, fields, variables, procedures, et cetera). For lists of the Reserved words to avoid, please see the following Web pages: http://support.microsoft.com/default.aspx?id=321266 http://support.microsoft.com/default.aspx?scid=286335 When reading this second page, pay particular attention to the myth on that page because you now know why it's not true: "For existing objects with names that contain reserved words, you can avoid errors by surrounding the object name with brackets ([ ])." 2.) Always rename the controls created with the Form Wizard so that the control's name is different than the field name. For example, LastName becomes txtLastName for the Text Box control bound to the LastName field. 3.) Never use spaces or other non-alphanumeric characters to name things. The one exception is the underscore. If you want things to look aesthetically pleasing, then set the field's Caption Property while the table is in Design View to display whatever you would like, including spaces and special characters. When you create new controls, the Caption Property is used for the attached label. Headers on tables and queries will also show the Caption Property instead of the field name, but only if the Caption Property has been set. 4.) Always refer to controls on the form in VBA code with Me! and not Me. because the dot operator signifies a property of the form, not necessarily the control by the same name. HTH. Gunny See http://www.QBuilt.com for all your database needs. See http://www.Access.QBuilt.com for Microsoft Access tips. (Please remove ZERO_SPAM from my reply E-mail address so that a message will be forwarded to me.) "shep" wrote in message ... Thanks for your help again. I will benefit from your assistance. "'69 Camaro" wrote: Hi, shep. Need: How can I get the Patient name to fill in when I select ChartNumber? If you have primary keys on these tables, then I would recommend an autolookup query to automatically fill in any and all information about the patient (like the phone number, too) on this appointment form which doesn't need any coding. There's a step-by-step tutorial on how to automatically fill in the information on a form on this Web page: http://www.Access.QBuilt.com/html/fo...toCompleteForm Substitute your appointments table for the tblInvoices table in the example and substitute your patients table for the tblContractors table in the example. (I would use the primary key of the patient table instead of the ChartNumber -- if that isn't your primary key -- to be on the safe side, but since it's unique you may get away with using ChartNumber.) When creating the query, make sure that the ChartNumber (or patient's table primary key) is added to the query design grid from your appointments table, not your patients table. When complete, all you have to do when creating a new appointment is to select the patient's name (or the ChartNumber, depending upon which value(s) you want to show) in the combo box and the patient's information will automatically populate all of the patient fields on the form. No code necessary. HTH. Gunny See http://www.QBuilt.com for all your database needs. See http://www.Access.QBuilt.com for Microsoft Access tips. (Please remove ZERO_SPAM from my reply E-mail address so that a message will be forwarded to me.) - - - If my answer has helped you, please sign in and answer yes to the question "Did this post answer your question?" at the bottom of the message, which adds your question and the answers to the database of answers. Remember that questions answered the quickest are often from those who have a history of rewarding the contributors who have taken the time to answer questions correctly. "shep" wrote: I am running WIN XP Pro and Office 2003 Pro I have I have a table with patients info. I created a query to create Name such as Joe J. Doe from the table's LastName, FirstName, MI and included ChartNumber from the table in the query. ChartNumber is unique to each patient. I have a table to management appointments. It includes ChartNumber and Name and they are lookups from the query. I created a form to enter appointment data into the appointment table. Problem: On the form, I have to pick ChartNumber and then pick Name from the lookup list. Need: How can I get the Patient name to fill in when I select ChartNumber? Thanks |
#15
|
|||
|
|||
You're welcome! Glad it's working!
Gunny See http://www.QBuilt.com for all your database needs. See http://www.Access.QBuilt.com for Microsoft Access tips. "shep" wrote in message ... It works!! Thanks again for your help! "'69 Camaro" wrote: You're welcome. I didn't mention it explicitly, but the tutorial shows how to create the query, then create a form based on it, but you don't need to create an entirely new form. You could set your form's RecordSource Property to the name of this query (as long as the autolookup query has the same fields included as your former RecordSource Property). And the reason you are having trouble with the code is because you designed your table using a Reserved word, Name. If you don't enjoy banging your head against the wall while screaming, "Why won't this work?!!" then _always_ follow these rules: 1.) Never use Reserved words to name anything (tables, fields, variables, procedures, et cetera). For lists of the Reserved words to avoid, please see the following Web pages: http://support.microsoft.com/default.aspx?id=321266 http://support.microsoft.com/default.aspx?scid=286335 When reading this second page, pay particular attention to the myth on that page because you now know why it's not true: "For existing objects with names that contain reserved words, you can avoid errors by surrounding the object name with brackets ([ ])." 2.) Always rename the controls created with the Form Wizard so that the control's name is different than the field name. For example, LastName becomes txtLastName for the Text Box control bound to the LastName field. 3.) Never use spaces or other non-alphanumeric characters to name things. The one exception is the underscore. If you want things to look aesthetically pleasing, then set the field's Caption Property while the table is in Design View to display whatever you would like, including spaces and special characters. When you create new controls, the Caption Property is used for the attached label. Headers on tables and queries will also show the Caption Property instead of the field name, but only if the Caption Property has been set. 4.) Always refer to controls on the form in VBA code with Me! and not Me. because the dot operator signifies a property of the form, not necessarily the control by the same name. HTH. Gunny See http://www.QBuilt.com for all your database needs. See http://www.Access.QBuilt.com for Microsoft Access tips. (Please remove ZERO_SPAM from my reply E-mail address so that a message will be forwarded to me.) "shep" wrote in message ... Thanks for your help again. I will benefit from your assistance. "'69 Camaro" wrote: Hi, shep. Need: How can I get the Patient name to fill in when I select ChartNumber? If you have primary keys on these tables, then I would recommend an autolookup query to automatically fill in any and all information about the patient (like the phone number, too) on this appointment form which doesn't need any coding. There's a step-by-step tutorial on how to automatically fill in the information on a form on this Web page: http://www.Access.QBuilt.com/html/fo...toCompleteForm Substitute your appointments table for the tblInvoices table in the example and substitute your patients table for the tblContractors table in the example. (I would use the primary key of the patient table instead of the ChartNumber -- if that isn't your primary key -- to be on the safe side, but since it's unique you may get away with using ChartNumber.) When creating the query, make sure that the ChartNumber (or patient's table primary key) is added to the query design grid from your appointments table, not your patients table. When complete, all you have to do when creating a new appointment is to select the patient's name (or the ChartNumber, depending upon which value(s) you want to show) in the combo box and the patient's information will automatically populate all of the patient fields on the form. No code necessary. HTH. Gunny See http://www.QBuilt.com for all your database needs. See http://www.Access.QBuilt.com for Microsoft Access tips. (Please remove ZERO_SPAM from my reply E-mail address so that a message will be forwarded to me.) - - - If my answer has helped you, please sign in and answer yes to the question "Did this post answer your question?" at the bottom of the message, which adds your question and the answers to the database of answers. Remember that questions answered the quickest are often from those who have a history of rewarding the contributors who have taken the time to answer questions correctly. "shep" wrote: I am running WIN XP Pro and Office 2003 Pro I have I have a table with patients info. I created a query to create Name such as Joe J. Doe from the table's LastName, FirstName, MI and included ChartNumber from the table in the query. ChartNumber is unique to each patient. I have a table to management appointments. It includes ChartNumber and Name and they are lookups from the query. I created a form to enter appointment data into the appointment table. Problem: On the form, I have to pick ChartNumber and then pick Name from the lookup list. Need: How can I get the Patient name to fill in when I select ChartNumber? Thanks |
#16
|
|||
|
|||
Thank you :-)
"shep" wrote: That did it!! I want to thank you again for giving your time and talent to help others. And I appreciate your patience. You have made me a happy man today. "Ofer" wrote: I missed the part where you said that you select the ChartNumber from a combo box, if so add to the rowsource of the combo another field, the [name] field, that way you wont need to access the table again. On the after update event you can write the code Me.[Name] = Me.[ChartNumber].column(1) the column numbers of the combo start from 0, by the position of each column in the recordset, assuming that the name is the second column. Or you can write in the control source of the name field =[ChartNumber].column(1) "shep" wrote: That wants to work, but I am not entering something correctly. Here is code I entered: Private Sub ChartNumber_AfterUpdate() Me.[Name] = DLookup("[Name]", "[tblAppointmentsV1]", " ChartNumber=" & Me.ChartNumber & "") Me.[Name].Requery End Sub When I select a ChartNumber from combo box, I get this msg: Invalid qualifier Qualifiers are used for disambiguation. This error has the following cause and solution: • The qualifier does not identify a project, module, object, or a variable of user-defined type within the current scope. Check the spelling of the qualifier. Make sure that the qualifying identifier is within the current scope. For example, a variable of user-defined type in a Private module is visible only within that module. The dot after Me and [Name] before .Requery are highlighted I did read Gunny's response and will try what he recommended. I would like to get your instruction working first. Obviously I am not a programmer, just an old retired guy trying to help the Epilepsy Foundation in San Antonio - pro bono. So your effort to help me is for a good cause. Thanks "Ofer" wrote: When you look at the poperties box, in the after update event, dont put the code in this line, on the right side there is a button, with three dots, click on it, and select code. In the code section insert the code. Now I'm not sure if you got your answer from Gunny already. "shep" wrote: Thanks again, I think I am getting close. I entered the code you provided on the After Update event of ChartNumber. I deleted the row source in the Name properties. When entered new record, Chartnumber enters but I get msg, " Can't find Macro Me. Doesn't exist or has not been saved. I did save changes to form. How should I save the Macro? Thanks for your help and your patience! "Ofer" wrote: On the after update event of the ChartNumber field write the code Me.[Name] = Dlookup("[Name]", "[tblAppointmentsV1]","ChartNumber="&Me.ChartNumber &"") Or on the ControlSource Property of the [Name] field write =Dlookup("[Name]", "[tblAppointmentsV1]","ChartNumber="& [ChartNumber] &"") On the after update of the ChartNumber field write the code (in the code section) Me.[Name].Requery "shep" wrote: Thanks for your response. Here is what I put in Row Source for Name: Me.[Name]=Dlookup("[Name]","[tblAppointmentsV1]","ChartNumber="&Me.ChartNumber &"") I get msg that source does not exist. I probably have not entered correctly. Can you please advise me further? Thanks "Ofer" wrote: Use Dlookup ' If ChartNumber is Number Me.[Patient name] = Dlookup("[Patient name]","[TableName]","ChartNumber = " & Me.ChartNumber) ' If ChartNumber is String Me.[Patient name] = Dlookup("[Patient name]","[TableName]","ChartNumber = '" & Me.ChartNumber & "'") "shep" wrote: I am running WIN XP Pro and Office 2003 Pro I have I have a table with patients info. I created a query to create Name such as Joe J. Doe from the table's LastName, FirstName, MI and included ChartNumber from the table in the query. ChartNumber is unique to each patient. I have a table to management appointments. It includes ChartNumber and Name and they are lookups from the query. I created a form to enter appointment data into the appointment table. Problem: On the form, I have to pick ChartNumber and then pick Name from the lookup list. Need: How can I get the Patient name to fill in when I select ChartNumber? Thanks |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Lookup fields - relationships | TS in FL | Database Design | 2 | June 7th, 2005 02:08 PM |
lookup vs combo box | Scubaman | General Discussion | 4 | June 7th, 2005 02:02 AM |
Concatenate two fields and then make a lookup box | Shannon | New Users | 2 | February 25th, 2005 09:07 PM |
In a lookup column, looking up multiple fields, can I change whic. | rthunder78 | New Users | 1 | February 22nd, 2005 07:35 PM |
What wrong with lookup fields? | AEA | Running & Setting Up Queries | 2 | October 8th, 2004 09:58 PM |