Keep table accrate using Query 2

[nwprog]

Here is a intresting hicup that someone might be able to help with. As I am still learning, I'm not exactly sure how to approach this problem. I have a table that is based on a Create Table Query titled "Open Jobs". The Query retrieves data from one of my main tables called "Tower Calls" which tracks open Maintenance Calls. When the call is finished a cmd button on the form time stamps the ready column. I have a form built from the resulting query table that I would like to keep accurate. The Query that generates the table works fine, but I would like to update the query table and the resulting form as jobs are closed. As you can see by the code below, the query seraches for jobs where the ready column is Null.

SELECT [Tower Calls].callid, [Tower Calls].date, [Tower Calls].tailnum, [Tower Calls].flt, [Tower Calls].gate, [Tower Calls].deptim INTO [Open Jobs]
FROM [Tower Calls]
WHERE ((([Tower Calls].redy) Is Null))
ORDER BY [Tower Calls].date, [Tower Calls].deptim;

What I am trying to do is to use an Action Query (I think) to check the Tower calls table for any changes to the specified column and then Delete any records which now contain a value in the ready column. Can someone recommend the best course of action to accomplish this. If it was just a form, I would simply do a Me.Requery cmd. But because the table already exists, I would have to delete the table each time for an update. Any help would be greatly appriciated.

Cheers,
Vince

 

[PHV]

Why not simply bind your form to a query instead of a temporary table ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[GingerR]

And why are you constantly adding and deleting records to/from a second table (OpenJobs)? Like PHV says, you can just use your main table and create queries that show various sets of data on-the-fly. No need (and bad design) to be copying the same data to different tables. You can see already the problems it is causing. We store data in one place, and simply display or do calculations on it with queries, forms and reports. Make sense? You could just have a query called 'OpenJobs' that filters on that field where it is 'null'.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[nwprog]

You know its funny you should say that because I actually figured that out this afternoon. But since you were kind enough to respond, please allow me to pose the second half of my question with regard to this same form. The query is working just fine, but now I need to reopen the records in the form that they are entered in. How do I recover the input form with the values from the record. I can get the form to open using the following code,

DoCmd.OpenForm "Aircraft Calls" acNormal

but it defaults to a new form not the record that I want to work with. I tried adding a WHERE condition, but I don't think that I'm coding it right.

DoCmd.OpenForm "Aircraft Calls" acNormal,,Like [callid]

I'm trying to recover all the populated columns back into the form so that the subseqent shifts can pick up where the previous shift left off by matching the primary key in the query results and the table the query is built off of. I hope I've explained my dilemma sufficently. I'm pretty sure that its just the way I'm programming it. Thanks for any help you can provide.

By the Way, I set the forms Timer Event to Requery every 60 seconds, so we are getting a real time result, but it won't accept and Requery for me on the GotFocus Event, any thoughts as to why?

 

[GingerR]

but it defaults to a new form not the record that I want to work with

Do you have DATA ENTRY property set to YES?

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[nwprog]

Yes Ginger, the Data Entry Property is set to yes.

 

[GingerR]

That is why it's opening 'blank', ready for 'Data Entry', ready for you to enter new data. Set that to NO and you should be all set.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[nwprog]

Ginger, (I am assuming your a woman) If I could see you I would kiss you. You are a genuis. Thanks a million.