asked on

MSAccess VBA - how to equate a table/query field to a user-supplied string

I have a table (TableA) that includes fields named Text1, Text2, Text3, Textn

I have a form "foo" bound to a different table (TableB) that has a text field named "Field Name".

I'm trying to figure out how to employ user input to the form (e.g., Forms!foo!Field Name = "Text1") to get the content of the correspondingly named field in table A, e.g., "Select TableA.[Forms!foo!Field Name] From TableA". which in this example would equate to "Select TableA.Text1 From TableA". I don't think this is a hard one, but it's eluding me.

Patrick Matthews

Without VBA, you could try Switch:

SELECT Switch(Forms![foo]![fieldname] = "Text1", Text1, Forms![foo]![fieldname] = "Text2", Text2, 
    Forms![foo]![fieldname] = "Text3", Text3, ..., 
    Forms![foo]![fieldname] = "TextN", TextN, True, "<Unknown field.  Panic!>") AS Result
FROM TableA

Open in new window

For more about Switch:
https://www.experts-exchange.com/Microsoft/Development/MS_Access/A_3556-Using-the-Switch-Function-in-Microsoft-Access.html

Patrick

dgmoore1

ASKER

I read your alternatives to switch in VBA with advice to use If...Then. The problem is that [Field Name] could contain any of 30+ values. If I have to execute an If...Then against all 30+ possible values for each of a thousand or more records I'm doing an awful lot of crunching. If the user has specified, say, Text 6 and Text10 in the form I'd like to be able to refer just to TableA.Text6 and TableA.Text10.

ASKER CERTIFIED SOLUTION

Patrick Matthews

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

dgmoore1

ASKER

That might do the trick - let me see what happens with that.

Thanks

dgmoore1

ASKER

DLookup is not generally my favorite solution, but in this scenario it works well and seems to be fast enough. I can process 1500 records looking for 10 fieldnames in about 10 seconds, which is acceptable for this app. Many thanks for the suggestion.