calling records and updating

[vicsaccess]

ok, i'm needing direction on the best method so I can start studying and trying more new things, yea. what I have are two tables, the first called circular holds work for the future, the second called workque holds work that is currently being looked at to schedule. what I need to do is that upon opening a form to display the records from the workque table, the onload event pulls up the circular table, decides if the dates in the table are within 8 days of now, if so insert into the workque table. then load the form displaying the current records in the workque table. i'm sure this is done, what is the best method?

[June7]

Why do you need to copy data from circular to workque? Why not just change the value of a field to indicated change in status? Apply filter for viewing desired records.

[vicsaccess]

thanks June, the circular table will be basically the master list of reoccurring work, then each time that work comes up do, weekly, monthly, quarterly or yearly will be a job that will need to be tracked. lets say widget #1 needs to be greased weekly it needs to generate a trackable job with details of each occurrence, widget #1 might also need quarterly cleaning which needs to be tracked. all that is in the circular table is the widget number, its work type code, next scheduled occurance and its reaccuring code (ww, m, q, y). then at some point if you need to pull up the history of widget #1 you will be able to see everything that was done for the last, say, year. my thought was if I can learn how to look at the circular table when loading the form, if the next scheduled date is within a certain time frame then it would create a work ticket in the workque and then change the next scheduled date in the circular table. I know how to do the code to determine the work and make the ticket but I've never actually looked at a different table will loading something. is that where I dim the database and record source?

[June7]

Conventional approach would be a form/subform arrangement for data entry/edit. Main form bound to circular and subform bound to workque. The PK of circular would automatically save as FK in workque when the dependent record is initiated. Also, the next date would not be saved but calculated when needed.

I suppose a procedure could be developed that analyzes the dates and creates the dependent workque records for all circular records that meet the date criteria.

Using code to save calculated data (such as the next scheduled date) and sets of records is tricky. Making sure the saved date does not get 'out of sync' with the raw data and making sure duplicate sets of records are not saved are both concerns to be considered in 'automation' code. However, this is not an uncommon approach and is subject of numerous threads. Various methods to accomplish. Use of recordset object is one.