Updating certain fields, deleting others. Please help =]

[manglao]

Hey guys, I am posting here cause I have an access related problem that I have no idea about fixing, google didnt help much so I figured you guys might know.

I am a university student doing an access project for one of my modules. I wont go into much detail about the project but basically we are making a library management system and we arent sure how to handle a particular problem.

We have also been told that we arent aloud to use VB at all.

I dont want to ask for to much information (Cause this needs to be original work, no plagiarism). Basically we need a button on a form which deletes certain fields on the form from one table and then adds a whole new record to another table using data on the form.

So basically I have 3 questions:
How theoratically could we achieve this? (Macros, Queries, Both)
What are the relevant macro or query statements we would need?

Side question: Is is possible to easily change the button styles in access 2007

Any help would be much appreciated, but please no VB. =]
Thanks in advance for any replies.
Access 2007

-Manglao

[pkstormy]

Well, to start, having a button on the form will mean running vba coding behind that button (whether it calls a macro to open a query or it just opens a query (which is easier)).

I would design a query which has a field in it for the autonumber/unique field (such as PersonID) with criteria such as: =Forms!MyFormName!PersonID. You can then set this up as an update query to remove certain values (ie.update those fields with Null). You could change (and save as another name) this type of query to an append query to append the data to another table.

Or you can again use another query (creating expressions) to grab data from the form and use that in append type query to append data from the form to a specific table. For example, one of your expressions might be: FirstNameExp: Forms!MyFormName!FirstName (and this field appends to field FName in the query to append to table XYZ.)

Ideally, you'd use the docmd.openquery "MyQueryName" command (if ADO) in vba to execute these in the OnClick event of the button. You can use DAO coding type as well.

ie. (in ADO)

Private sub OnClick_MyButtonName()
docmd.setwarnings false
docmd.openquery "MyQuerytoUpdate"
docmd.openquery "MyQuerytoAppendtoNewTable"
etc....
docmd.setwarnings true
end sub

[manglao]

thanks for your help. We appear to have solved the problem

[pkstormy]

thanks for your help. We appear to have solved the problem

Could you post what your solution was?