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.

How about updating the sql for the query in VBA so you can sent the value rather than the reference? — Fionnuala, Feb 08 '11 at 22:04
This also leads me to wonder why you have not simply linked the table. — Fionnuala, Feb 08 '11 at 22:10
Tables are linked. Can you give me an example of how I might update the SQL for the query in VBA so I can send a value rather than reference? — Christopher Robinson, Feb 09 '11 at 01:26

score 3 · Accepted Answer · answered Feb 10 '11 at 21:29

3

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

answered Feb 10 '11 at 21:29

mwolfe02

23,787
9
91
161

Great, I think I'm finally getting it, thank you all. Do I have to use DAO or ADO? Does that have to be explicitly given in VBA? – Christopher Robinson Feb 10 '11 at 22:13
If you are working with Jet, you should stick with DAO. MS has gone back and forth on which one it sets as the default when creating new .mdb's. You *can* use both in the same project, but there is *a lot* of overlap between the two libraries in terms of objects and methods. If you have both set as references, then their order within the list of references becomes important. If you want to be explicit in your code you can fully qualify any references to methods or objects, as I've done in my sample code: `qdf As DAO.QueryDef`. – mwolfe02 Feb 10 '11 at 22:27
Wow. Years later this post saved my bacon. I was tearing my hair out trying to figure out a way to use a Table UDF as a record source for an Access report. This answer works really well. – Rocky Aug 21 '17 at 21:05

score 3 · Answer 2 · edited Apr 23 '12 at 16:59

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.

Fionnuala · Answer 3 · 2011-02-10T21:39:40.227

1

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"

edited Feb 10 '11 at 21:39

answered Feb 10 '11 at 21:19

Fionnuala

90,370
7
114
152

do you think there is no performance penalty when using the first option ? Specially if the backend is non MS, say DB2 or Oracle... – iDevlop Feb 10 '11 at 21:30
1

As I understand it, which is not saying a lot, there should not be much difference in running a moderately simple, sargable query in Access. If there is fancy stuff or table scans, a pass-through is probably a good idea. – Fionnuala Feb 10 '11 at 21:34
Thank you. Because I am trying to access the back-end directly via pass-through query, I cannot directly edit the query with reference to a form control in the SQL area. I know that I need to use VBA. Where would I put the VBA code you mention above? – Christopher Robinson Feb 10 '11 at 21:36
I have added some notes, but where you put the code depends on how your application works. Suitable commonly used events include After Update events for controls and the Current event for the form. – Fionnuala Feb 10 '11 at 21:41
I'm not sure I see an advantage to changing the WHERE clause of a passthrough over using the passthrough like a linked table and using a value derived from a control in criteria using the passthrough. – David-W-Fenton Mar 10 '11 at 01:25

score 0 · Answer 4 · edited Dec 11 '11 at 15:06

0

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

edited Dec 11 '11 at 15:06

Shef

44,808
15
79
90

answered Mar 09 '11 at 21:40

JRod

1

1

Isn't this what I suggested in my answer? Shouldn't you choose it as the correct answer? Or did you do something different than what I suggested? – David-W-Fenton Mar 10 '11 at 01:27

MS Access pass through dependent on form value

4 Answers4

Linked

Related