I have been using this forum to help me build my database for months by reading what others post and it's awesome!!! But I am not sure how to even search for my latest issue...not sure if it's related to the query or the combo box.
In Access 2010, I have a combo box on a navagation subform called cmbSortFacility that is used to filter a query and run a report. Right now it returns a blank report. I think this is because the value it's filtering isn't directly related to the primary table but I don't know how to referrence it or where, in the combo properties, the field of the query?
The main table is tblWorkers it has a field called Series Code which has a relationship to tblSeriesCode!FacilityID which has a lookup to tblFacility!FacilityName
The unbound combo box Row Source is SELECT [tblFacility].[FacilityID], [tblFacility].[Facility Name] FROM tblFacility ORDER BY [Facility Name];
Works fine, it results in a list of the Facility Names to be selected. Then in the query field I have added
[tblFacility]![Facility Name] Like "*" & [Forms]![Main Menu]![SubSeriesSelect]![cmbSortFacility] & "*" Or [Forms]![Main Menu]![SubSeriesSelect]![cmbSortFacility] Is Null
As a side note, on another form I used the same expression and combo box set up but it filters off the Series Code which is a field in tblWorkers and it works great this one doesn't. Is that because now I am trying to filter from a value two tables away from the main? Any ideas? All the fields from those related tables are in the query so I don't know why it isn't pulling records from the selected field. Also, if I change the combo box Row Source to a field list the reports run just fine.
In Access 2010, I have a combo box on a navagation subform called cmbSortFacility that is used to filter a query and run a report. Right now it returns a blank report. I think this is because the value it's filtering isn't directly related to the primary table but I don't know how to referrence it or where, in the combo properties, the field of the query?
The main table is tblWorkers it has a field called Series Code which has a relationship to tblSeriesCode!FacilityID which has a lookup to tblFacility!FacilityName
The unbound combo box Row Source is SELECT [tblFacility].[FacilityID], [tblFacility].[Facility Name] FROM tblFacility ORDER BY [Facility Name];
Works fine, it results in a list of the Facility Names to be selected. Then in the query field I have added
[tblFacility]![Facility Name] Like "*" & [Forms]![Main Menu]![SubSeriesSelect]![cmbSortFacility] & "*" Or [Forms]![Main Menu]![SubSeriesSelect]![cmbSortFacility] Is Null
As a side note, on another form I used the same expression and combo box set up but it filters off the Series Code which is a field in tblWorkers and it works great this one doesn't. Is that because now I am trying to filter from a value two tables away from the main? Any ideas? All the fields from those related tables are in the query so I don't know why it isn't pulling records from the selected field. Also, if I change the combo box Row Source to a field list the reports run just fine.
Last edited:
