How to refer to field name dynamically using a variable in MS Access VB

Question

I'm making a search form for keyword searches. I have comboboxes with values ["And","Or","Not"], comboboxes with a list of field names, e.g. ["Title","Description",...] and textboxes associated with each combobox. I'm trying to create the sql query based on the values of the comboboxes and text box. To do that, I need to refer to the field name based on the value of the combobox. I'm trying solutions I've found searching, but I'm still getting the error: "Compile error: Sub or Function not defined". The debugger highlights "VHS_Metadata_Aug52014(field)" in yellow.

.....

bool_type = Me!Controls!dropBoolType2.value

field = Me!Controls!dropSearch2.value

value = Me!Controls!txtKeywords2.value

If (value) > 0 Then

    SQL_query_string = SQL_query_string & " " & bool_type & " " & VHS_Metadata_Aug52014(field) = " & value"

End If

.....

And what is VHS_Metadata_Aug52014? The syntax: VHS_Metadata_Aug52014() is that of a function. — mseifert
– mseifert, Commented Aug 6, 2014 at 20:34

Tyler Agostino · Accepted Answer · 2014-08-07 15:35:49Z

1

I'd use dot notation instead. Using bangs like that doesn't let you use variables very easily. Try something like

Forms("formname").controls(foo).value

Where foo is the value from the combobox.

I'm not sure what VHS_Metadata_Aug52014 is though, if it's the name of the form you were trying to reference, you were partly there.

answered Aug 7, 2014 at 15:35

Tyler Agostino

1161 silver badge5 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

abalter Over a year ago

Interestingly, I'm in the habit of using the ! because in the code editor in Access, when I type a "!", intellisense helps me find the control name, but not with ".". Makes no sense to me.

Collectives™ on Stack Overflow

How to refer to field name dynamically using a variable in MS Access VB

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related