How to get name of current field in table?

[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.

 

[davejong]

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.

Thank you for those observations, I appreciate the engagement.
I don't think I'm at liberty to post a db copy, sorry.
I inherited this application so I don't know whether it started life as an Excel worksheet and got migrated to Access.
You're quite right though to say that I'm bringing an Excel perspective to it. Right down at the detail level, passing the field name (column heading) to a called function would of course present no difficulty in Excel.
I absolutely take the point of the hammer/screwdriver analogy but I think in this case it's a bit of a caricature given that (it seems to me) the single-table design will still be quite fit for purpose.

 

[Deleted member 147267]

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.

This is certainly possible. My interpretation was that you wanted the function to figure out what field was calling it without you having to provide that information in the call. That cannot be done. Often, a function call in a query includes the field name, but you have to type in the field name in each field. By doing so, each record calls the function for each field you have the call in. The caveat is that you will have to alias the field. So

MyAlias: FunctionNameHere(fieldNameHere) would seem to be the syntax you need. The original question seemed to be phrased as if you wanted this:

MyAlias:FunctionNameHere() and expect the function to figure it what field that is coming from.

As for your position on why you would ever have a bunch of related tables rather than one flat file suggests that while you might be an absolute wizard at programming, that experience doesn't include relational databases or you wouldn't say that. To go into the whys and wherefores of this can be a deep subject that I don't have time for right now, but I'm confident others will add to that. Suffice to say that flat files such as yours make some data mining operations difficult, and some virtually impossible without extensive work-arounds; assuming it can be done at all.