Appending & Deleting records w/ command button

[Jill]

I developed a database that keeps track of all assets in our technology department. We are in the process of "retiring" some assets, example, computers. Thus I have created two tables, tblComputers and tblRetiredComputers. I have added a command button to my Computer form that users can click on to retire an asset. It appends the record to the tblRetiredComputers and deletes it from tblComputers.

This works good, except for the fact that it's prompting the user to enter the asset number everytime they hit the button.
In my queries I have a criteria where tblComputers.AssetNumber = [Forms]![frmRetiredComputers]![AssetNumber]

Thus, everytime a user goes to retire an asset, this paramter pops up wanting the user to enter the AssetNumber. Is there a way I can get it to automatically look at the asset number, instead of having the user re-enter it when they want to retire the asset?

I think I am definitely out of my realm of expertise, so any suggestions would be greatly appreciated. Thanks.

 

[Jack Cowley]

I might have approached you problem a little differently.... Instead of another table add a Yes/No Field to the exiting table and name it Retired. Then the Retire button just sets this field to True. Base the form on a query that shows only equipment that has No in the Retired field....

But to answer your question: The criteria should read:

[Forms]![frmRetiredComputers]![AssetNumber]

Do not add the 'tblComputers.AssetNumber = ' bit....

Hope this helps.

Jack

 

[Jill]

Thanks, Jack. I am doing as you suggested. I added a retired (yes/no) field. If it is checked I have it coded to run the append query which copies the data into the table Retired Computers. This is the query where I used the criteria =[Forms]![frmRetiredComputers]![AssetNumber]

My question now is how can I get this to run without popping this ([Forms]![frmRetiredComputers]![AssetNumber]) up as a parameter field everytime the user checks the field to retire the item.

I can't just take it out or the query will copy all of the records in the Computer table over to the Retired table. Is there a way that I can code it to look at the Asset Number without having the user have to enter it as a parameter??

Any help would be greatly appreciated!

[This message has been edited by Jill (edited 09-12-2000).]

[This message has been edited by Jill (edited 09-12-2000).]

 

[Richie]

Did you set the query parameters? By the way Jack's suggestion about it being unneccessary to move retired records sounds like pretty good advice to me.

 

[Jack Cowley]

Jill -

If I were you I would not move the record(s) as there is no reason to do it. Just checking the Yes/No field is all you need to do. Now all of your data is in one table. To see retired equipment create a query based on your table. In the critera field of the Retired Yes/No field put in a -1 (this is a minus 1). When you run the query you will see only the retired equipment. Create another query exactly like the first one but put a 0 (zero) in the criteria field and you will see all the active equipment. Base your form on this query. Now you do not need to update any tables, etc. I think this is a simpler and easier approach to what you want to do....

Good luck!

Jack

[This message has been edited by Jack Cowley (edited 09-12-2000).]