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
|
|||
|
|||
Create new records based on a query list?
I have a database with Equipment, and related test point tables.
When i pick an equipment item there are a number of related test points found. All fine so far. I need to show this list of points along with an additional box where the user types the result of each test point. When complete they need to click a button which will create, in an additional table, a set of records (one for each point) that store this information. Can someone point me in the best direction to solve this please? |
#2
|
|||
|
|||
Create new records based on a query list?
From what you've given I see two tables; tblEquipment and tblTestPoint
which should be related with Referential Integrity on. I suggest adding another table, tblTestPointReading. TPR would be the many side of one-to many with tblTestPoint with Referential Integrity on. tblTestPointReading would have fields for TPRID, TestPooointID, TestPointName, ReadingDate, maybe ReadingTime, TestPointValue. TestPointNote. Your main form for taking readings would be based on a piece of equipment. Once the equipment is selected, its subform (based on tblTestPointReading) would be populated with a new record named for each test point for which a reading will be taken. HTH -- -Larry- -- "John Milward" wrote in message ... I have a database with Equipment, and related test point tables. When i pick an equipment item there are a number of related test points found. All fine so far. I need to show this list of points along with an additional box where the user types the result of each test point. When complete they need to click a button which will create, in an additional table, a set of records (one for each point) that store this information. Can someone point me in the best direction to solve this please? |
#3
|
|||
|
|||
Create new records based on a query list?
Larry
You have understood the required process precisely. The table structures are pretty much as I anticipated and are all up and running. My uncertainty is at the last point where I need to populate a new record for each test point in tblTestPointReading. I can't see how to create and fill in these records. Thanks for the help John "Larry Daugherty" wrote in message ... From what you've given I see two tables; tblEquipment and tblTestPoint which should be related with Referential Integrity on. I suggest adding another table, tblTestPointReading. TPR would be the many side of one-to many with tblTestPoint with Referential Integrity on. tblTestPointReading would have fields for TPRID, TestPooointID, TestPointName, ReadingDate, maybe ReadingTime, TestPointValue. TestPointNote. Your main form for taking readings would be based on a piece of equipment. Once the equipment is selected, its subform (based on tblTestPointReading) would be populated with a new record named for each test point for which a reading will be taken. HTH -- -Larry- -- "John Milward" wrote in message ... I have a database with Equipment, and related test point tables. When i pick an equipment item there are a number of related test points found. All fine so far. I need to show this list of points along with an additional box where the user types the result of each test point. When complete they need to click a button which will create, in an additional table, a set of records (one for each point) that store this information. Can someone point me in the best direction to solve this please? |
#4
|
|||
|
|||
Create new records based on a query list?
Well, there's the rub! Access and other databases are tools for the
entry and massaging of data and not for the creation of empty records. Creating empty records is usually a stupid thing to do. Not in your case. You need to create a new record for every test point reading that should be taken in tblTestPointReading. Note that there is always more than one way to get things done in Access as in most things. If others have better ideas, please jump in. Wow! I see that my mind went on to other things and left the table organization described to you in error: here is an amended version: All of this is air code so your mileage may vary. ================================================== == From what you've given I see two tables; tblEquipment and tblTestPoint which should be related with Referential Integrity on. I suggest adding another table, tblTestPointReading. TPR would be the many side of another one-to many with tblEquipment with Referential Integrity on. tblTestPointReading would have fields for TPRID, EquipmentID, TestPointName, ReadingDate, maybe ReadingTime, TestPointValue. TestPointNote. sorry about the confusion caused by my error. ================================================== ===== tblTestPoint just serves as the template for the creation of the Test Point Reading records. On your "Take Readings Form: the fun begins at the point where you select the piece of equipment. Call that recordset rstE You need to open a recordset based on tblTestPoint where EquipmentID = EquipmentID on this form's current record. You need to open another recordset on tblTestPointReading Call that recordset rstR. For each record in rsteE add a new record to rstR. Set rstR!EquipmentID =rstE!EquipmentID, rstR!TestPointName=rstE!TestPointName, set rstR! Reading Date and Reading Time as you want them. Update and move on to the next. Eventually rstE runs out of records. When that happens, close both recordsets. Your form should now have a form setting there with a subform containing a bunch of Test Point records with names and the current date. You'd probably leave the time blank until they enter the reading. You could have it automatically update when the value changes. The user enters readings and eventually moves on to the next piece of equipment or simply closes the form. HTH -- -Larry- p.s. If that isn't even close, send me what you have & I'll try to get it going. -- "John Milward" wrote in message ... Larry You have understood the required process precisely. The table structures are pretty much as I anticipated and are all up and running. My uncertainty is at the last point where I need to populate a new record for each test point in tblTestPointReading. I can't see how to create and fill in these records. Thanks for the help John "Larry Daugherty" wrote in message ... From what you've given I see two tables; tblEquipment and tblTestPoint which should be related with Referential Integrity on. I suggest adding another table, tblTestPointReading. TPR would be the many side of one-to many with tblTestPoint with Referential Integrity on. tblTestPointReading would have fields for TPRID, TestPooointID, TestPointName, ReadingDate, maybe ReadingTime, TestPointValue. TestPointNote. Your main form for taking readings would be based on a piece of equipment. Once the equipment is selected, its subform (based on tblTestPointReading) would be populated with a new record named for each test point for which a reading will be taken. HTH -- -Larry- -- "John Milward" wrote in message ... I have a database with Equipment, and related test point tables. When i pick an equipment item there are a number of related test points found. All fine so far. I need to show this list of points along with an additional box where the user types the result of each test point. When complete they need to click a button which will create, in an additional table, a set of records (one for each point) that store this information. Can someone point me in the best direction to solve this please? |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Toolbars, Drop-Down Menus | Rick | New Users | 1 | September 21st, 2005 11:17 AM |
Attn Sprinks- Not duplicate insert records | babs | Using Forms | 1 | December 13th, 2004 06:25 PM |
Unmatched Query Mess | Natalia | Running & Setting Up Queries | 8 | October 28th, 2004 02:36 PM |
Taher | Setting Up & Running Reports | 1 | August 31st, 2004 09:07 PM | |
synchronizing form and list box | Deb Smith | Using Forms | 8 | June 21st, 2004 08:15 PM |