Creating a Form from two Tables

[randolphoralph]

I am trying to create a form with fields from two tables.

The Table Names are HCF and IRR.

HCF has the following fields:
Ref Number (Primary Key)-Relationship set
Date
Comment

IRR has the following fields:
Ref Number (Primary Key)-Relationship set
Phone
Address

The problem that I am having is that there are 2 records in the HCF table and when I open the IRR table it is not showing the Ref Number for those 2 records.

I have tried to create a form that includes Ref. Number (from HCF table) Date, Comment, Phone, and Address. When I create the form it pulls up the form and it is blank with no fields on it. What am I doing wrong?

 

[Banana]

Are those 1-1 relationship?

Any particular reason why they can't be in same table? Or are they really supposed to be one-many?

 

[missinglinq]

Also you show

Ref Number (Primary Key)-Relationship set

for both tables. If there's a "relationship set" Ref Number cannot be designated as the PK of both tables; it has to be the PK of one table and the Foreign Key of the other table.

 

[randolphoralph]

They are 1-1.

How do I set PK of one table and the Foreign Key of the other table?

They do have to be in the same table if at all possible

 

[Banana]

They are 1-1.
...
They do have to be in the same table if at all possible

Actually, you only need one table if this is in fact 1-1 (e.g. there will be exactly one HCF for each IRR and never more); just move the fields from one table to another, delete that table, then just use that table with all fields in it.

OTOH, if this is supposed to be one-many (e.g. there can be many HCF to one IRR), then you need to add another row to make it a foreign key. Assuming HCF is supposed to be a many side table, it'd look like this:

HCFID <- This is PK Autonumber
IRRID <- This is just a long integer

You then set relationship from IRR table's IRRID (the PK autonumber) to the HCF table's IRRID (the long integer). Access will then recognize this as a one-many relationship and consider the HCF table's IRRID to be a foreign key.

HTH.

 

[boblarson]

I agree, if it is all going to be one to one then put everything in the same table. I would rarely (there may be occasions) have tables for a one to one relationship.

 

[heman85]

Try a QUERY

Did you try creating a QUERY for Both tables?

Then Create a Form From That QUERY...

 

[Chezhian]

See this. Hope it helps you.

 

[Jared]

I am grateful to find this thread, because I am having the exact same problem with my Access 2003 database.

I changed my form's control source to a query containing my main table and related tables, but when I do that, the form appears blank, presumably because of the 1-to-1 relationship glitch referred to below.

I changed the control source back to the primary table, and am now trying to fix this by manually placing controls that link back to the related tables, but when I do that, I get error messages saying it is invalid because it doesn't appear on the field list or that it can't be edited because it's "bound to the expression 'tblOtherTable![Field]'."

Is there a way to manually create controls that fill fields not on the field list? If not, is there any way, short of using subforms all over the place, to have fields from tables in a 1-to-1 relationship appear on the same form?

 

[Banana]

I want to verify something first-

Why do you have a 1-1 relationship? Most of time, if there is a 1-1 relationship, then the tables should be combined into one table.

 

[Jared]

I broke the tables up to follow data normalization rules. I have a lot of contingent data, and so didn't want to waste space with needless blank fields.

For example, there are different sets of data that would be filled in for each employee based on what role they perform. I didn't want to simply fill the main table with every optional field, so the primary field contains the most basic information common to all employees, and information necessary for the different roles is recorded in fields in related 1-to-1 tables as needed by the Personnel ID number.

 

[Jared]

I have now corrected the 1-to-1 relationship within a query problem...partially.

I changed the joins in the query to be left outer joins stemming out from my "primary" table, and the form now populates. It will let me view all records, but when I attempt to change any information, I am informed "Form is read-only."

I have checked through the form properties, and while I may be missing something, I cannot see what property might be relevant to fixing this. Can anyone help?

 

[Banana]

I have had similar problem regarding different questions for different roles, and I used to use 1-1 tables, but later found it inadequate in modeling the relationship.

I've since changed the relationship to a one-many... Something like this for your case, perhaps:

tblEmployees <--List all employees

tblRoles <-- List all available roles

tblAttributes <-- List all attributes we need to know about a given role

jctRoleAttribute <-- Junction table relationg attributes with roles

jctEmployeeAttribute <-- Three way junction table involving employee and their roles then their "answers" for attributes.

If you want to know the details and whys, do a forum search for "three legged tables", you should find that thread that The_Doc_Man and I participated in discussing this problem.

HTH.

 

[Banana]

I have now corrected the 1-to-1 relationship within a query problem...partially.

I changed the joins in the query to be left outer joins stemming out from my "primary" table, and the form now populates. It will let me view all records, but when I attempt to change any information, I am informed "Form is read-only."

I have checked through the form properties, and while I may be missing something, I cannot see what property might be relevant to fixing this. Can anyone help?

Just to answer the question directly as the normalization issue may take a while to sift through:

I've found that querying one-one table to be problematic. I can't remember what I did, but it is possible if you set it up correctly. For example, don't use primary key from parent table, but rather foreign key from child table. You should then be able to insert in new row. This requires experimenting (you don't need to use form for this; just toggle between query design view and datasheet view and see if you can update the query. If you can do it, then it's usable in form.

Even so, I found it to be more hassle & headache so a simpler way would be to just use a subform, which should still work for 1-1 relationship. You could then dynamically change the recordsource of the subform to match the child tables.

HTH.

 

[Jared]

Unfortunately, I don't think I have the expertise or experience yet to fully understand the suggestions you're making, Banana...though I am very appreciative of the willingness to help.

I know I can use subforms, and have done so in the past. I just figured there would be a way to manually insert a control, since it seemed a pretty basic functionality...ah, well. Subforms it is, then.

 

[Banana]

I understand the desire to just have one form, and this should be doable, given the 1-1 relationship, but this doesn't seem to be the case. In my case, I just hide the subform so it look as if there's just one form and my user doesn't have any idea there's a subform, and in the process, significantly simplify my work because I can just let Access take care of it.

Hopefully it will come together for you.

 

[Jared]

That was at least one of the reasons I was hesitant about using subforms in some cases. How do I go about having the fields from a subform be visible and editable with the subform they're in be invisible? I presume there's a property somewhere in here to adjust for that...

(I am, after all, all about letting Access do the work for me.)

 

[Banana]

It's two step process:

In the main form, you need to click on the subform container, then set those properties (in Format tab):

1) Special Efforts= None
2) Border = Transparent


Then in subform itself (click the gray box on upper right of the form then do the following:

1) Record Selector: No
2) Naviagtion Buttons: No
3) Border: None


There may be some more but can't remember off the top of head.

If you are not sure what I'm talking about "subform container" and "subform itself", take a look at Bob Larson's excellent illustration of the difference.

HTH.

 

[Jared]

Following those simple steps, plus eliminating the scroll bars on the subform, it's indistinquishable from any other bunch of fields in the form. Thank you very kindly!