Using fields to update others

[KLouise]

I want to update fields B and C in a table based upon another field (A) in the same table but I want:

• Field B to update when the original field A is changed in the table (I have managed to get this working in the form but I am trying to get the fields in the table to do the same as sometimes we batch update via the tables and this doesn't seem to work so we have to go into each record individually in form view to update the fields)
• Field C to only use part of the original field

Any ideas where to start on this?

 

[plog]

Yes, first you need to decide why this needs to occur, and then most likely not do it. Except in rare instances you shouldn't store calculated data, and as you described them, Field B and C are calculated fields.

Instead of storing them, you should probably calculate them when you need them. Just to be sure, can you tell me how to determine B and C? And is there every an instance where A could change but B and C would retain their old values based on the old A value?

 

[KLouise]

Field B would be a hyperlink of the first field... I may be overcomplicating it

Field C would use the last part of field A to determine the file type

 

[jdraw]

Instead of talking in the abstract can you tell us in plain English WHAT you are trying to do? No Access terms, just plain talk. We only know what you tell us, and up to this point it's all abstract.

 

[KLouise]

Field A is a file location which I want to be able to link to from the database so the file opens - for example C:\Users\KLouise\Documents\document1.doc

This came up after the database had already been in use for a while so I created a hyperlink field (field B) so that users could just click on the link to open the file... however I can't get it to work so that the hyperlink field updates in the table but it updates in the form.

I also want to be able to update other fields with parts of this field, such as the file type and file name, which are all part of the original field. So in this case the file name would be document1and the file type Word.

Does that make more sense?

 

[jdraw]

Sort of, but too detailed at the moment.

What is the purpose of the database?
What do users do with the "document"?

if you were at McDonald's what would you say to the person behind you to describe your issue/database/business? Suppose that person does Not know you, does Not know Access nor database.

 

[plog]

Yes, that makes more sense and I was correct in my initial assessment. You should not store Field B and Field C. For field B, I would place a button on the form that says 'Open File' and then use the appropriate Do.Cmd method to open the corresponding file. For field C, I would 'calculate' it based on what's in field A. Specifically, you would use some the string functions on this page: http://www.techonthenet.com/access/functions/

Again, you shouldn't store either of these fields.

 

[KLouise]

I work for a team that keep a record of evidence documents. These are stored on a central filing system on a shared server in a folder named as the case number.

We have a database is to record the evidence documents, where they are stored, version history, file type, size, owner and a description of the type of document.

The users of the database update the document but don't do anything with the document itself other than store them to the central filing system and provide the owners of the documents with the link for where they are stored.

I have been asked to automate as much of the data entry into the database as possible, much of which I did when I created it 3 years ago.

I want to provide the users of the document with an active link to the document when I send them a report in excel format.

The users of the database have asked if I can automatically update the file type and the file name as this information is in the location of the document.

They have also asked if I can set up a button to open the folder where the document is using this information and automatically update the file size but that's another issue altogether!!!

I'm sorry if it sounds complicated - I've been bombarded with requests to make just about everything automatic... they'll be asking me to get it to make them a cup of tea next!

 

[KLouise]

X posted.

Thanks Plog.

I'm being asked to make all sorts of 'improvements' by people who want to save precious seconds, some of who aren't very technical but have looked on the internet and told me it can be done.... doesn't mean it should be...

 

[plog]

Tell me about it. Half my job is protecting my coworkers from themselves. Only today exists for them, never tomorrow.