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.