Access Database Search form Not Working

Hi Guys

As attached. In this database I am trying to create a search form Called "Copy of Application Summary by 1000" I had added a new field Called "Deal Work Flow" as soon as I select the options in it the search form doesn't work.

Any help will be appreciated.


Edit... Attachment removed due to privacy information contained therein.
Topic Advisor
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeffrey CoachmanMIS LiasonCommented:
When posting a sample database please follow these guidelines:

Sample database notes:
1. Back up your database(s).
2. Combine the front and back ends into one database file.
3. Remove any startup options, unless they are relevant to the issue.
4. Remove any records unless they are relevant to the issue.
5. Delete any objects that do not relate directly to the issue.
6. Remove any references to any "linked" files (files outside of the database, Images, OLE Files, ...etc)
7. Remove any references to any third party Active-x Controls (unless they are relevant to the issue)
8. Remove, obfuscate, encrypt, or otherwise disguise, any sensitive data.
9. Unhide any hidden database objects
10. Compile the code. (From the VBA code window, click: Debug-->Compile)
11. Run the compact/Repair utility.
12. Remove any Passwords and/or security.
13. If a form is involved in the issue, set the Modal and Popup properties to: No
    (Again, unless these properties are associated with the issue)
14. Post the explicit steps to replicate the issue.
15. Test the database before posting.

In other words, a database that we can easily open and immediately see and/or troubleshoot the issue.
Post the explicit steps on how to recreate the issue.
And if applicable, also include a clear graphical representation of the *Exact* results you are expecting, based on the sample data.

When I try to open the form, I get a parameter prompt... "A.SalesManager"
When I select a value from the combobox, I get that same prompt again, followed by: "WorkFlowStatus", it seems that Access cannot find these fields...

The bottom line is that the other comboboxes are working, so look to them for hints on why this one isn't.

Perhaps another expert  can dig deeper...

When I try to open the form, I get a parameter prompt... "A.SalesManager"
I concur with Jeff.  Removing SalesManager from the query source takes care of this error since this field doesn't seem to be needed for this form anyway.  As for the WorkFlowStatus error, first you need to revise your WorkFlows query this way:
SELECT Workflows.[Workflow ID], Workflows.WorkflowStatus
FROM Workflows
ORDER BY Workflows.WorkflowStatus;

Open in new window

Second, you need to use the field [Deal Workflow ID] instead in your procedure
filter = filter & IIf(Len(filter) = 0, "", " AND ") & "[Deal Workflow ID] = " & Me!Combo19

Open in new window

The above items should get it working; however this does not take care of the "ANY" option in your dropdown.  You need to revise your IF statement and make it similar to the one used by "Decision Status" cboStatusFilter (which also has "ANY" in its choices).  Another piece of advice, be sure to use a descriptive name instead of Combo19.  I'm not sure why you didn't change this at the onset when all your other controls are named properly.

One probable reason you ran into this problem is because you are using a Lookup Field in your table for the field [Deal Workflow ID].  In my opinion, Microsoft did a disservice to its consumers by adding this feature.  It causes more problems than it solves and doesn't allow the database designer to learn how to properly use Relational objects in a Relational Database.  My last piece of advice for you is to avoid using lookup fields in your tables.  Here's a link to some useful information that covers some of the problems associated with Lookup Fields.

The Evils of Lookup Fields in Tables


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
surah79Author Commented:
As attached a new copy with all the test information. In this database I am trying to create a search form Called " Application Summary by 1000" I had added a new field Called "Deal Work Flow" as soon as I select the options in it the search form doesn't work.

It should populate the form with critieria selected in the Deal Work Flow combo box
Any help will be appreciated.If I don't select anything then it should default to show all the records same as the When we select "ANY" in the  Decision Status box.

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

surah79Author Commented:
Hi Ron

It working now but its giving the ID instead of the Name there. Do I have to make a change in the table to get workflow status instead of workflow ID.

Just showing the difference
Thanks a lot
Did you remove the Lookup Field from the table?  The advice on Lookup Fields was not to fix your current problem, rather, it was to guide you in your future database designs.  Right now your table has quite a number of Lookup Fields so changing just one is of no value.  

Go ahead and put back your Lookup Field in this instance.  In the next database you attempt to create, try to learn first about relational database design.

surah79Author Commented:
No I didn't remove the lookup field from the table.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.