Passing Criteria from a Form to a View???

[RandyMyers]

I have been programming using Access MDB front ends connected to SQL Server utilizing ODBC for some time now. I have used popup forms for users to enter criteria for opening forms or reports on many occasions. I would use unbound fields on forms and then use the statement

Like "*" & [Forms]![frmFormName]![FieldName] & "*"

as the criteria in the query. Several fields could be passed in this manor and fields could be made optional.

I recently have started using Access Projects (adp files) and have not figured a way of doing this same operation in views of stored procedures.

If anyone has an idea on this I would appreciate the help.

Also, is there a real difference in performance using adp versus using mdb and ODBC connections. My boss wants to use adp because of Network Bandwidth, which he feels will be substantially less with adp

 

[cmmrfrds]

On performance, the adp will use less bandwidth since it sends the resultset back to the PC. A linked table sends the whole table back to be processed on the desktop.

If you are using a stored procedure as the record source of a Form, look under the data tab. There is a parameter property. Put your parameters there in the format.
@Parm1=whatever,@Parm2=somethingelse,etc....

This is not object oriented, but it works and I have never had a problem with it and that is to use public variables. Create the public variables in a standard module.

Public var1 as String
Public var2 as Date

Public Function ReturnVar1() as String
ReturnVar1 = var1
End Function

var1 = [Forms]![frmFormName]![FieldName]

Return the public variable with a function which can be used any place including an sql statement.

Dim sqlstr as String
Dim rs as New ADODB.RecordSet
Dim cn as New ADODB.Connection '- assume a connect to sql server.

var1 = "sam"
sqlstr = "Select * From tab1 where myname = " & chr(39) & ReturnVar1() & chr(39)

rs.Open sqlstr, cn

 

[RandyMyers]

I tried your solution. Good idea, but it still is not working.

Here is my statement:


Private Sub Form_Open(Cancel As Integer)

Dim dbs As New ADODB.Recordset
Dim rst As New ADODB.Connection
Dim sql As String

var1 = "[Forms]![frmWorkOrdersSearch]![cmbEOCID]"
sql = "Select * From WorkOrders Where EOCID= " & Chr(39) & ReturnVar1() & Chr(39) & ";"

dbs.Open sql, rst

End Sub


It kicks out with a debug error at the dbs.Open sql, rst statement (even if I replace the var1 with a hard code value.

Thanks for any help

 

[cmmrfrds]

What does your connection look like.

Debug.Print rst

I don't see how you setup the connection, can you provide that information. The naming is counter intutitive, usually something like rst for recordset and cn for connection.

 

[RandyMyers]

Thank you for your help. I am sure it is just me, but I can not figure out how to get the information you are requesting.

Where would I put the Debug.Print rst statement? How would I retrieve the results?

I am connecting to a SQL Server 2000 database using Windows Authentication. The account has full admin privilages.

Here is my revised code:

Private Sub Form_Open(Cancel As Integer)

Dim rst As New ADODB.Recordset
Dim cn As New ADODB.Connection
Dim sql As String

var1 = "[Forms]![frmWorkOrdersSearch]![cmbEOCID]"
sql = "Select * From WorkOrders Where EOCID= " & Chr(39) & ReturnVar1() & Chr(39) & ";"

rst.Open sql, cn

End Sub



Here is the module:

Option Compare Database

Public var1 As String
Public var2 As Date

Public Function ReturnVar1() As String
ReturnVar1 = var1
End Function

 

[cmmrfrds]

Dim rst As New ADODB.Recordset
Dim cn As New ADODB.Connection
Dim sql As String

'-- Need to set the connection, since the project (adp) is already connected to the server you want then you can use the current connection.

Set cn = CurrentProject.Connection

var1 = "[Forms]![frmWorkOrdersSearch]![cmbEOCID]"
sql = "Select * From WorkOrders Where EOCID= " & Chr(39) & ReturnVar1() & Chr(39) & ";"

rst.Open sql, cn

 

[RandyMyers]

Thank you very much. I will give that a try.