passing parameter from access into sql-server

Question

REPHRASING QUESTION:

i apologize for being unclear. i have a textbox and a button a form. when the button is clicked it runs this saved access query:

select * from sqlservertable where field=form!textbox.value

i have an access front end and sql server back end.

this query is not working. it doesnt like me passing this parameter form!textbox.text. is there an alternate way to pass the value of the textbox?

You want to pass it as an access variable or as a SQL @variable? — JNK
@JNK, i just want to pass the text from the textbox into the sql statement — Alex Gordon
Could you rephrase your question? I don't understand your question. — Vidar Nordnes
@everyone: woudl this be the right solution? blueclaw-db.com/accessquerysql/parameter_query.htm — Alex Gordon
When you say "runs this saved query" what do you mean? Does the button call DoCmd.OpenQuery? If not, it's unclear what you mean, since SELECT queries don't do anything other than display data (i.e., they perform no actions). — David-W-Fenton

David-W-Fenton David-W-Fenton · Accepted Answer · 2010-10-21T04:20:28

The .Text property of an Access control is available only what that control has the focus.

Secondly, in Access, you refer to forms as members of the Forms collection, i.e., the collection of open forms.

So, you use:

  Forms!FormName!ControlName

Without specifying the Forms collection as the container object for the form, the Access expression service won't know where to find the control.

And as .Value is the default property of controls, you don't need to specify it, though if you want to be really picky and explicit and type more characters for no actual benefit in this context, you could use:

  Forms!FormName!ControlName.Value

But that won't behave any differently at all in this context (the only situation where it will is if you're trying to force evaluation of the control before passing it as a parameter of a subrountine, in which case without .Value you may end up passing a control reference instead of the value, which could be bad or it could be fine).

passing parameter from access into sql-server

2 Answers