Fetch a value based on the NAME OF THE COLUMN

[GrandMasterTuck]

Hi folks. I always have unique issues, and you guys are champs at helping to solve them. Here's a tough one (at least for me):

I have a query that's fetching records. Works like any other query. In one of my query's columns, I want the recordset to return a value based on a variable column name in the query. For example, let's say I have the following columns and records in the underlying table (on which the query is based):

NAME      AGE     RACE     SEX     FETCH
----------------------------------------------
James      18       W       M       AGE
Bill       19       W       M       RACE
Amy        20       W       F       SEX
Don        19       B       M       NAME

What I want the recordset in my query to look like is this:

NAME      AGE     RACE     SEX     DISPLAY
----------------------------------------------
James      18       W        M       18
Bill       19       W        M       W
Amy        20       W        F       F
Don        19       B        M       Don

I'm using that DISPLAY field to supply a value to a report, and I want to be able to sort of future-proof this report so that I don't have to hard-code a bunch of IF/THEN statements on that field (the one hosting DISPLAY) so the system knows which value to display. This way, if, in the future, the boss wants a new field added, like HEIGHT (for example), I can then get the report to display the HEIGHT value for any record by switching that record's FETCH value to "HEIGHT", and won't need to open the report in design view and modify the IF/THEN statements, I'll only need to modify the table itself to add the column.

EDIT: I guess I don't necessarily need the query itself to do this, it's fine with me if I can code a field on the report to do it instead. Just need help with the syntax. Again, the FETCH column in that table gives me the NAME of the Column from which I want to fetch the data, and that data is displayed in the DISPLAY column (which is then, in turn, displayed on the report, or instead maybe the field on the report does the 'calculation' to figure out which data to show... either way)

I guess what I'm asking is: what is the proper syntax for a report field's control source so it fetches a value from a different field that has a name that matches the value entered into the FETCH column.

Does that make sense? I hope somebody can help me with this... Thanks!

 

[plog]

NAME is a bad field name. It's a reserved word and makes coding more difficult than it needs to be. I suggest you change it.

This would be simpler if you had a primary key on this table. Also, you didn't provide a table name, so my code use 'YourTableNameHere'.

What you want can be acheived via a DLookup like so:

Display: DLookUp([FETCH],"YourTableNameHere","[NAME]='" & [NAME] & "' AND [AGE]=" & [AGE])

If you had an ID field you could use that in the criteria argument instead of every other field. Lastly, this is will be slow, the more records you have in YourTableName the longer its going to take.

 

[Galaxiom]

That is a very strange concept. It isn't a normal structure because you are storing information about display requirements in records about the subjects. Moreover it is weird that each record would have a different attribute to display in the same control on the form.

Is the most important thing about James his age? Or for Bill, his race? It makes absolutely no sense at all.

BTW Never store Age because it changes as time progresses. Store DateOfBirth.

 

[GrandMasterTuck]

Okay, I think I need to clarify a few things, and it's going to be a long post. Thanks to you both for the suggestions, but neither is what I need.

I spelled out some examples above that were pretty poorly thought out. My actual database is too complicated to try and explain what I need with actual examples from my system, so instead I come up with overlysimplistic examples, and sometimes don't think them through. Try this one:

