List Box Respond to Combo Box Selection

[mmchaley]

Hello!

I am using access 2010

I am trying to get my list box to filter based on the selection of a combo box.

My Combo box cboOrgRole is on my main form and is bound to OrgRoleID - on form - frmOrgEntry

The unbound list box lstRoleList is in a tabbed subform - frmPersonnel

This is my data pull for lstRoleList

SELECT tbl00PersonRole.PersonRole, tbl01Orgs.OrgRoleID
FROM tbl00PersonRole INNER JOIN tbl01Orgs ON tbl00PersonRole.OrgRoleID = tbl01Orgs.OrgRoleID
WHERE (((tbl01Orgs.OrgRoleID)=[Forms]![frmOrgEntry]![OrgRoleID]));

​

This is the code I have on cboOrgRole AfterUpdate

Private Sub cboOrgRole_AfterUpdate()
Me.frmPersonnel.lstRoleList.Requery
End Sub

I have also tried Recalc - both throw a Compile error: Method or data member not found

My goal is to be able to select the role of the organization, (General Contractor, Architect, Engineer, or Client) and have it list the available titles for the specific organization type.

My "00" tables are library tables

Thank you for your assistance,

Mark

 

[pbaldy]

You'd want the name of the combo in the SQL, not the field it's bound to.

 

[llkhoutx]

There's widely available code for identifying which row is selected in a list box. Have you searched for it?

 

[mmchaley]

I should have prefaced my post with my knowledge of programming comes from searching solutions on Google. When I see something I think works, I try it.

I changed OrgRoleID to cboOrgRole - it is still throwing the error on After_Update

 

[pbaldy]

Try


Me.frmPersonnel.Form.lstRoleList.Requery

 

[mmchaley]

I changed

Private Sub cboOrgRole_AfterUpdate()
Me.frmPersonnel.lstRoleList.Requery
End Sub

to

Private Sub cboOrgRole_AfterUpdate()
Me.frmPersonnel!lstRoleList.Requery
End Sub

That solved the error problem, but now the list box will only display the records corresponding to the first and last OrgRoleID records. All the in-between records are not showing up. I have confirmed the records exist.

Cheers,
Mark

 

[pbaldy]

The join in the query isn't eliminating them? Can you post the db here? Those are exactly the same by the way.

 

[mmchaley]

Here is the database.

I thought the . and ! functioned the same as well, but the . threw an error and the ! didn't.

Cheers,

Mark

 

[pbaldy]

I think the problem is the listbox SQL. If you take out the criteria, the only IDs returned are 4 and 12, so those are the only ones that would show any results in the listbox.

 

[mmchaley]

Thanks - that made me realize I was not calling from library table.

Cheers,
Mark

 

[pbaldy]

No problem Mark.

 

[mmchaley]

How do I set this thread as solved?

 

[pbaldy]

You can use the Edit Thread function under Thread Tools, and change the prefix.