Pass Through Query to SQL Server with Form Parameters

  • Thread starter Thread starter mattieflo
  • Start date Start date
M

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

Pendragon said:
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 said:
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.
Click to expand...
Click to expand...
 
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
 
Back
Top