Updating a table in Access through code

I am using Microsoft Access '97 to create a databse which basically stores information about how a process is completed. I'm fairly sure I will need to use VB to carry out the following:

Each process sheet has a process number and is set to active if it is the most up to date revision.

What I want to be able to do is:

When a new process sheet is entered, I want Access to check if a sheet with that process number already exists. If it does I want to set it to inactive.

I also want it to retrieve the revision number from the old sheet and store it incremented by one in the new sheets revision number field.

I need to be able to tell Access to store the current date in the date field whenever the record has been created.

If anyone can help me with this or any part of this, I would be very grateful. I'm very STUCK!

To store the current date in the date field set the Default Value property of the field to:

= Now()

To display date only you can set the Format property of the field to:

Short Date

To open the table from within VBA, you can use OpenRecordset method of the currentDB object. Ref Access 97 help.

Then use FindFirst, Edit, and Update methods of the required recordset object for the rest. Refer to Access 97 help for more info on these methods. You can also see the Fields collection.

Hope that helps.

By the way, are you familiar with the Data Report in VB6?

Yes, I have a basic knowledge of it. Thanks for the help.

How do I change the orientation of a data report before loading it? What I want to do is to load the report in landscape orientation. By default it loads in portrait orientation. How do I do this?

Is the data report not generated depending on your printer settings? Can you not just change your printer to landscape as default and the data report will change as well?

Does that mean if I change the Orientation property of the Printer object, orientation of the Data Report will also change?

I'm sure thats what happened when I was using them in VB6.. Reports I had created on my system would not look correct on other people's pc's.

Changing the orientation of the printer to landscape loads the report in landscape mode. But changing the orientation property of the Printer object does not. Why is that?

I'm really not sure. I never looked in to it in much depth. Maybe someone else in this forum will know.