Create new records based on a query list?

J

John Milward

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?
 
L

Larry Daugherty

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
 
J

John Milward

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
 
L

Larry Daugherty

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.
--
 
Top