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?

Answer 1

Have you tried setting the RecordSource property on the form (from code) with:

Form.RecordSource = "SELECT ... FROM ... WHERE [Occurrence Date] BETWEEN " & Text1 & " AND " & Text2

Answer 2

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).