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. ScottGem 68,840 Reputation points Volunteer Moderator
    2011-02-14T13:12:34+00:00

    You don't need to replace the ORDER BY, just Add a WHERE clause.

    However, if you are going to put "five pairs of text/date controls, with the text control simply displaying the milestone type." then you will need to use an unbound form! When you run the Append query, it adds 5 SEPARATE records to the table. So to use pairs of textboxes means an unbound form. And, again, at your stage of knowledge, I don't recommend it.

    Instead you set the subform to continuous form mode so it displays all five records. You use a combobox as Ken suggested that displays the Phase description. You lock this combo or set Enable to No so the user can't edit the combo. You then have a textbox for entering the date.

    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?

    0 comments
  2. Anonymous
    2011-02-14T00:22:50+00:00

    Thanks Ken. This is all starting to make sense. A little more explanation: The phases are chronological periods of time. Each phase will have its own milestones as well as a number of evaluation criteria, to determine if the potential property is a good fit to purchase for environmental conservation. I intend to set up the evaluation criteria following the same model as the milestones. So, the main Project form will open a number of subforms (at the appropriate times) - one for each phase. The first phase is a site visit phase with about 5 milestones and numerous evaluation criteria. So, in the Phase 1 subform (which is parent/child linked via ProjectID), there will be (among other controls) 5 date fields corresponding to the five Phase 1 milestones. Thus, I want to be able to use your last approach (i.e., where the user enters a date for each of the milestones when they are complete). The Phase 1 subform's RecordSource should return the 5 Phase 1 milestone records for the project which were initially added through the main Project form's AfterInsert SQL statement (which is now working fine). On the PHase 1 subform, I'd put, as you suggested, five pairs of text/date controls, with the text control simply displaying the milestone type. To accomplish this, I think I'd need to replace the clause "ORDER BY MilestoneTypePhase" with "WHERE MilestoneTypePhase = 1". This should select the five milestone records for this project that are within Phase 1. Each record will have a unique MilestoneType value corresponding to things like "project initiation date" or "site visit date", which will populate the different text fields for the text/date pairs. So, when I set the ControlSource properties for the text/date pairs, I need to distinguish which pair goes with which MilestoneType value. And, that's where I'm uncertain how to proceed. For the text/date pairs, I'd want to bind them to MilestoneType and ProjectMilestoneDate, respectively. What is the best/efficient way to do that while selecting the correct milestone record? Do I use a query with a WHERE clause like WHERE MilestoneType = 1/2/3/4/5. Or, is there a way, through Expression Builder to choose, for instance, ProjectMilestoneDate and modify it to indicate which date (i.e., MilestoneType = x)? An example would be very helpful. I very much appreciate your help. Once I get this running, I'm going to go on a study binge to understand what's behind all the advice I've gotten in this thread. Mike

    Was this answer helpful?

    0 comments
  3. Anonymous
    2011-02-13T22:15:43+00:00

    Assuming the parent form is based on the Project table (or a query on that table) then you can link the subform to the parent form by setting the LinkMasterFields and LinkChildFields properties of the subform control (that's the control in the parent form which houses the subform) to ProjectID in each case.  This will restrict the subform to those rows which reference the current record in the parent form.

    For the subform's RecordSource, if you want to be able edit the MileStoneType use a query which sorts the rows by MilestoneTypePhase (I assume this indicates the order of the milestones)

    SELECT ProjectMilestoneID, ProjectID, tblProjectMilestones.MilestoneTypeID, ProjectMilestoneDate

    FROM tblProjectMilestones INNER JOIN tluMilestones

    ON tblProjectMilestones.MilestoneTypeID = tluMilestones.MilestoneTypeID

    ORDER BY MilestoneTypePhase;

    Include a combo box in the subform, with MilestoneTypeID as its ControlSource property, and a text box with ProjectMilestoneDate as its ControlSource property.  Set up the combo box as follows:

    RowSource:     SELECT MilestoneTypeID, MilestoneType  FROM tluMilestones ORDER BY MilestoneTypePhase;

    BoundColumn:   1

    ColumnCount:   2

    ColumnWidths:  0cm;8cm

    If your units of measurement are imperial rather than metric Access will automatically convert the last one.  The important thing is that the first dimension is zero to hide the first column and that the second is at least as wide as the combo box.

    If you don't want to be able to edit the MileStoneType, but merely enter the date then, as the subform's RecordSource, return the MilestoneType column in the query rather than the MilestoneTypeID:

    SELECT ProjectMilestoneID, ProjectID, MilestoneType, ProjectMilestoneDate

    FROM tblProjectMilestones INNER JOIN tluMilestones

    ON tblProjectMilestones.MilestoneTypeID = tluMilestones.MilestoneTypeID

    ORDER BY MilestoneTypePhase;

    In this scenario add two text boxes to the form with MilestoneType and ProjectMilestoneDate as their ControlSource properties.  Set the Enabled property of the MilestoneType text box to False (No) and its Locked property to True (Ye) to make it read only.  Link the subform to the parent form on ProjectID as above,

    Ken Sheridan, Stafford, England

    Was this answer helpful?

    0 comments