Record Version control 1

[cristobalito]

Hello again dear people

I have another issue. This time related to different versions of one record.

For example, I have main table where a new record would be entered. In a few months' time, the user may need to update some of the information related to that record. However, I do not want to lose any of the original information. I want to the user to be able to create a new version of the same record. The reason for doing this is so that over time it will be possible to track how a record has changed and how many times etc. I want this functionality to be an automatic function in order to remove/minimise human error...

I hope this is not too tedious or on the other hand impossible?

Kindest Regards
Cristobalito

 

[AlexMidd]

I would have the non-changeable items for the record, i.e. at minimum the primary key and any identifying data, in a main table. Then I would create a second table (for data that can change) with the primary key for the main table as a foreign key, then link the two tables via the keys on a One-To-Many basis.

This way, as the changeable data is changed, you just create a new record with the same id in the foreign key field of the second table. That way, you now have a history for each item with previous data in previous records for each item.

e.g.
MAIN TABLE
MAIN_ID: 1 Name: Record1
MAIN_ID: 2 Name: Record2

SECOND TABLE
ID: 1 MAIN_ID: 1 DATA: AAAAA
ID: 2 MAIN_ID: 2 DATA: BBBBB
ID: 3 MAIN_ID: 1 DATA: CCCCC

So, record1 now has two sets of data with AAAAA and CCCCC as the data, with additional records being added every time data changes and the MAIN_ID field linking back to the appropriate record. Hope this makes sense. Have fun! )

Alex Middleton

 

[THoey]

Good design Alex... I would just add a date column to the detail table to know when the the change was made... Terry
**************************
* General Disclaimor - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...

 

[mikehoot]

I agree, a good idea, and can be customised as required. For example, I have implemented Access Security and therefore can record CurrentUser() as a text field in each new record, including records re-created from existing records. This gives me great control when it comes to audit.

Nice One :-9

 

[cristobalito]

Thank you all for your guidance. Your tips seem most usful for me.

I think I have understood the table linking concept very well. However, how do I get a user to input the data into the right tables using forms? Should I have a 'new version' button which changes all the input boxes to point at the second table? If so is this easy?

Most Kind Regards
Cristobalito

 

[AlexMidd]

Set up a form for doing new versions, say, Form1. Set the form's RecordSource to the second table. You can then set up text boxes or other controls on this form with their ControlSource property set to the appropriate fields. If the form is set up for new records (see the property sheet for the form - right click it and select properties) data entered into the boxes will add a new record to the table.

You will need some way of telling the system which record in the main table the new record refers to. You could have the user enter this, or it can be chosen from a combo box, or the form can take it from another form, say a previous form on which the user has selected a record from the main table.

As you can see, this is getting much more complicated and too complex to sum up here. You may wish to check the help files or the wizards as I find these helpful in these situations. For forms, go to the 'Forms' tab, select 'New', then Design Wizard and click 'OK'. This will give you the option of setting up a form with fields from more than one table and where the record selected is set up for you etc.
Have fun! )

Alex Middleton

 

[cristobalito]

Alex,

thank you very much for your help. I will try out your suggestions. You are most kind,

Regards
Cristobalito

 

[mrf1xa]

Another way might be to put a subform on your original input form, based upon the detail table, and link the ID fields. So, when you select a record on your main form, the subform will show all the associated detail records, and new ones can be added too.

Nigel

 

[cristobalito]

Thank you for your idea Nigel. This access stuff is quite complex hey! But I think with people like you guys around things should become manageable and acheivable for me..

kindest regards
cristobalito

 

[cristobalito]

OK Folks,

I've had success! Thank you all very much.

What I did was to create a one to many relationship between the main table and the version table. Then I created a main form with a subform embedded. I've added a button to the subform to update the versions as required. Yippee, one task of loads completed!

Thank you all,
Kindest Regards
Cristobalito

ps. if MikeHoot reads this, I would like to know more about the CurrentUser() function? How could I include this, it sounds very interesting to me...

 

[Darrylles]

Hi,

Sorry if I got here before Mike, (Mike may not respond again), but the CurrentUser() function simply returns the name of the user that manipulated that record.

So if you add a 'ChangedBy' field to the table, then you know who updated the record at that time.

To get the users name you say:
Textbox = CurrentUser()
or
msgbox CurrentUser()
that's it.

You'll probably get 'Admin' if you try it now.




"Never argue with an idiot, he'll bring you down to his level - then beat you with experience."

 

[cristobalito]

Hello Darrylles

thanks for your comment. I'm a bit naieve with access. I've put a field named 'UpdatedBy' now where do I put the 'Textbox = CurrentUser()'?

Sorry to bother you again
Kindest Regards
Cristobalito

 

[AlexMidd]

The easiest way is to create a text box as part of your form or subform, and set its ControlSource property to the field that will store the user's names. Then it might be appropriate to set the DefaultValue property to =CurrentUser(). What this will do is, when a new record is created, automatically put the current user's (i.e. whoever is logged on to Access, 'Admin' by default) ID into the field for the new record, thus you know who has entered the new record. You could even make the text box invisible so that users cannot change it.

As I say, this is the easiest way (IMHO) but not necessarily the best way (i.e. if anyone knows better ....).

Alex

P.S. Always glad to have been of help. Have fun! )

Alex Middleton

 

[cristobalito]

Thanks once again Alex.

I've sussed it now. Now I like the idea of a creating a login screen and ID, which would automatically be entered to this field. Have you done this type of work before?

Regards
Cristobalito

 

[AlexMidd]

Er... yes. I am hesitant because it is a very large and complex subject, although there are many previous posts and FAQ's on this site to help. The complexity relates to setting up permissions etc, but I would imagine that you could have a login purely to keep track of users, rather than for full security features.

Have a look at previous posts and FAQ's for this info (I don't have the article numbers handy) if you want to look into this area, but as I say, it's not for the faint-hearted. If you decide you want to go down this route, let us know and we may be able to help on specific points. Have fun! )

Alex Middleton