none
DoCmd.OpenForm causes Enter a Parameter Value dialogue to appear when search in right table for a name RRS feed

  • Question

  • Hi,

    I am using Access 2013, I have 2 tables that are related 1 to many, Company(1) and Contact(many). I have created a form based on these 2 tables, Commercial Client Form, so that the user can see the data in the 2 tables in 1 form. I created another form, Commercial Client Search Form, & use a macro to open, allows the user the search for company or contact info in the 2 tables. They can search by company name, company type, town or contact name. Example, user want to find the address of a company and only know the name is CIBC & the town the company is in, they can search by those 2 criteria and the Commercial Client Form displays that fine. These types of searches work fine. 

    The Contact table contains all the contact names, phone numbers, email addresses, etc. for all contacts for a each company. Problem is when the user wants to search for a contact name...

    User opens the Commercial Client Search Form, enters the name they are searching for in the Contact Name field, they select search button, the Enter Parameter Value window opens asking for Contact.ContactName, enter the contact name again, the Commercial Client Form opens showing all records in both tables. Only the companies that have the contact name entered should be showing in the form.

    This is the VBA code I am using to open the form with SQL statement...

    strCriteria = strCriteria & "Contact.ContactName Like " & Chr(34) & Chr(42) & Me!ContactName.Text & Chr(42) & Chr(34)

    strSQL = "SELECT Contact.ContactName, * FROM Contact RIGHT JOIN Company ON Company.Company_ID = Contact.Company_ID " & "WHERE " & strCriteria & " ORDER BY [Company].[Company Name_ID] ASC;"

    DoCmd.OpenForm "Commercial Client Form", , strSQL

    This works fine when searching by fields in the Company table, just Enter Parameter Value dialog appears when searching for a name in the Contact table, ContactName field.

    In the Commercial Client Form, the subform which the source object is the Table.Contact, Link Master Fields is Company_ID and Link Child Fields is Company_ID.

    Company Table fields: Company_ID (Auto Number), Company Name_ID, Client Type_ID, Transit Number, Address, Town_ID, Province, Postal Codes.

    Contact Table Fields: Contact ID (Auto Number), Company_ID (Number), ContactName (Short Text), Phone Number, Fax Number, Email Address, etc.

    Any help as to why this is happening and how to get the search to work to display properly without the Enter Parameter dialog appearing would be appreciated. thanks.

    Friday, July 24, 2015 6:21 PM

Answers

  • From the sentence "In the Commercial Client Form, the subform which the source object is the Table.Contact, Link Master Fields is Company_ID and Link Child Fields is Company_ID", I suspect that the main form is based on the Company table only. As a consequence, the ContactName field is unknown in the main form - hence the parameter prompt.

    Try this instead:

    strCriteria = "Company_ID In (SELECT Company_ID FROM Contact WHERE ContactName Like " & Chr(34) & Chr(42) & Me!ContactName.Text & Chr(42) & Chr(34)
    DoCmd.OpenForm "Commercial Client Form", , , strCriteria ' Please note the extra comma


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Rivington2 Monday, July 27, 2015 3:48 PM
    Friday, July 24, 2015 7:06 PM

All replies

  • From the sentence "In the Commercial Client Form, the subform which the source object is the Table.Contact, Link Master Fields is Company_ID and Link Child Fields is Company_ID", I suspect that the main form is based on the Company table only. As a consequence, the ContactName field is unknown in the main form - hence the parameter prompt.

    Try this instead:

    strCriteria = "Company_ID In (SELECT Company_ID FROM Contact WHERE ContactName Like " & Chr(34) & Chr(42) & Me!ContactName.Text & Chr(42) & Chr(34)
    DoCmd.OpenForm "Commercial Client Form", , , strCriteria ' Please note the extra comma


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Rivington2 Monday, July 27, 2015 3:48 PM
    Friday, July 24, 2015 7:06 PM
  • You'll find an example in FindRecord.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    The option to search a  by a subform value in this little demo file does pretty much exactly what you are attempting other than the parent form is one of projects rather than companies.

    Ken Sheridan, Stafford, England

    Friday, July 24, 2015 10:34 PM
  • Parameter requests can be generated from several areas in Access and are annoying.

    Open all the underlying forms directly to confirm that they open without asking for parameters.  This is generally the fastest way to isolate what is generating the issue.  Systematically check all the underlying queries in the form to make sure they have not "lost" their field names as this will also cause a parameter check. 

    Parameter requests can come from many places including combo boxes and lists on the form and may include fields that are not displayed.   

    Unrelated but important the other item to check on the form, select design view, open properties see if the initial data set is coming in with filters applied as this also gums up the works.

    Good luck!

    Regards, Julie Bernhardt


    Julie Bernhardt Telecom Advocates, Inc.

    Saturday, July 25, 2015 3:48 PM
  • Thank you. This helped to fix the problem. New syntax to me, I had not know about.
    Monday, July 27, 2015 3:49 PM