No List in Combo Box

[Prada82]

I have a query and then a form connected to it which opens up a report. The form has a combo box where the user is suppose to select a job and then open up a report. Now when I use a “like” statement…the list of jobs appear, but I don’t want similar titles to the one selected to show up. For example, I select “Mgr, Accounting” and Mgr, Accounting, Sr Mgr, Accounting shows up. I just want the one title, Mgr, Accounting. When I restrict the query to just select a job from the form using the builder, the list of jobs disappears!

So going from:
Like "*" & [Forms]![fGet 1 NEW Job Level Info]![getcodetitle2] & "*" (where the list of jobs show) to
[Forms]![fGet 1 NEW Job Level Info]![getcodetitle2] (the list is blank).

I can't seem to figure out what I am doing wrong! Please help!! Thank you :)

[HansV]

Welcome to Eileen's Lounge!

What a mysterious problem! Could you create a copy of the database, remove anything not relevant to the problem from the copy, as well as all proprietary/sensitive information, zip the copy and attach the zip file to a reply? The attachment can be up to 250 KB. Thanks in advance.

[Prada82]

Here you go! All three of the forms have the same problem so take your pick! Thanks!! I'm sure its just something silly...I probably have been starting at this for too long.zzzzzzzzzzzz

[HansV]

The problem is that you have set the Row Source of the combo box to a query (SQL statement, actually) that is based on the query that is filtered by that combo box. The Row Source should display the unfiltered list. The report should use the filtered query.

So you need two queries for each situation: an unfiltered query to act as row source for the combo box, and a filtered query to act as record source for the report.

In the attached version, I have done this for the first form/query/report. The others would work the same way.

Copy of 1.6.12.zip

[Prada82]

So do I need to make changes to report or just the form?

[HansV]

I made three changes:

1. I created a copy of the query 'qGet 1 Job Code Details' and renamed the copy to 'qGet 1 Job Code Details Filtered'.
2. I removed the criteria from the original query 'qGet 1 Job Code Details' so that it returns all codes.
3. I set the Record Source of the report 'rGet 1 Job Code Details' to 'qGet 1 Job Code Details Filtered'.

[Prada82]

Do I still need the "like" statement if I am just wanting to return the title that the user selected? Can I just word it like this: [Forms]![fGet 1 Job Code Details]![getoldcodetitle4]?

[HansV]

Sorry, forgot to mention that. In the "Filtered" version of the query, I removed the Like bit, so that the criteria indeed looks like [Forms]![fGet 1 Job Code Details]![getoldcodetitle4]. You can see this if you open the "Filtered" version of the query in design view.

[Prada82]

Awesome...it works! Thank you so much Hans...I heard about this site though an Access class I would taking. You were well recommended!

[HansV]

Thanks for your kind words - much appreciated!