none
Query on Access forms and tables RRS feed

  • Question

  • Hello, 

    Please can anyone help me how to solve this error message from my Access database. I have couple of table  for entering biological data, I have couple tables,the first is 'Clustertable' I have to enter the plot number, I did and in the second form those plot numbers have to automatically appear on the 'Sorting table' but every time I selected the plot number it gives me this error message.The current field must match the joint key 'Cluster' in the table that serves as the 'one' side of one -to- many relationship. Enter a record in the  one side table with the  desired key value, and then make the entry with the desired  join key in the 'many-only' table. 

    Thankyou

    Sunday, July 28, 2019 11:14 PM

All replies

  • The simplest interface for two tables in a one-to-many relationship type is a form, in single form view, based on the referenced table, ClusterTable in your case, and within it a subform in continuous forms or datasheet view based on the referencing table, SortingTable in your case.  Link the subform to the parent form by setting the LinkMasterFields and LinkChildFields properties of the subform control to the primary and foreign key in the relationship type, which appears to be Cluster in your case.  The correct foreign key value will be entered automatically in each row in the subform by virtue of the linking mechanism.

    An alternative interface would be to have two separate 'linked' forms, with the form bound to the referencing table opened in dialogue or synchronized mode.  You'll find examples of each in FormsDemo.zip in my public databases folder at:

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

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

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

    You can also correlate two subforms within a single parent form, which can be bound or unbound.  This allows both to be in continuous forms view.  For an example of this see CorrelatedSubs.zip in the same OneDrive folder.

    Ken Sheridan, Stafford, England

    Monday, July 29, 2019 9:08 AM