Using one-to-one relationship

Anonymous
2011-02-07T01:32:55+00:00

For various reasons, I need to break up a large table into smaller tables.  I've set up the smaller tables with one-to-one relationships.

If I assign a form's record source using just one of the smaller tables (and its fields), the form opens correctly showing the first record.  And, I can move through all the records using the Access record controls.

If I add one or more of the smaller tables (which participate in the one-to-one relationship with the original table), the form opens but no records are available - i.e., it opens as if I asked it to display a new record, and it doesn't recognize that there are other records available.

The one-to-one relationships are correctly set, with each table's primary key being a single key.

I have form controls that need to be bound to several of the one-to-one tables, so several tables need to be the form's record source.

As an example:  Table A has an autonumber primary key field, AID, and another field, Aname.  Table B has a primary key field BID (numeric long integer type) joined to AID in a one-to-one relationship (with referential integrity), and another field, Baddress.

If Table A is a recordsource for a form, the value of field Aname appears on the form and I can move through all Table A records.  As soon as I add Table B (along with Table A) as a recordsource, the form opens with no records available (i.e., a blank form as if I told it to go to a new reocrd).

I realize I can resolve this through a single large table, but that's what I'm trying to avoid. 

Has anyone worked with one-to-one relationships enough to have run into this problem?  And come up with a solution?

Thanks for any help.

Mike

Microsoft 365 and Office | Access | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
ScottGem 68,840 Reputation points Volunteer Moderator
2011-02-10T19:13:03+00:00

Mike,

I'm going to agree with Roger yet again. Helping you has been a pleasure and we will be here to support you as your project continues.

The main difference between bound and unbound forms is that, with a bound form, Access handles all the I/O between the form and the table. With an unbound form, the user has to write code (VBA), to handle the I/O. The complexity of that code can vary widely depending on the usage and purpose of a form. For example, a form solely for entering new records is fairly easy to code. But a form for retrieving data and populating the unbound controls, can be very complex. The coding involved depends on the table structure and form layout. Personally, I'm lazy and tend to use bound forms wherever I can.

There is only ONE circumstance in Access where a child record can be created with an automatically populated FK. And that's when using a subform. When you correctly link a subform to the main (parent) form, then any records created in the subform have the FK automatically populated. This is one of the reasons I said that subforms are a very powerful feature. But this only works with bound forms. So, in every other circumstance, when you add a record to a child table, you have to make sure the FK is populated correctly.

Hope this helps, Scott<> P.S. Please post a response to let us know whether our answer helped or not. Microsoft Access MVP 2010 Blog: http://scottgem.wordpress.com Author: Microsoft Office Access 2007 VBA Technical Editor for: Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports and Queries

Was this answer helpful?

1 person found this answer helpful.
0 comments
Answer accepted by question author
Anonymous
2011-02-10T18:15:24+00:00

Mike,

It's posts like this that makes the whole process of answering questions here worth while.  I think we've seen a lightbulb go off above your head <grin>

As you no doubt realize, there are two distinct knowledge sets necessary for database development: design and implementation.  The Design set is universal and can be applied to any DBMS implementation.  The Implementation set is specific to the DBMS.

For Design knowledge, I recommend Michael Hernandez' "Database Design for Mere Mortals".  I taught 12 years of college courses in db design from that book.  It was a revelation to me.

For Access Implementation knowledge, I recommend "Access Cookbook" by Ken Getz, Paul Litwin, and Andy Baron.  I learn best from examples and this book is a set of common problems and solutions in Access development.  It's not really a book that you read through.  It's best used to find how to solve a specific problem.

I created the samples on my website (www.rogersaccesslibrary.com) with the same idea in mind. 

Good luck!

-- Roger Carlson

MS Access MVP 2006-2010

www.rogersaccesslibrary.com

If you want a detailed answer, ask a detailed question!

Was this answer helpful?

1 person found this answer helpful.
0 comments

