Pass a form field to a View

[flaviooooo]

Hey,

I'm not sure if this can be done, but here I go:

I have a form, where I created a few comboboxes, to do some selections. For example the company and a date can be selected here.

Now I want to run a query (a view), with these cboboxes as criteria.

How can this be done??

Thanks in advance for the help!

 

[danvlas]

strSQL = "Select * From YourViewName Where CompanyNameField = '" & Me.CompanyNameCombo & "' And DateField = '" & Format(Me.DateCombo,"mm/dd/yyyy") & "'"

Then you can use strSQL as a recordsource for a form or a report...

HTH



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[belovedcej]

In addition to Daniel's suggestion, you could replace the view with a stored procedure. The stored procedure would use @VariableName to indicate a variable of some kind. (I.E., set a field equal to @VariableName)

Then your report could be created using the stored procedure as your recordset.

In the "Input Parameters" property of the report, put the following:

@VariableName = Forms!FormName!txtBoxName

The question becomes whether or not you want to run this process on the server or on your local box.

 

[flaviooooo]

Thanks for the response guys.

Running the process on the server would be best off course (I guess)

Which option would do this?

 

[belovedcej]

using the stored procedure will have the SQL statement run on the server.

using VBA code and a recordset will run the SQL from the front end.

Both are viable solutions. If you will be dealing with large quantities of data, you probably prefer running on the server. This allows the statement to be precompiled and it runs much faster.

However, it may be that you want to reduce database traffic (don't know why - I've read other people's thoughts on this and they have their reasons. I have never needed to do so because I deal with tiny user groups.). If that is the case, and the amount of data being manipulated is small, putting the SQL statement on the front may make more sense.

 

[danvlas]

Both the view(or SQL statement) and stored procedure are server side processes. The store procedure has an execution plan and is much more programmable, but one cannot use it for further filtering (ie one cannot use "Select * From StoredProc")
An inline function is an alternative, but it has a fixed number of arguments, not suitable for dynamic filters

HTH.


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html