Combobox Filter not working on all selections

[Jerry Lutz]

I have created a form with a combobox to filter displayed records.



This combobox filter works on all of my 10 populated selection options , except 1. When I select any of the other 9 options available, the records filter correctly, when I select the 1 problem option from the 10 selectable items, I get no results being returned.

I am not sure if anyone else has experienced this issue - if you have could you possibly point me in the right direction to resolve.

[ranman256]

you didn't provide the code, or combo details.

[Bulzie]

Are you sure your recordset has one of those records that match the Combobox value? Also could be a datatype issue. As ranman256 suggested, can you provide examples.

[Micron]

I'm understanding that there is one combo with 10 items, not 10 combos that are cascading.

To me, it can only mean that for one particular list item, there are no results. Create a test query using the fields you're trying to filter and manually input a working value as criteria to validate this query works. Then input the value that doesn't work and see what happens. If it behaves the same, it is as I said.

Is the non-working value an option like "ALL" or NULL?

[Jerry Lutz]

Thanks everyone for your quick responses, I am new to this (Access) and not a programmer - so not sure I am going to provide what you need to assist.

There are master records in a separate table, which do contain multiple records with the search criteria - when I do a manual query the records are returned.
Out of the 10 combobox options (being generated by a second table that contains acceptable selectable options) - all return results except 1 of the selection items - which is what baffles me.

The SQL code being used on the combobox is below - and I am using the on change of the combobox to Me.Requery the form

SELECT DISTINCT Access_Funerals.Location
FROM (Access_Branches INNER JOIN Access_Funerals ON Access_Branches.BranchSelection = Access_Funerals.Location) INNER JOIN Access_ServiceOptions ON Access_Funerals.ID = Access_ServiceOptions.FuneralsID
ORDER BY Access_Funerals.Location;

[Micron]

You should use the AfterUpdate event, not that I think this is the cause. Your sql has no WHERE clause, so I don't get the connection with the combo. Might be faster if you copied your db, compacted the copy, zipped it and posted it here. See 'how to attach files' at the top of the forum window. You could remove sensitive info and anything else that's not required to fix this. If required, there is a utility here to randomize things like names for privacy protection.

EDIT - that sql is for the list, so it doesn't require a WHERE clause. So what's still not evident is where/how you're using the chosen value. I still think that it is an issue with the data, but you still haven't said if the problem value works in a query. This

when I do a manual query the records are returned

doesn't explain if you're using the problem value in a query designed for results. It seems like you're referring to a query that populates the combo list, and that's not relevant to the issue you've described.

[Jerry Lutz]

Hi Micron - I can not post the entire DB unfortunately, if you couldn't tell from the sql it is an application we are using at multi-branch funeral homes.
The query does work if done manually and returns records (when I do a manual query the records are returned.)

Here is what I am trying to do

I have created a Navigation Form
Assigned to First Button is "SelectBranch" Form
Within the "SelectBranch" Form - I have created a combobox which gets its acceptable values from a second table - this table holds the values of the funeral home branch name.
I have placed a report within the SelectBranch form which displays all current services for the selected combobox branch.
This report has a link attached to the name of the deceased which then opens up that individual record for editing.

The combobox is querying the branch table, and on the on change for the combobox, it is requering the form to display only the branch that is selected in the combobox. It works for all branches except one.. all selections work as expected and update the report with the selected branch (except the one)

Am I going about this the wrong way perhaps?

[Gicu]

What is the name\value of the branch that doesn't work? Does it have an apostrophe in it?
Cheers,

[Jerry Lutz]

No Apostrophe in the Name - it is called "Pending Pal Care" - I tried changing it to "Pending" and all the records in the main table to "Pending" still does not show.

[Jerry Lutz]

Hey Guys - I figured it out ... when creating the recordset - I removed unused tables - it now works for some reason.
Thanks for your time!