44 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2011-02-13T16:24:46+00:00

    I corrected a mistake in the above code, underlined below:

    Private Sub Form_AfterInsert()

        Dim strSQL As String

        strSQL = "INSERT INTO tblProjectMilestones (MilestoneTypeID, ProjectID) " & _

         "SELECT MilestoneTypeID, " & Me.ProjectID & " AS ProjectID FROM tluMilestones " & _

         "WHERE MilestoneTypePhase =1;"

        CurrentDb.Execute strSQL, dbFailOnError

    End Sub

    With that correction, I get the following error:

    RunTime error "3314':  You must enter a value in the 'tblMilestones.ProjectMilestoneDate' field.

    Since, as you said, "This would add a record for each Phase 1 milestone so the user would only have to enter the dates." I don't know where to go from here.

    Can you help me out Scott?

    Mike

    Was this answer helpful?

    0 comments
  2. Anonymous
    2011-02-12T16:50:07+00:00

    It will work on a bound form, though I can't think of many situations where it would be an appropriate interface.  In a situation where the relationship between two tables is one-to-many it would be pointless, and very confusing to the user.  A subform is the obvious way.  There could conceivably be a situation where a referenced table represents a type, and a referencing table it's one and only sub-type, where new type and sub-type rows must be entered simultaneously, in which case a form bound to a query joining the tables would work well, but that assumes there will be entities which have only the shared attributes, and no others not shared by the single sub-type.  In Date's example this would mean that all non-programmer employees have no attributes which programmers can't also have, which is possible but unlikely I'd imagine.  In this case a form specifically for entering programmers via the query might be appropriate.  Normally however, I'd expect other sub-types of employees, each with its own distinct non-shared attributes, which would mean having a separate query and form to enter each, which would be cumbersome. A form with subforms (one per subtype) shown/hidden in the parent form's Current event procedure as appropriate to the class of employee would be a better solution to my mind.

    Ken Sheridan, Stafford, England

    Was this answer helpful?

    0 comments
  3. Anonymous
    2011-02-11T21:26:24+00:00

    I'm still not successful in getting this code to work.  Here's how it's entered in the parent form of my test db:

    Private Sub Form_AfterInsert()

        Dim strSQL As String

        strSQL = "INSERT INTO tblProjectMilestones (MilestoneTypeID, ProjectID) " & _

         "SELECT MilestoneTypeID, " & Me.ProjectID & " AS ProjectID FROM tluMilestones " & _

         "WHERE Phase =1;"

        CurrentDb.Execute strSQL, dbFailOnError

    End Sub

    The only difference from the code on this thread is that "CurrentDB" becomes "CurrentDb" (as above) after VBA parses the line.

    The main (parent) form only has two controls:  one to display the ProjectID and another to enter the ProjectName.  When you start to enter ProjectName, ProjectID's value is assigned and displayed correctly.  After leaving the ProjectName field (i.e., when the AfterInsert event fires) I get an error message:

    "Run Time error 3061.  Too few parameters.  Expected 1."

    Debug takes me to the code with the line "CurrentDb.Execute strSQL, dbFailOnError" highlighted.

    The only tables in this test DB:

    Project:   ProjectID         AutoNumber

                  ProjectName    Text

    tluMilestones:    MilestoneTypeID       AutoNumber

                            MilestoneType          Text

                            MilestoneTypePhase  Number

    tblProjectMilestones:    ProjectMilestoneID       AutoNumber

                                     ProjectID                    Number

                                     MilestoneTypeID          Number

                                     ProjectMilestoneDate    Date/Time

    tluMilestones contains several records with various MilestoneType's and MilestoneTypePhase's.  The other two tables contain no records.

    I think I've faithfully modeled the tables you described above.  The parent/child forms are linked via ProjectID.  The three tables' relationships are correctly set.

    Obviously, I'm not smart enough yet to know if the SQL statement is correct for its intended purpose.  But that seems to be the only obvious source of the error.

    Sorry to keep coming back on this.  But, if I can get this working, it solves the vast majority of issues I have for this project.

    Thanks.

    Mike

    Was this answer helpful?

    0 comments