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?
IrogSintaConnect With a Mentor Commented:
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

Jeffrey CoachmanConnect With a Mentor MIS 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...

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.

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to and use offer code ‘EXPERTS’ to get 10% off your first purchase.

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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.