Form Help

[Glen]

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 Whipp]

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

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?

 

[Glen]

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


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

 

[Gina Whipp]

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,

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


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