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

davejong

New member
Local time
Today, 19:28
Joined
Jul 3, 2020
Messages
13
Apologies in advance, I'm an absolute beginner so likely to display embarassing ignorance.

I have a table containing details of people.

I keep documents relating to these people in individual folders.

I'm trying to create a query that shows me whether I have certain specific documents on file for each person.

In a VBA function I can go and look in a specified place to see if there is a file present e.g. in P:\Fred Bloggs\Driving Licence\

So I have a calculated field "Driving Licence" and for that I call my function passing it the person's name and the string "Driving Licence" and the function tells me whether there's a file in the "Driving Licence" folder for that individual.

Now imagine that besides driving licence I have several other document types that I'm trying to do the same thing for. In each case the logic is the same, the only thing that varies is the name of the document. So I could follow suit for the rest of the fields e.g. call my function passing it:
* name and "Birth Certificate" for the "Birth Certificate" field
* name and "Security Credentials" for the "Security Credentials" field
... and so on.
I'd really prefer to enhance my VBA function so that it just needs to be passed the name and then it will use that together with the field name to do the file lookup. So then each calculated field willl have the same code in it, just calling the function using the person's name.

How do I get the name of the current field within my function, so that I can combine it with the incoming person's name to specify the folder to go look in for the relevant document?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 12:28
Joined
Oct 29, 2018
Messages
21,358
Hi. Maybe you could post the code for your function to give us a better idea of what you're trying to do.
 

Micron

AWF VIP
Local time
Today, 15:28
Joined
Oct 20, 2018
Messages
3,476
Are you driving this from a query alone, or is there a form involved? A form would probably make this easier. You could, for example, have an unbound combo showing names and a multi select listbox where you choose one or more document types to look for. Fancier but more code would be 2 multi select listboxes - one for names, the other for documents, but would require a nested loop. The function code alone might not tell us what calls it (i.e. passes the parameters).
 

June7

AWF VIP
Local time
Today, 11:28
Joined
Mar 9, 2014
Messages
5,423
If you want to call function from query, what you have is what is required. Or one argument and concatenate fields.

What is table structure? Is each document type its own field? This is not normalized structure.

Even if you do normalize, still need same arguments for function.
 

Micron

AWF VIP
Local time
Today, 15:28
Joined
Oct 20, 2018
Messages
3,476
what you have is what is required.
Maybe not - but are we clear on what is and what isn't? You cannot refer to a query field in the same field you have the function in so maybe not. I interpret the question like "I have a query and I want to pass the name of the field that's calling the function". Not as far as I know. There might be a field property that you can pass instead (such as its caption) but I've never tried.
 
Last edited:

Rene vK

Member
Local time
Today, 20:28
Joined
Mar 3, 2013
Messages
123
you can make a _Click on your fieldname and start a function, create a string and try to find the file with Dir$
Sorry, this tablet is not very sufficient to browse my own code in Access.

To be clear: in my dbase I have a form which I click with the mouse. I catch one of the fields in that record.
 

Isaac

Lifelong Learner
Local time
Today, 12:28
Joined
Mar 14, 2017
Messages
8,738
Would be very helpful if could post your existing code.
 

Rene vK

Member
Local time
Today, 20:28
Joined
Mar 3, 2013
Messages
123
Something Like this?
Code:
Private Sub PersonsName_Click()
Debug.Print PersonsName.Value

dim NameToFind as string
NameToFind = "P:\" & PersonsName.Value & "\Driving Licence\" & fileName

If Len(Dir(NameToFind)) = 0 Then
   Msgbox "This file does NOT exist."
Else
   Msgbox "This file does exist."
End If

End Sub
 

June7

AWF VIP
Local time
Today, 11:28
Joined
Mar 9, 2014
Messages
5,423
But that will not allow function to be used for different document types, which is core of OP's question. For some reason they feel need to reduce function to one argument when what they have is perfectly suitable to the situation.
 

Rene vK

Member
Local time
Today, 20:28
Joined
Mar 3, 2013
Messages
123
But that will not allow function to be used for different document types, which is core of OP's question. For some reason they feel need to reduce function to one argument when what they have is perfectly suitable to the situation.
True but with a Select Case situation he can find the different files. I only try to get him on his way...
 

isladogs

MVP / VIP
Local time
Today, 19:28
Joined
Jan 14, 2017
Messages
18,186
I'm still not clear what your table structure is but from your description you should have at least two tables for this with a one-many relationship
Firstly a tblPerson table and secondly a tblPersonDocument table
The second table would include fields PersonID, DocType (e,g. Driving License, Birth Certificate), DocID, DareIssued, DocFullPath.
Creating that type of setup will make querying your data simple to manage
 

Cronk

Registered User.
Local time
Tomorrow, 06:28
Joined
Jul 4, 2013
Messages
2,770
I'm still not clear what your table structure is but from your description you should have at least two tables for this with a one-many relationship
Firstly a tblPerson table and secondly a tblPersonDocument table

Maybe the OP is trying to populate the second table by searching for the existence of the relevant file. In which case, it would be a matter of looping through the tblPerson, and then searching for every type of document, and if it exists, adding a record to the document table.
 

Isaac

Lifelong Learner
Local time
Today, 12:28
Joined
Mar 14, 2017
Messages
8,738
I think they are wanting to run a query with a vba function as one of the query fields to return whether a document type is existing in a folder. And some help in adjusting an existing function to just look at the field name in a query. Like June said I think they already have 99% what they need may just need some help adjusting code. like Select Function(fldName) where fldName will be the same as a document type...or maybe interpreted via the function.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 15:28
Joined
Feb 19, 2002
Messages
42,973
Guessing gets us no where.

@davejong,
Please post your schema and tell us where you are trying to place this code?

It sounds like your schema isn't normalized that that is what is causing your problem but that would be a guess also.:)
 

Rene vK

Member
Local time
Today, 20:28
Joined
Mar 3, 2013
Messages
123
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.
 

Attachments

  • FindDocs.zip
    43.6 KB · Views: 112
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Today, 15:28
Joined
Feb 19, 2002
Messages
42,973
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.
 

Gasman

Enthusiastic Amateur
Local time
Today, 19:28
Joined
Sep 21, 2011
Messages
14,046
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.
Well said Pat. That is exactly what I have in one of the links in my signature (My Philosophy), having being one of those, taught to fish when I first started. :)
 

Micron

AWF VIP
Local time
Today, 15:28
Joined
Oct 20, 2018
Messages
3,476
If you give a person a db fix that just patches up the problem, or just provides a means of overcoming one of many hidden hurdles created by poor design, if it's a fish, it's a shark.
 

Users who are viewing this thread

Top Bottom