MS Access skipping depends on form value

Question

How do I assign a pass-through request to a Row Source, which depends on a different 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, end-to-end requests do not support a link to any form controls.

I read here that there is a way through VBA, however I do not know how to use VBA in combination with the Row Source control.

Answer 1

As Remu said in his answer, related tables will make this easier. However, if you have an end-to-end query with the name MyQuery, you can do the following to dynamically update the RowSource from MyComboOrListBoxwhen the value changes CompanyID_Control:

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

AfterUpdate CompanyID_Control:
[Event Procedure].

, Remou, AfterUpdate CompanyID_Control, combobox/listbox RowSource:

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

Answer 2

, SQL SELECT, - FROM:

  SELECT MyPassthrough.*
  FROM MyPassthrough
  WHERE [criteria here]

, , , QueryDef , , SELECT , . , , , , .

, QueryDefs. : SQL Server VIEW DDL? ! Access ( -, ), , , .

Answer 3

, , Access, . :

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

.

SQL , SQL QueryDef:

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

SQL, :

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

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

Answer 4

I just figured it out after subtle attempts, this is a simple fix, create your end-to-end circuit with what you want to call in the form, but leave it blank so that it will type everything and then save the transition. Close this and create a new query and add each column from passthrouh in. Now in the criteria of the new request, which causes the add [Forms]![Reporting]![CompanyID_Control]) to forward , and just make sure the form is open, it should work just as fast, but now you can use your forms