Trying to do something that would be easy with paper

[mjamie12]

I am completely new, but I have been reading these forums for a while as it has helped me get through accounting classes. However, I am attempting to do something that we have never done in class and I'm completely lost.

I have built a very simple database for a mill dog rescue that I volunteer for. Within the database, there are tables for vets, foster families, adopted families, etc. What I would like to do is create a button that copies 3 fields from the open record and pastes them into a different table, then deletes the open record. Specifically, when a dog gets adopted, we don't need to track the majority of the data, such as feeding frequency, but we would like to keep a record of the dog's name, breed and age. Thus, I would like to copy the data from the table called Dogs from the fields DogName, Breed, and Age (for the open record only) and paste it into a new record with the same named fields under a table called "AdoptedDogs".

I am not good with Access and the director is perhaps even less skilled, so this is why I was hoping to get a macro for a command button.

If anyone out there can help with this, that would be spectacular. Alternatively, if anyone out there can recommend a professional that can provide this code for not too much money, that would also work.

 

[jdraw]

I recommend this tutorial to help with database tables. It has a step by step process and includes answers. It will help you.

Good luck and Happy New Year.

 

[Matoco1]

There may be easier ways to do this but one way is to create an append query.

Create an append query which will add the data to another table, using the ID field as search criteria (do not append the ID number leave the 'append to' field blank). Once this is done add a button to your form and paste this code into the event procedure VBA

    DoCmd.OpenQuery "QueryName"
        
    DoCmd.RunCommand acCmdDeleteRecord

This will run the query and then delete the record in your open form.

I have attached a very basic example of this to show it working.

 

[Cronk]

I'll offer a different option that is easier to implement.

Just add an extra field to the dog table called Active (Yes/No) to default to True.

On your main, form add a check box bound to this field. When a particular record is finished with, just uncheck the box. Have a filter on the main form, so only records which have active set to true are displayed.

This has the advantage of not need to copy records as well as the ease of undoing the changes.

 

[mjamie12]

Thank you for the replies. I have not had a chance to read the tutorial or try the macro yet. (Work keeps getting in the way of my hobbies). I will read the tutorial tomorrow and try the macro first thing and post my progress. But again, thank you for the help.