Pass Through Query with Criteria from Form

[dansalmada]

Hi,

I have an .mde file that does the followingand since it is an mde I cannot see how it is done)

- A form asks for criteria (first you select a filter or no filter check button)
- The form takes the criteria and (I think) with a Module and a Macro adds a piece of code to a pass through query which runs
- A report is run with the pass through query and the criteria

I know the Macro adds criteria to the pass through query because I've seen that:

Example

Original Query is
Select parts.name, parts.line, cars.number from parts, cars where parts.name =cars.name and parts.line = cars.line

When the filter button is checked "Yes" and the criteria has been written by the user you press the macro button and it adds the piece of code to the pass through query. Let's say the form contains the Text Box "Line" and I write in it "Chevrolet". When I press the macro button the code in the pass through changes to

Select parts.name, parts.line, cars.number from parts, cars where parts.name =cars.name and parts.line = cars.line and parts.line='Chevrolet'

How is that done????

 

[pdx_man]

A query has many properties. One of them being .SQL, so this is updated once the button is pushed. Here is a function that I have that will automatically go through every Pass-Through query and change a parameter on the end to the current user's name.

Public Sub UpdateQryUsers()
Dim qdfLoop As QueryDef
Dim XStr As String
Dim Posit, Posit2 As Byte

    Set dbs = CurrentDb
        For Each qdfLoop In dbs.QueryDefs
            If qdfLoop.Connect <> "" Then
                If Right(qdfLoop.SQL, 1) = "'" Then
                    Posit = InStr(qdfLoop.SQL, "'") - 2
                    Posit2 = InStr(qdfLoop.SQL, ",")
                    If Posit2 > 0 Then
                        XStr = Mid(qdfLoop.SQL, Posit2, 50)
                    Else
                        XStr = ""
                    End If
                    qdfLoop.SQL = Left(qdfLoop.SQL, Posit) & " '" & fOSUserName & "'" & XStr
                End If
            End If
        Next qdfLoop
End Sub

This code runs with the database is opened. I have a function (from these forums) fOSUserName that gives me the NT User name of the person logged into the machine.
So where before it may have had:

EXEC proc_My_Stored_Proc 'Smith'

And then later Thompson logs into the database, it becomes:

EXEC proc_My_Stored_Proc 'Thompson'

My guess is your macro is doing something similar to this.