如何通过拼接变量引用字段值？VBA字段引用问题求助

Got it, let's fix this! The issue here is that when you concatenate the string, you're just passing plain text to MsgBox instead of telling VBA to treat that string as a reference to your field. Here are two reliable solutions to get the actual field value:

1. Use the Eval Function #

The Eval function evaluates a string as an expression, which means it'll interpret your concatenated string as a field reference instead of plain text. Here's how to adjust your code:

Dim strCompany As String
strCompany = "Amazon"
' Eval will parse the string as a field reference and return its value
MsgBox(Eval("[" & strCompany & " Emp Nbr]"))

2. Use Me.Controls (For Form/Report Modules) #

If you're working within a form or report module, you can directly reference the field via the form's controls collection. This is often more efficient than Eval:

Dim strCompany As String
strCompany = "Amazon"
' Access the field/control value through the form's controls
MsgBox(Me.Controls("[" & strCompany & " Emp Nbr]").Value)

Why Your Original Code Didn't Work #

Your first example works because [Amazon Emp Nbr] is directly interpreted as a field reference by VBA. But when you concatenate strCompany into a string, you end up with a literal text string "[Amazon Emp Nbr]"—VBA has no idea you want it to look up a field with that name. Both solutions above tell VBA to treat that string as a reference instead of plain text.

内容的提问来源于stack exchange，提问作者SparkChaserJoe