How to get name of current field in table?

[Rene vK]

When you give a person a fish, you feed him for a day. When you teach him how to fish, you feed him for a lifetime. While we may not get to "perfect" we should at least try to minimize the issues. The more bad tables and relationships and object names, the more things in the app that will be fragile and require fixing or working around. One thing that I have discovered over the years is that if you don't have time to do it right the first time, you certainly don't have time to do it twice or three times, or whatever. Believe me when I tell you about the scars I acquired from doing it wrong.

As long as we are all pro's..... but we're not! For most of us it is an extra task. We do try to work in the way it should but sometimes you need a path to find a way. If people like it or not, some of us just want it to work.
If most of us are pro's we would not try to find answers on a forum. Then we use our knowledge to build superb solutions for businesses and other clients. The only problem I see with sloppy building is going from problem to problem. Preparation is key, even for us amateur fools!

 

[Cronk]

I've been engaged many times to do something to an existing "working" system where the original "developer" has moved on to another job. It's very easy to see their skill set in looking at table design and inefficient operation.

That is not being critical though of the efforts in trying to create a practice best solution. On re-visiting some of my early efforts, I could have done better.

It's one thing to do something sloppy through ignorance but not acceptable when you know the difference. Just because something works, that does not mean its "superb".

 

[davejong]

