Database Updates/Users

[ParyGoal]

Hello everyone

I have been looking all over the internet for an example that may help me with what I am trying to accomplish.

I have a visual basic form that contain several tabs. Each tab is bound to a child table. The is a parent child relationship.
The main form has a grid that keeps truck of the date the procedure was created, the create date, createdbyID, RevisedByID, Revision date, Audit Date, and finally lastAuditByID.

I am able to capture the create date, createdByID field as a soon as you click on the create button. Please note that I am working in a bound mode.
My question is how can keep truck of the person who last revised the document, the date it was revised, Last person to audite the documents, and auditedbyID?

I would greately appreciate any help. Any example would also be very helpful.

Thank you

Parygoal

 

[mrdenny]

You would need to store this information within the database. Either via code when actions are performed in the front end, or via triggers so that you don't have to change the front end.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[ParyGoal]

Hi mrdenny

Thank you for your reply.
I agree with you on the fact that I need to store the info in the database. I already have a table set up with the required fields:
RefNumber Number
CreateDate Date
CreatedByID Number FK
LastAuditDate Date
LastAuditedByID Number FK
LastRevisionDate Date
LastRevisedByID Number Fk

When I first create a procedure for a selected equipment, I click create button which stores the CreateDate and CreatedByID.

The information is displayed on the Grid. When you clik on a particular column on the Grid all the corresponding detail information is displayed in all the grids with each tab.

Now how do I go about storing LastRevisionDate Date
LastRevisedByID, LastAuditDate Date, and LastAuditedByID according to each action?

I hope I was clear with my questions

Thank you

ParyGoal

 

[mrdenny]

I'm assuming that you are using SQL Server for the database. If you are you can put triggers on the tables which can log when the user inserts, updates or deletes data into the tables.

When it comes to marking when the data is viewed, you'll need to add a function into the form so that when it loads up it logs into the database is the backgroup and logs that the table is being viewed.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[ParyGoal]

I am actually using an Access Database.
Is there a way to accomplish the same thing using an access database. I know that you can't create trigers on an access table. But is there another way?

Thank you

ParyGoal

 

[mrdenny]

I'm not sure. I haven't used access for years. Check with the folks in the MS Access forum.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com