How to get name of current field in table? (1 Viewer)

davejong

New member
Local time
Today, 15:12
Joined
Jul 3, 2020
Messages
13
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.
 

Micron

AWF VIP
Local time
Today, 11:12
Joined
Oct 20, 2018
Messages
3,476
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.
 

June7

AWF VIP
Local time
Today, 07:12
Joined
Mar 9, 2014
Messages
5,425
There is generic reference possible using ActiveControl but I have never used it as I find it unreliable. If you expect ActiveControl to be a textbox but just clicked a button then the button is active. So must be very careful using this method.

How are there multiple variations of this code? One procedure with 2 arguments is a generic construct.

Correct, your choice. It is a balancing act between normalization and ease of data entry/output. Normalize until it hurts, denormalize until it works.
 

Cronk

Registered User.
Local time
Tomorrow, 02:12
Joined
Jul 4, 2013
Messages
2,770
All but one view expressed here is to use a second table for recording type of document (normalization). However, it's your database and you can choose to ignore that advice and have an inferior design.

Add a third table name DocumentType containing all the different types of qualifications. The existence of a qualification ID and document filename and path indicate a particular person has that qualification. It also lends itself to having a bit of code to loop through existing documents to populate that person qualification table (ie to give you the "names of the fields" that you sought in your OP).

It is then easy to query your data as to whom has qual A and (qual B or qual C) because it's normalized. Your proposed design will not make for easy query design because it will lack normalization.
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 02:12
Joined
Jan 20, 2009
Messages
12,849
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.

Wrong. You have probably already wasted more time trying to work around the problems of denormalized data than it would have taken to change it. If not you certainly will in time.

Your current structure is a dead end and you are wasting your time continuing on that path.
 

davejong

New member
Local time
Today, 15:12
Joined
Jul 3, 2020
Messages
13
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...

Hi Micron, sorry for expressing myself so badly, if that was the message I transmitted. What I meant was that this basic single table design is, I think, fit for purpose for this particular fairly simple and small-scale application.
I never was an absolute wizard at programming. Good enough, but definitely not a wizard - otherwise I might still be doing it ;-)
You're quite right that my practical experience doesn't include relational databases - when they emerged into the commercial mainstream, I was already done with coding. I continued working in IT for a long time though, and I subsequently did some academic study on relational data bases. So I do completely understand the imperative for normalisation.
 

Micron

AWF VIP
Local time
Today, 11:12
Joined
Oct 20, 2018
Messages
3,476
If I came across as condescending, it wasn't meant that way. I gave you the benefit of the doubt when I said you might be a wiz in that area. It's just that I think others would agree when I say there's seldom any long term benefit to a flat file in Access. Short term, sure - less work. Long term, usually more trouble than necessary.
Anyway, hope you get or got a solution by now.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:12
Joined
Feb 19, 2002
Messages
42,986
And it will turn on you and bite you in the you know what:)
 

Users who are viewing this thread

Top Bottom