Error: you have chosen forms that can't be linked........

Posted on 2009-04-28
Last Modified: 2013-11-28
I have an access database that I have several forms in. I am trying to create a main menu form that the user could click on to bring up the other forms. The main menu form was created with no table assigned. I have one button on the main menu form  that brings up the orders form to enter new orders. I am trying to create a button that would open a form I have based on a query. Everytime I used the wizard it will not let me do the "Open form and find specific data to display; I get the error
You have chosen forms that can't be linked.You must have at least one field that can be linked on each form.
Question by:BSAS_ITGuy
    LVL 11

    Expert Comment

    You will need to set up relationships between the source tables in the queries.
    Go to tools, relationships, add the tables and define the joins.
    You will need to make sure that a one-to-many relationship (Primary, Foreign Key) exists by checking enforce referential integrity.
    You can then create a form with subforms.

    Eg of Primary Foreign Key Relationship
    tblInvoice & tblInvoiceItems
    Primary  Key = tblInvoice.InvoiceID
    Foreign Key = tblInvoiceItem.InvoiceItemInvoiceID

    LVL 1

    Author Comment

    I do have the relationships established but when I go in to click on  "enforce referential integrity" I get the following message

    No unique index found for the referenced field of the primary table.
    LVL 11

    Expert Comment

    You will need to establish a primary key on the main table.
    Usually this is an auto number field.
    LVL 1

    Author Comment

    How do I do this after the table is already established, when I click properties it only gives me a box to type in the Table description.
    LVL 11

    Accepted Solution

    We are talking about design view on the table yes?
    a) If you do not already have an autonumber field in your table, you can add one.  Once the field is in place, right click it and select Primary Key
    b) If this is the case, you will need to add numeric/long fields in your related (or dimension) tables, and then update them to correspond to the appropriate record in the main (or fact) table.
    c) Under tools / relationships add the fact and dimension tables and join them (drag the foreign key field in dimension tables to the primary key field in the fact table) and enforce referential integrity (ie a reference in dimension table cannot occur until it exists in the fact table).

    Example of Primary / Foreign Key relationship

    tblSale (iSaleID is Primary Key in fact table)
    iSaleID, sSaleNo
    1          A1000
    2          A1001

    tblSaleItem (iSaleItem ID Is Primary Key, iSaleItemSaleID is foreign key in dimension table)
    iSaleItemID, iSaleItemSaleID, sDescription, nValue
    1                   1                         WidgetA       100
    2                   1                         WidgetB        110
    3                    2                         WidgetA        100
    4                    2                         WidgetC        150

    LVL 1

    Author Closing Comment

    That's it, thank you for the solution.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
    This isn't a frequent question on EE. I must have seen it three or four times (among several thousand questions). However, I use this trick quite often, most frequently as a delayed Current event. A form does not expose it's calculation dependenc…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now