Data Transfer

[hjknipp1024]

I have a form that is used to enter test results for a lab. We track the status of each test and when the test is completed, the analyst selects the "Released" status from the list. I want the entire row of information (not just a field) for that test to then be transferred to a separate table. I think I need to use an Append query to specify moving from my "Preships" table into my "Released" table. I think I can then add a macro or query to my form that they can click to "Archive Released Lots." Can someone walk me through the best way to do this?

 

[pbaldy]

The best way is probably not to do it.

Generally speaking, you shouldn't move data around between tables. You'd leave records in a single table, with a status field of some sort. That lets you query any combination of "live" or "archived" records simply by adjusting query criteria.

If you feel you must, you'd run an append query and then a delete query, both using the appropriate criteria to grab the selected record.

 

[hjknipp1024]

I like the idea of not moving them. It makes me feel better about the data not being lost in transfer. So you're saying I can archive the "Released" records somehow so that they won't appear on the form for the users to see? How can I hide them from the form once they have selected the "Released" status?

 

[pbaldy]

Several ways; most common would probably be to base the form on a query that had a criteria excluding them. A requery of the form would drop a record once that status changed while the form is open. You can also use this technique:

http://www.baldyweb.com/wherecondition.htm

with a hard-coded wherecondition.

 

[hjknipp1024]

I have a table with each status option listed by ID:
The number is from the "ID" column and the status is from the "Status" column.
1=Uninspected
2=In Test
3=Rejected
6=On Hold
7=Released

I want to create a script to only display a status with an ID of <7 so that my form will not display "Released" lots. I can insert the script onto my form as a filter so that they can refresh it to remove the released lots. How do I create this script so that it doesn't just query the information? I don't know how to create a script that works as a display filter.

 

[pbaldy]

If you don't want them to able to remove the filter via some method, more reliable would be to base the form on a query with that criteria. That the records with 7 would be completely inaccessible to the form.

 

[hjknipp1024]

I am so appreciative of your help, but you are being too high level. I am a newbie so I need you to spell it out for me. How do you base a form on a query? Do I create a macro for this? I am looking at the Filter/Query/Search macro. Would Apply Filter do this?

 

[pbaldy]

No; create a query that selects all necessary fields from the table. On the status field, put a criteria of:

<7

Save the query. In the form's properties, change the record source from the table to the query.

 

[hjknipp1024]

Thank you so much!