Get a text value from a form in Access using a VBA module

Question

I currently want to get a value from a form to set it in an SQL query I make in a module under Access using VBA. I tried to use

value = Forms![NameOfForm]![NameOfTextbox] 

sqlquery = "...." & value & "....."

It make an error (2450) saying it cannot found the specified form. How can I get the value of this textbox so I could use it in my module?

Thx

Answer 1

Modify your VBA code to ensure the form is open.

DoCmd.OpenForm "NameOfForm"

That should prevent error #2450.

Afterwards, you don't need to store the value of [NameOfTextbox] to a variable, then use that variable to build your SQL statement. You can use its value directly.

sqlquery = "SELECT * FROM YourTable " & _
"WHERE some_field = '" & Forms![NameOfForm]![NameOfTextbox] & "';"

Or embed a reference to the textbox itself (instead of the textbox's value) in the query.

sqlquery = "SELECT * FROM YourTable " & _
"WHERE some_field = Forms![NameOfForm]![NameOfTextbox];"

I assumed some_field is a text data type field, so enclosed the textbox value with single quotes in the first query example. Notice the second example doesn't need the quotes because it refers to the textbox by name rather than its value.

However, should you continue with your original approach (storing the textbox value to a variable), don't name your variable "value" because value can be confused with a property of many objects.