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
|
|||
|
|||
Form Help
I think I have a simple problem but I am having difficulty solving the
issue so maybe someone here can help. I have two primary tables. The first is an IPR table which idetifies problems that we experience during integration. It has several fields such as the IPR Table ID named "ID" (autonumber index) and the issue description (memo field). The second table is a TIR table with a TIR ID (autonumber index) and several other fields including a field to capture the relationship to IPR through a number field named "IPR ID". I have created two forms, an IPR form for entering the IPR data and a TIR form for entering TIR data. Much of the data on the TIR form will use the data from the IPR table. Here's what I am trying to do: Use the TIR form to capture resolutions. I want the users to enter the TIR form and type the IPR ID into the Form. I want the Form to populate with the data that matches that IPR ID by pulling from the appropriate record on the IPR table. For example: If a user enters the TIR form with TIR ID#3 and enters IPR ID #25 into the form, I want the TIR Form to pull the issue description, entry date, etc. from the IPR record that corresponds to the IPR ID. As the user updates through the TIR Form I want it to update the IPR table and TIR table without creating a new record in the IPR Table. Any suggestions? |
#2
|
|||
|
|||
Form Help
Glen,
You would use a DLookup and a query for this. Create a query that has all the fields you want in it including IPRID. Then use the DLookup on your form to pull the values. If numeric... DLookup("Field1","IPRTable","IPRID=" & Me.IPRID If Text... DLookup("Field1","IPRTable","IPRID='" & Me.IPRID & "'" OR The RecordSource of your form, create a query that joins those two tables together and when you enter the IPRID you will be able to see the corresponding data from the other table... I'd do it this way. *Because* you really don't want to store the same data in two tables. You would be *breaking* a normalized database. You can pull that information any time you want via a query. -- Gina Whipp 2010 Microsoft MVP (Access) "I feel I have been denied critical, need to know, information!" - Tremors II http://www.regina-whipp.com/index_files/TipList.htm "Glen" wrote in message ... I think I have a simple problem but I am having difficulty solving the issue so maybe someone here can help. I have two primary tables. The first is an IPR table which idetifies problems that we experience during integration. It has several fields such as the IPR Table ID named "ID" (autonumber index) and the issue description (memo field). The second table is a TIR table with a TIR ID (autonumber index) and several other fields including a field to capture the relationship to IPR through a number field named "IPR ID". I have created two forms, an IPR form for entering the IPR data and a TIR form for entering TIR data. Much of the data on the TIR form will use the data from the IPR table. Here's what I am trying to do: Use the TIR form to capture resolutions. I want the users to enter the TIR form and type the IPR ID into the Form. I want the Form to populate with the data that matches that IPR ID by pulling from the appropriate record on the IPR table. For example: If a user enters the TIR form with TIR ID#3 and enters IPR ID #25 into the form, I want the TIR Form to pull the issue description, entry date, etc. from the IPR record that corresponds to the IPR ID. As the user updates through the TIR Form I want it to update the IPR table and TIR table without creating a new record in the IPR Table. Any suggestions? |
#3
|
|||
|
|||
Form Help
On Mar 19, 10:07*pm, "Gina Whipp" wrote:
Glen, You would use a DLookup and a query for this. *Create a query that has all the fields you want in it including IPRID. *Then use the DLookup on your form to pull the values. If numeric... DLookup("Field1","IPRTable","IPRID=" & Me.IPRID If Text... DLookup("Field1","IPRTable","IPRID='" & Me.IPRID & "'" OR The RecordSource of your form, create a query that joins those two tables together and when you enter the IPRID you will be able to see the corresponding data from the other table... *I'd do it this way. *Because* you really don't want to store the same data in two tables. *You would be *breaking* a normalized database. *You can pull that information any time you want via a query. -- Gina Whipp 2010 Microsoft MVP (Access) "I feel I have been denied critical, need to know, information!" - Tremors II http://www.regina-whipp.com/index_files/TipList.htm "Glen" wrote in message ... I think I have a simple problem but I am having difficulty solving the issue so maybe someone here can help. I have two primary tables. *The first is an IPR table which idetifies problems that we experience during integration. *It has several fields such as the IPR Table ID named "ID" (autonumber index) and the issue description (memo field). *The second table is a TIR table with a TIR ID (autonumber index) and several other fields including a field to capture the relationship to IPR through a number field named "IPR ID". *I have created two forms, an IPR form for entering the IPR data and a TIR form for entering TIR data. *Much of the data on the TIR form will use the data from the IPR table. Here's what I am trying to do: Use the TIR form to capture resolutions. *I want the users to enter the TIR form and type the IPR ID into the Form. *I want the Form to populate with the data that matches that IPR ID by pulling from the appropriate record on the IPR table. *For example: *If a user enters the TIR form with TIR ID#3 and enters IPR ID #25 into the form, I want the TIR Form to pull the issue description, entry date, etc. from the IPR record that corresponds to the IPR ID. *As the user updates through the TIR Form I want it to update the IPR table and TIR table without creating a new record in the IPR Table. Any suggestions? Gina, Thanks very much. I knew it was an easy fix. I just didn't have the query set up properly. I included the IPR Table in the query and set the ID's equal in the query with a simple build function. It appears to work perfectly. I am going to do some testing on it to make sure but so far it is working exactly as I wanted it to. Thanks again for your help. Glen |
#4
|
|||
|
|||
Form Help
You're welcome!
-- Gina Whipp 2010 Microsoft MVP (Access) "I feel I have been denied critical, need to know, information!" - Tremors II http://www.regina-whipp.com/index_files/TipList.htm "Glen" wrote in message ... On Mar 19, 10:07 pm, "Gina Whipp" wrote: Glen, You would use a DLookup and a query for this. Create a query that has all the fields you want in it including IPRID. Then use the DLookup on your form to pull the values. If numeric... DLookup("Field1","IPRTable","IPRID=" & Me.IPRID If Text... DLookup("Field1","IPRTable","IPRID='" & Me.IPRID & "'" OR The RecordSource of your form, create a query that joins those two tables together and when you enter the IPRID you will be able to see the corresponding data from the other table... I'd do it this way. *Because* you really don't want to store the same data in two tables. You would be *breaking* a normalized database. You can pull that information any time you want via a query. -- Gina Whipp 2010 Microsoft MVP (Access) "I feel I have been denied critical, need to know, information!" - Tremors II http://www.regina-whipp.com/index_files/TipList.htm "Glen" wrote in message ... I think I have a simple problem but I am having difficulty solving the issue so maybe someone here can help. I have two primary tables. The first is an IPR table which idetifies problems that we experience during integration. It has several fields such as the IPR Table ID named "ID" (autonumber index) and the issue description (memo field). The second table is a TIR table with a TIR ID (autonumber index) and several other fields including a field to capture the relationship to IPR through a number field named "IPR ID". I have created two forms, an IPR form for entering the IPR data and a TIR form for entering TIR data. Much of the data on the TIR form will use the data from the IPR table. Here's what I am trying to do: Use the TIR form to capture resolutions. I want the users to enter the TIR form and type the IPR ID into the Form. I want the Form to populate with the data that matches that IPR ID by pulling from the appropriate record on the IPR table. For example: If a user enters the TIR form with TIR ID#3 and enters IPR ID #25 into the form, I want the TIR Form to pull the issue description, entry date, etc. from the IPR record that corresponds to the IPR ID. As the user updates through the TIR Form I want it to update the IPR table and TIR table without creating a new record in the IPR Table. Any suggestions? Gina, Thanks very much. I knew it was an easy fix. I just didn't have the query set up properly. I included the IPR Table in the query and set the ID's equal in the query with a simple build function. It appears to work perfectly. I am going to do some testing on it to make sure but so far it is working exactly as I wanted it to. Thanks again for your help. Glen |
Thread Tools | |
Display Modes | |
|
|