Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2006
    Location
    New Kensington, PA
    Posts
    17

    Unanswered: Error: Opening form with filter with no matching records??

    (Access 2003) I have a company database form that calls a company contact form. Each company can have multiple contacts. By setting a filter on company name, when opening the contact form/table the filter returns an error if no company name/contact exists in the contact table. The relationship is set one to many. How can I test if company name exits in contact table and then apply the filter or else open the contact form to add a new record? All suggestions are appreciated!

  2. #2
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    On the company form, add a combobox whose row source is all contacts for the current company. Make sure the 'Limit To List' property is set to "Yes," and include some code in the "On Not In List" event to add a contact into the contact table. Open the contact form in the After Update event of the combobox.

    What this does is to open the contact form with a previous contact name, if it was in the list. If not, it creates the new name and brings up the form with that name.

    Hope this helps,

    Sam

  3. #3
    Join Date
    Oct 2006
    Location
    New Kensington, PA
    Posts
    17
    Thanks Sam!

    I also created a Macro that seems to work i.e.:

    SetWarnings
    ----(Warnings On = No)
    Find Record
    ----(Find What: [CoName] = forms!frmCoForm.[CoName])
    ----(Match: Any Part of Field)
    ----(Match Case: No)
    ----(Search: All)
    OpenForm
    ----(Form Name: frmContacts)
    ----(View: Form)
    ----(Where Condition: [CoName]<>forms!frmCoForm.[CoName])
    ----(Data Mode: Add)
    ----(Window Mode: Normal)
    OpenForm
    ----(Form Name: frmContacts)
    ----(View: Form)
    ----(Where Condition: [CoName]=forms!frmCoForm.[CoName])
    ---- (Data Mode: Edit)
    ----(Window Mode: Normal)
    SetValue
    ----(Item: [CoName]
    ----(Expression: forms!(Form Name: frmContacts)
    Last edited by LBorowiec; 12-04-06 at 17:24.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •