MS Access pass through dependent on form value

Question

How do I assign a pass-through query to Row Source that is dependent on another value in the form?

Essentially I want to do this:

SELECT x.companyid, 
       x.companyname, 
       x.productid
  FROM x
 WHERE (((x.CompanyID) = [Forms]![Reporting]![CompanyID_Control]))
ORDER BY x.productid;

But of course pass-through queries do not support reference to any form controls.

I have read here that there is a method via VBA, however I do not know how to use VBA in conjunction with the Row Source of a control.

Answer 1

As Remou stated in his answer, linked tables will make this easier. However, if you have a pass-through query named MyQuery, you can do the following to make the RowSource of a MyComboOrListBox control update dynamically when the value of the CompanyID_Control changes:

Private Sub CompanyID_Control_AfterUpdate()
Dim SQL As String, qdf AS DAO.QueryDef
    Set qdf = CurrentDB.QueryDefs("MyQuery")
    qdf.SQL = " SELECT  x.companyid, x.companyname, x.productid " & _
              " FROM x " & _
              " WHERE x.CompanyID =" & Me.CompanyID_Control & _
              " ORDER BY x.productid;"
    Me.MyComboOrListBox.RowSource = "MyQuery"
End Sub

You'll also need to set the AfterUpdate property of the CompanyID_Control to:
[Event Procedure].

Note that even if you use linked tables as Remou suggested, you will still need code in the AfterUpdate event of the CompanyID_Control to refresh your combobox/listbox RowSource:

Private Sub CompanyID_Control_AfterUpdate()
    Me.MyComboOrListBox.Requery
End Sub

Answer 2

If a table is linked, you can just run a query against it as if it was an Access table, this includes referring to forms. So:

SELECT * FROM MyLinkedTable
WHERE ID = Forms!MyForm!MyID

Will work fine.

To permanently change the SQL of a query, you can use the SQL property of the QueryDef:

 Set qdf = CurrentDB.QueryDefs("MyQuery")
 qdf.SQL = "SELECT * FROM MyLinkedTable " & _
           "WHERE ID = " & Forms!MyForm!MyID  ''Or on MyForm, Me.MyID

You can also set a forms Record Source or the row source of a combo or listbox to an SQL string, with a linked table it is simple enough:

 Me.RecordSource = "SELECT * FROM MyLinkedTable " & _
           "WHERE ID = " & Forms!MyForm!MyID  ''Or on MyForm, Me.MyID

Or

 Me.MyCombo.RowSource = "SELECT * FROM MyLinkedTable " & _
           "WHERE ID = Forms!MyForm!MyID"

Answer 3

In some cases, you will get efficient results by writing a SQL SELECT using your passthrough query as the FROM source:

  SELECT MyPassthrough.*
  FROM MyPassthrough
  WHERE [criteria here]

This will very likely be just as efficient as editing the passthrough's QueryDef in code, and it's going to fail only when the passthrough doesn't return in its own SELECT the fields you need to filter on. Whether or not it's worth changing that depends on what you're using the passthrough for, and how complicated it is.

In general, I always avoid editing saved QueryDefs. Consider this: how often would you alter a SQL Server VIEW with DDL? Not often! In Access, it can lead to minor bloat (or not-so-minor, in some cases), and I always avoid anything that causes my front end to bloat, insofar as that's possible.

Answer 4

I just figured it out after tons of trying, this is an easy fix, create your passthrough with what ever you want to call in the form but leave it blank so it calls everything and then save the passthrough. Close that out and create a new query and add every column from the passthrouh in. Now in the criteria of the new query thats calling the passthrough add [Forms]![Reporting]![CompanyID_Control]) and just make sure the form is open, should run just as fast but now you can use your forms