CODEWITHSUNDEEP
sqlms-accesscombobox
Tyler
Tyler

Reputation: 616

How to make subform display data according to combobox selection?

I'm trying to bind multiple tables together and have a subform display data based on my combobox (ID name = CbproductName) selection in a form named Form2. I'm using Ms office Access.

This is the query 

SELECT Employee.EmpName, Employee.EmpCode, Employee.CompanyID, Employee.DeptID,
       Employee.ComputerID, Software.ProductName 
  FROM Software
       INNER JOIN (
             (
              Computer INNER JOIN Employee ON Computer.CompID = Employee.ComputerID) 
              INNER JOIN Application ON Computer.ComputerName=Application.[A-ComputerID]
             )
       ON Software.ID = Application.SoftwareID 
 WHERE Application.SoftwareID = Form2.CbProductName;

However it keeps prompting me to enter the parameter value when i run the form, and it doesn't display the result of the entered value. it also doesn't change according to the combobox selection. Can someone point me at a direction to get it done?

Upvotes: 1

Views: 14060

Answers (1)

HansUp
HansUp

Reputation: 97131

In a query, reference a form by its name as a member of the Forms collection. And then reference the value of a control on that form using the control name. It should look like this pattern:

Forms!FormName!ControlName

In your query, change the Where clause to this:

WHERE Application.SoftwareID = Forms!Form2!CbProductName;

Then make sure to refresh that query in after update event of CbProductName. You indicated the query is used as the record source for a subform, so assuming the subform control is on the same form as the combo box, try this as the after update procedure:

Private Sub CbProductName_AfterUpdate()
    Me!SubformControlName.Form.Requery
End Sub

Beware, the subform control name may not be the same as the name of the form it contains. Make sure you use the name of the control.

Upvotes: 2

Related Questions