using a macro to tranfer info from one table to another? 1

[abbazabba]

Hi there everyone...

I'm creating a database for an equipment sign out system in a college.

At the moment, I have a form to type in the students details, equipment borrowed and date due back.

When the student brings equipment back, I can bring up their details by searching for their name using a query.

I have a tick box titled 'returned' on the form.

I need these students info to be stored in another table and deleted from the 'current' loaned equipment list when this tick box is clicked, as it is no longer necessary to store it there.

I know how to activate a macro using the on click option, but not how to transfer then delete the file.

Help!

Cheers

Phil

 

[psemianonymous]

First, I'd like to add the disclaimer that I don't recommend you delete it from the main table anyway. I'd recommend a "FINISHED" flag of some sort, that you can check to True and then ignore any FINISHED items when doing lookups.


But basically what you need to do is use parameter queries that 1. Add item to new table, 2. Delete item from old table. Then in your macro, run query#1 and query#2.

 

[abbazabba]

Ok, maybe deleting it from the original is a bit extreme..

You mentioned a finished flag of some sort. Each record has a tick box in a field called 'returned'.

I made a query and in the criteria for the 'returned' field I put: Like "«Expr»"=False

This brought up only the records where the equipment hadn't been returned.

I also have a parameter query that searches for a chosen item of equipment.

How do I make the two work together so that every time I search for an item it only looks in the non-returned items?

 

[psemianonymous]

This is kind of nasty, and others don't recommend it because of the performance hit, but I like to make queries that filter the data for me. Basically I'm saying that you make a query called "Unreturned Items" or some better name, then you use that query for a data source instead of your original table. Dealing with small amounts of data, there is no noticeable performance hit. Don't run Wal-Mart's inventory doing this sort of thing, though.


So in your case, use your newly-created "Unreturned Items" query (find a better name for it, but you get the idea...) as a data source for your search-for-item parameter query.

 

[abbazabba]

wEY heY!

That works really well. Now when I run the search it only brings up unreturned items.

Thank you very much for your time!!

Phil.