Pass Through Query to SQL Server with Form Parameters

[mattieflo]

Hello,

I'm trying to write a pass through query while constructing a where
statement off values of my form. I'm not even sure where to start. If I used
vba code and constructed the SQL statement that way, does anyone know what
kind of code I'd use to make a pass through query? Any examples on this
somewhere?

Thanks, I hope that wasn't too confusing.

 

[Pendragon]

How are you going to utilize the resulting query? That will be important on
where the final code is placed.

Here's a start:

Dim sSQL as string
Dim .... add your own variables here based on the inputs on your form

sSQL = "SELECT [choose your field results from the query] FROM [your source]
WHERE " <-- it's important to have the space before the quotes

YourVariable1 = "[result field] = " & me.[form input field 1]
YourVariable2 = "[result field] = " & me.[form input field 2]
etc etc

sSQL = sSQL & YourVariable1 & "AND " & YourVariable2 etc etc

Remember to put " AND " between each pair of variables.

Example:

sSQL = "SELECT CompanyName, FirstName, LastName FROM tblContacts WHERE "

MyCompanyID = "[CompanyID] = " & me.txtCompanyID
MyContactID = "[ContactID] = "& me.txtContactID

sSQL = sSQL & MyCompanyID & " AND " MyContactID

If you are trying to set up a form that allows users to input pieces of
information into your form fields, then you should do a search in this group
for "form searches" or check out the multitude of Access code samples
websites, such as Allen Browne's.

 

[mattieflo]

Oh sorry for not specifying what I wanted to do with the result. I wanted to
get the results from the pass through query and append it to the local tables
so that the user can work with the info.

How are you going to utilize the resulting query? That will be important on
where the final code is placed.

Here's a start:

Dim sSQL as string
Dim .... add your own variables here based on the inputs on your form

sSQL = "SELECT [choose your field results from the query] FROM [your source]
WHERE " <-- it's important to have the space before the quotes

YourVariable1 = "[result field] = " & me.[form input field 1]
YourVariable2 = "[result field] = " & me.[form input field 2]
etc etc

sSQL = sSQL & YourVariable1 & "AND " & YourVariable2 etc etc

Remember to put " AND " between each pair of variables.

Example:

sSQL = "SELECT CompanyName, FirstName, LastName FROM tblContacts WHERE "

MyCompanyID = "[CompanyID] = " & me.txtCompanyID
MyContactID = "[ContactID] = "& me.txtContactID

sSQL = sSQL & MyCompanyID & " AND " MyContactID

If you are trying to set up a form that allows users to input pieces of
information into your form fields, then you should do a search in this group
for "form searches" or check out the multitude of Access code samples
websites, such as Allen Browne's.

Hello,

I'm trying to write a pass through query while constructing a where
statement off values of my form. I'm not even sure where to start. If I used
vba code and constructed the SQL statement that way, does anyone know what
kind of code I'd use to make a pass through query? Any examples on this
somewhere?

Thanks, I hope that wasn't too confusing.

 

[Simmo76]

1st time user

Hello, this is my 1st time using the forum.

I am also attempting to pull parameters from an Access form to use in a SQL pass through query.

I have tried the following as the pass through query however I get an error on the at AS.

Dim sSQL as string
Dim MyAvailName as string
sSQL = "SELECT * FROM ibms_analysis.dbo.avail_type WHERE "
MyAvailName = "[avail_name] = " & me.forms!frm_Test!text0
sSQL = sSQL & MyAvailName

Any help would be great. Thanks