I have a database called EmployeeCards. I have one important table (the other ones aren't important for this example): tblTimeCards. I use this database to track employees and the time cards they are issued. Time cards are plastic ID tags that give them permissions to enter different levels of the office building, so some employees may have more than one card.

The database allows me to issue a time card to an employee and print it on a plastic card using an Access report and a card printer. But the BOSS doesn't want all the cards to look alike. He wants certain data elements on some cards, but not on others. What I do NOT want to do is create several different reports for several different cards types. I just want ONE report that will print EXACTLY what the boss wants for each card type.

I also do NOT want to put massive complicated IIF statements on the report's fields. In the report (which prints on a card), I have the NAME on top (unless the EmployeeType is "RECEPTION", then it just prints the JobType with no name) and a variable field below that which will print one of the other values from one of the other columns. That second field's code looks vaguely like this (NOTE: THIS IS AN EXAMPLE! AND NOT REPRESENTATIVE OF MY ACTUAL TABLES OR VALUES):

IIF([EmployeeType]='MANAGER', [AssignedFloorName], IIF([EmployeeType]='RUNNER', [AssignedFloorName] & "-" & [JobType], IIF([EmployeeType]='ACCOUNTING, "ACCOUNTING", "ASSOCIATE")))

As you can see, this complicated IIF statement chooses what to print in that second field, depending on which EmployeeType is set for that record. The reason why I don't want these big IF/THEN statements? In my database, there are seventeen EmployeeTypes, which means that IF/THEN statement has to be SEVENTEEN levels deep (or sixteen with the last OR), and it's too much code to fit in one field on the card. Plus, if the boss wants to add EmployeeType number EIGHTEEN, I then have to reprogram that controlSource on that second field with yet another embedded IIF statement to account for the new possible value.

In a practical world, this database will be used by other people after I'm gone to a better job, and that person probably won't know squat about Access. I want to make it EASY for him to add new EmployeeTypes, and easy for him to grant the boss's demands if they amount to, for EXAMPLE: If the EmployeeType is "DANCER" (EXAMPLE! Just an EXAMPLE! I'm making this up as I go!), I want the Employee's HEIGHT AND WEIGHT in the second field (PLEASE DON'T ASK ME WHY I WOULD DO THAT!! THIS IS JUST AN EXAMPLE!). In that scenario, if my IIF statement in that controlSource doesn't account for "DANCER" EmployeeTypes, and if this replacement for me doesn't know how to code Access reports, they're sunk, because that IIF statement will display "ASSOCIATE" in the field.

So what I want to do is 'future-proof' this database so that new EmployeeTypes can be added, and the display on the card customized for that EmployeeType.

Let's look at my original example, but this time with the new table names and field names as spelled out above:

TABLE: tblTimeCards

EmpName     AssignedFloor     JobType     HeightWeight     Fetch
--------------------------------------------------------------------------
Fred Jones          1          Sales          603.195       JobType
Bob Smith           2          Manager        511.180       AssignedFloor
Jan Williams        1          Accounting     508.130       JobType

So here we have THREE records, one for Fred, one for Bob, and one for Jan. On the report (which prints on a card), I want to have TWO fields, one on top of the other.

The TOP field on the report (the card) CAN have an IIF statement. I will set this field's ControlSource to IIF([EmployeeType]="Accounting", "ACME PAPER COMPANY", [EmpName]), which will display the EmpName, except for Jan's Card, which will display the company's name. Juuuuuuust examples.

The BOTTOM field is where I want this problem solved. What I want the BOTTOM field to display is the column VALUE that has a column NAME that matches the Fetch column's value for that CardType. So, in the above example, if I were to print Fred's card, I want that second line to display the value in the JobType field, which is "SALES", but if I print Bob's card, I want the second line to simply show the number "2" there, because the Value of the FETCH column for Bob's card is AssignedFloor, and the VALUE of the AssignedFloor column is "2". With me so far?

What I REFUSE to do is put a huge IIF statement in that second field's control source that references what to do with any of the available JobTypes, because in the example above, I listed possible JobTypes as "SALES", "MANAGER" and "ACCOUNTING". What I did NOT list was "DANCER" because that job type doesn't exist. So my IIF statement has no instructions on what to do if the JobType is "DANCER".

In the future, if my boss wants to ADD this "DANCER" JobType, I don't want to force him to hire an Access programmer to edit the IIF statement in that report field's ControlSource. Instead, what I'd like for him to do is add DANCER as a new EmployeeType, and set the FETCH to "HeightWeight", which will automatically tell the report to fetch the value of the HeightWeight column and display THAT in the second field.

My question is this: How do I write the ControlSource for that second field on the report? plox's example tells me how to code a DLookUp that retrieves the VALUE of the FETCH column, but that's not what I want. I want to fetch the VALUE of the column where the NAME of the column matches the VALUE of the FETCH column.

So if the VALUE of the FETCH for record #1 is "EyeColor", that means I have a COLUMN that is NAMED "EyeColor", and in that COLUMN is a value "Brown". On the report, I want it to display "Brown" in that second field. On ANOTHER ROW (Record#2), I have the FETCH set to "ShoeSize" instead of "EyeColor". In my table, I also have a COLUMN that is NAMED "ShoeSize", and for record #2, the ShoeSize column's value is "14M".

On my card, the field in question's ControlSource needs to fetch the VALUE from the COLUMN that is NAMED after the value of the FETCH column for that record. Record #1's FETCH column is EyeColor, so I want the card to display the VALUE of the column named EyeColor, which is "Brown". But for Record #2, the value of the FETCH column is "ShoeSize", so I want it to display the VALUE of the column named "ShoeSize", which is "14M"

I need to know how to write that ControlSource. I've been working on this for hours, and can't figure out the syntax. I don't know how to code a variable into the name of the field that I'm looking up.

I hope that makes more sense. And that it wasn't too painfully long to read. I have attached an overly simple database example to this post in the hopes that somebody can supply the value for that Display field's ControlSource. Thanks again, all!

 

[JHB]

Here is an idea how you can do it, look at the code in the OnCurrent event for the main form.

 

[GrandMasterTuck]

EDIT:

Thanks, JHB, your method works great on this Single Form for displaying the value I need in that one field on that same Single Form. Now for my next wrinkle:

Instead of a Single Form, let's say the form is a Continuous Form that lists all records in that table. How do I modify your code to work for each record in the continuous form? I've attached the new database with the updated form (it's now a continuous form instead of a single form).

I realize that this code could exponentially slow the speed of my app, and I'm okay with that, because in the ACTUAL APP I built, the 'continuous form' isn't based on a table (with hundreds of records), but a query (with no more than ten or so records).

Any ideas on that one?

 

[JHB]

You can't do that in a Continuous Form, because of your unbound control, (it will always show the same value for all records).
So instead you need to base your form's recordsource on a query which include an extra field, and put the value in that field using a module when the query execute/run.
Database attached.
Oversimplifying things are okay, but if it always has been supposed to be based on a Continuous Form you should have mentioned it because it requires a completely different solution.

 

[GrandMasterTuck]

I get Run-Time Error '3061': Too few parameters. Expected 1.

I created a new module with this function:

Function FetchValue(CardID As Long, CardTopLine As String) As String
Dim dbs As DAO.Database, rst As DAO.Recordset
  
  If Not IsNull(CardID) Then
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("Select [" & CardTopLine & "] From tblPersonnelJobTypes Where [CardID]=" & CardID)
    FetchValue = rst.Fields(0)
  Else
    FetchValue = ""
  End If

And in my Query, I have a new column added with this in the control source:

TheCardTopLine: FetchValue([CardID],[CardTopLine])

tblPersonnelJobTypes is the table that contains that "Fetch" column, which is called "CardTopLine." In that CardTopLine column is the NAME of the column who's data I want to retrieve and display. The Query, I assume, should be fetching the value of the field name I indicate here and showing it to me in that TheCardTopLine column. But it's not doing that, I get an error message.

Is there something I'm missing?

As for the confusion with the single form vs. continuous form, I apologize for that. Again, I was hoping to 'adapt' the solution presented to me for use someplace else, and didn't want to have to type the giant wall of text (which I ended up typing anyway). I'll do better next time.

EDIT: OMG IT WORKS! Without going into details (unless somebody needs specific details on what happened here), I had the table/column name references screwed up. I was referencing the wrong column in my Function, and once I fixed it, JHB's latest solution did EXACTLY what it was supposed to do. THANKS AGAIN, JHB!

EDIT AGAIN: Nope, still not working. I modified the query, got it working, saved it, closed it, closed the database, reopened the database, and now I get that 3061 Run-Time error again. Too Few Parameters. Expected 1.

What. The. Aych-EE-Double-Hockey-Sticks.

What I did was implement JHB's solution by adding the function and adding that column to the query, and when I opened the query, it showed me the data I was looking for in those columns. Then I set my continuous form to open that query when double-clicking the CardID field, and it worked. Then closed the db (so I could do a quick copy-paste backup on my desktop in case I mess it up and have to go back), reopened the db, opened that same continuous form, double clicked the CardID field, and bam, run-time error.

This is the line that gets highlighted (no more simple examples, just exactly the way it is in my db):

Set rst = dbs.OpenRecordset("Select [" & CardTopLine & "] From qryPersonnelFetchCards Where [CardID]=" & CardID)

I don't get it! Why would it work, then suddenly not work? I didn't change anything!

EDIT AGAAAAAAAIN! It works if I open the query from the Access side menu, but not if the double-click action opens the query!?!? Why would opening the query programmatically not work, but opening it manually does work? WTF!

FINAL EDIT: Okay, got it working again. Apparently this code dislikes it when the fields it's referencing are from tables attached to other tables in the query. I had to literally create a second query and import all the fields by using the first query as the record source for the second query. That fixed it. No idea why. Anyway, thanks to everyone for helping with this. What a pain!

 

[JHB]

Good you found out it finally, good luck!