Wow, sorry everyone, I was not checking in here over the weekend and I'm amazed at the level of activity. Thank you, I appreciate your time and effort.
Also sorry I obviously didn't explain my problem well. I'll give it another try.
My database is a singe table, a list of people with information such as contact details, job role. Also I use it to manage their onboarding to the role and this involves keeping track of whether a number of activities have completed, each evidenced by the presence of a specific type of document. So for each of these I have a field to show whether done/not done.
Alongside the Access database I have I folder structure: a folder for each person and under that folder a repeating structure to organise the evidence documents. So there's a folder for Joe Bloggs, and under that folder are subfolders for Joe's specimen signature form, scope of practice declaration etc.
There are no forms, so far I haven't seen a need for any.
So now when I get an evidence document I file it away in the appropriate folder and then mark "done" in the corresponding field in the table row for that person. I'm changing this so that the database can go and look for itself to see if the evidence documents are present. I'm creating a query with calculated fields, one for each of the evidence documents. Also I have added into my table a new field "FilePath" to hold the name of the evidence documents folder for the person (I could derive this from their name and other information but I'm leaving that for a later enhancement).
I have a function like this:

Function GetFile(Pathname As String) As String
GetFile = Dir(Pathname, vbNormal)
End Function

Calculated field for Specimen Signature is like this:
Specimen Signature: GetFile([FilePath] & "\Specimen Signature\")

Calculated field for Scope of Practice Declaration is like this:
Scope of Practice Declaration: GetFile([FilePath] & "\Scope of Practice Declaration\")

... and similar for each of the other evidence documents.

This looks very inelegant and so I'd like to have the same code in each of these several fields, and have the lowest level part of the folder name (the "Specimen Signature" or "Scope of Practice Declaration" text string) picked up from the field name, either in the field calculation formula or in the called GetFile function.

 

[theDBguy]

Wow, sorry everyone, I was not checking in here over the weekend and I'm amazed at the level of activity. Thank you, I appreciate your time and effort.
Also sorry I obviously didn't explain my problem well. I'll give it another try.
My database is a singe table, a list of people with information such as contact details, job role. Also I use it to manage their onboarding to the role and this involves keeping track of whether a number of activities have completed, each evidenced by the presence of a specific type of document. So for each of these I have a field to show whether done/not done.
Alongside the Access database I have I folder structure: a folder for each person and under that folder a repeating structure to organise the evidence documents. So there's a folder for Joe Bloggs, and under that folder are subfolders for Joe's specimen signature form, scope of practice declaration etc.
There are no forms, so far I haven't seen a need for any.
So now when I get an evidence document I file it away in the appropriate folder and then mark "done" in the corresponding field in the table row for that person. I'm changing this so that the database can go and look for itself to see if the evidence documents are present. I'm creating a query with calculated fields, one for each of the evidence documents. Also I have added into my table a new field "FilePath" to hold the name of the evidence documents folder for the person (I could derive this from their name and other information but I'm leaving that for a later enhancement).
I have a function like this:

Function GetFile(Pathname As String) As String
GetFile = Dir(Pathname, vbNormal)
End Function

Calculated field for Specimen Signature is like this:
Specimen Signature: GetFile([FilePath] & "\Specimen Signature\")

Calculated field for Scope of Practice Declaration is like this:
Scope of Practice Declaration: GetFile([FilePath] & "\Scope of Practice Declaration\")

... and similar for each of the other evidence documents.

This looks very inelegant and so I'd like to have the same code in each of these several fields, and have the lowest level part of the folder name (the "Specimen Signature" or "Scope of Practice Declaration" text string) picked up from the field name, either in the field calculation formula or in the called GetFile function.

Hi. Just curious, how many records do you have so far and how many document fields are in the table for each record?

 

[davejong]

Hi, 250 records ATM and 9 different evidence documents for each.

 

[theDBguy]

Hi, 250 records ATM and 9 different evidence documents for each.

Okay. That doesn't sound bad right now. You should be able to normalize your table structure without much trouble before it gets worse. I would suggest doing that first.

 

[davejong]

Okay. That doesn't sound bad right now. You should be able to normalize your table structure without much trouble before it gets worse. I would suggest doing that first.

Thank you for that suggestion. As a former developer and system designer, I understand the concept of normalisation, but I'm not really seeing what the practical benefit is here to breaking my current single table down into multiple tables. The evidence documents really just equate to ticks in boxes; I have no significant interest in their content, only whether they are present or not. And for each person there will be exactly one or zero instances of each document type. I expect I'm missing something - but I appreciate that you're not here to (re)teach me database design for free ;-)

(I should have perhaps have also said that my 250 records is a fairly stable number. People come and go but over time the population of data subjects remains around this number.)

 

[theDBguy]

Thank you for that suggestion. As a former developer and system designer, I understand the concept of normalisation, but I'm not really seeing what the practical benefit is here to breaking my current single table down into multiple tables. The evidence documents really just equate to ticks in boxes; I have no significant interest in their content, only whether they are present or not. And for each person there will be exactly one or zero instances of each document type. I expect I'm missing something - but I appreciate that you're not here to (re)teach me database design for free ;-)

(I should have perhaps have also said that my 250 records is a fairly stable number. People come and go but over time the population of data subjects remains around this number.)

Hi. In that case, have you tried the demo posted by Rene in Post #17? I haven't looked at it, but I don't know of a way to make your function column aware in a query, in Access. If the demo works for you, then maybe you can go in that direction.

As a side note, when people ask for help in creating or using a database application, we, database developers, tend to provide guidance into its proper use. There's a saying if you have a hammer, everything looks like a nail. However, there's an appropriate tool for every task. If you are happy to have all your data in a single table, then perhaps the tool for you is Excel, not Access. I know in Excel, you can make the function column aware because Excel can use "absolute" addresses. Just my 2 cents...

 

[davejong]

Hi. In that case, have you tried the demo posted by Rene in Post #17? I haven't looked at it, but I don't know of a way to make your function column aware in a query, in Access. If the demo works for you, then maybe you can go in that direction.

As a side note, when people ask for help in creating or using a database application, we, database developers, tend to provide guidance into its proper use. There's a saying if you have a hammer, everything looks like a nail. However, there's an appropriate tool for every task. If you are happy to have all your data in a single table, then perhaps the tool for you is Excel, not Access. I know in Excel, you can make the function column aware because Excel can use "absolute" addresses. Just my 2 cents...

Thank you again.
I had trouble opening the file Rene posted due to security restrictions on my work computer. I need to try on my personal laptopn after work.
Your comments about Excel are appreciated. I inherited the database and I started to build complementary Excel spreadsheets to do useful stuff, because I'm quite proficient in Excel. But then my boss suggested to me that it would be better to build onto the Access database rather than having spreadsheets alongside - and she does have a point - so that's why I've arrived here. Transferring everything to Excel would be a possibility (I could certainly do what I'm trying to do here quite easily in Excel), but the multi-user access capability of Access is advantageous for this database.

 

[Isaac]

If you already have a function that looks at a string value passed to it (the document type), I don't see why you can't simply USE that function and pass in your field in your query? So if you have a function Function DoesDocumentExist(strName,strType), then simply use it against the query field in the query.
Query field:
DocumentExists: DoesDocumentExist(strName,[tablefieldname])

Then within your function, you use If statements or Select Case statements to match the field name with the document types, unless they're already identical.

 

[davejong]

People, I totally agree on the normalisating an good practice but this guy wants a solution... Let him try and do the good stuff later, he says he is a beginner!

Dave, I hope I understand your question and ,my attribution, will help you on your way.
Open the form, click the field and see what happens.

Rene, thank you for this.
I see what you've done and I think it's very likely that I will have use for that code at some point in the near future.
It doesn't really answer the problem I'm trying to solve here though (except for providing some ideas for refining the function that looks for the file, thank you again for that).
I think you will see what I mean if you read my second explanation further down the thread.
Once again thank you for your input; I really appreciate it.

 

[June7]

@davejong, could you respond to comment in post #4?

I don't think you even have a 'problem' to solve.

 

[davejong]

If you already have a function that looks at a string value passed to it (the document type), I don't see why you can't simply USE that function and pass in your field in your query? So if you have a function Function DoesDocumentExist(strName,strType), then simply use it against the query field in the query.
Query field:
DocumentExists: DoesDocumentExist(strName,[tablefieldname])

Then within your function, you use If statements or Select Case statements to match the field name with the document types, unless they're already identical.

Thank you... I think this is where, as I feared, my ignorance is going to be really embarrassing.

So I had

Specimen Signature: GetFile([FilePath] & "\Specimen Signature\")

and following your suggestion - taking it quite literally - I changed it to

Specimen Signature: GetFile([FilePath] & "\" & [tablefieldname] & "\")

Now when I run the query a message box comes up asking me to input a value for tablefieldname. Looks like I have misunderstood.

 

[Isaac]

Okay, your picture is beginning to become more clear to me. I think I am with @June7 on this one. The route I was going down, doesn't really seem like it is going to be any shortcut at all. Sorry I can't be of more help at this time.

 

[davejong]

@davejong, could you respond to comment in post #4?

I don't think you even have a 'problem' to solve.

Sorry for not responding to that sooner.

I have a solution that works, with the last part of the file path ( e.g. "\Specimen Signature\") hard coded in each of these "Is the document present?" fields. But it just seemed to me that this can't be the optimal solution because in each case the hard coded text string is identical to the name of the query field. So I thought that it should be possible to reference the field name so as to get rid of the hard coding and have the same code in every field i.e. "see if there's a file in the folder (FilePath concatenated with the name of the current field)"

 

[davejong]

Okay, your picture is beginning to become more clear to me. I think I am with @June7 on this one. The route I was going down, doesn't really seem like it is going to be any shortcut at all. Sorry I can't be of more help at this time.

Thank you anyway, I do appreciate you taking the time to try to help me.

 

[June7]

Your function needs two data elements to build image file path: user and document.
Your data structure is not normalized. Instead of a field (is it yes/no?) for each document type, a normalized structure would have a related dependent table where each document is a record. In which case, data would not have to be passed as arguments and procedure could be hard coded to directly reference two specific form fields/controls (user and document). As is, procedure needs to be explicitly told which document type to use because there is no single field to pull value from.

Your current procedure is optimized to accomplish task with this data structure.

If there is a 'problem' to solve, it is the non-normalized data structure.

 

[Rene vK]

Rene, thank you for this.
I see what you've done and I think it's very likely that I will have use for that code at some point in the near future.
It doesn't really answer the problem I'm trying to solve here though (except for providing some ideas for refining the function that looks for the file, thank you again for that).
I think you will see what I mean if you read my second explanation further down the thread.
Once again thank you for your input; I really appreciate it.

Dave, your welcome. Hope we got you on your way. My idea is not directly written for your automation.
Good luck.

 

[Deleted member 147267]

I imagine a db copy would answer a whole lot of questions. As I follow along in this thread, I can't help but wonder about something. To take DB Guy's notion a bit further, it would appear that there is not much interest in building a screwdriver for driving this screw because a hammer seems just fine. I'd bet money that the classic Excel to Access migration errors and thinking is at play. Be that as it may, I also don't see any way around making each calculated field call the function by manually writing an alias and function call that passes the field name itself. There is no other way, AFAIC. The fact that it has to be done in several fields just to retrieve different values of the same basic thing means Excelitis is at play here. As soon as some other measure gets added to the mix, the query has to have a field added, and we all know what that usually points to.

 

[davejong]

Your function needs two data elements to build image file path: user and document.
Your data structure is not normalized. Instead of a field (is it yes/no?) for each document type, a normalized structure would have a related dependent table where each document is a record. In which case, data would not have to be passed as arguments and procedure could be hard coded to directly reference two specific form fields/controls (user and document). As is, procedure needs to be explicitly told which document type to use because there is no single field to pull value from.

Your current procedure is optimized to accomplish task with this data structure.

If there is a 'problem' to solve, it is the non-normalized data structure.

Thank you again for taking the time to think and write about this, it is kind of you.

It's literally decades since I made my living as an applications designer/developer so I expect my thinking is outdated. I'll write it down anyway ;-)

The specific coding challenge I was asking for help with is related to reuse, in my mind. I wanted to avoid having multiple variant instances of the same code. I imagined that there would be some way for code executed from within a field to be aware of it's context, to know what field it was being executed from. From the replies I gather that this is not the case.

As I recall, logical data base designs should always be normalised but physical implementations are sometimes compromised where it's pragmatic to do so. In my view this is a case in point. The evidence document is a distinct logical entity from the person but it's information content (in the context of this application) is minimal (exists/doesn't exist). So I think that doubling the number of tables in the schema to achieve normalisation doesn't bring much practical benefit (it makes my coding challenge go away, but what else?) in return for the increase in complexity.

In summary I take your point that this may be better seen as a choice to be made rather than a problem to be solved.