Subforms & 1 to 1 Relationships

[fastrunr]

Hello,

I have a subform that has a 1 to 1 relationship with the main form. Therefore, I want to enable the user to populate the subform with only one value.

However, enabling "allow additions" causes the subform to appear to allow more than one value in this field (even though the 1-1 relationship prevents the user from actually adding more than one). But disabling "allow additions" prevent the user from being able to populate (with a single value) the records that haven't yet been filled in.

So I'm screwed either way ; )

Is there any way around this? How do people typically deal with 1 to 1 relationships in subforms.

Any help greatly appreciated!

 

[traingamer]

I've dealt with 1-1 table relationships on tabs (rather than subforms), linking on the proper fields. The tabs can all have different data sources, if needed.

You could also put it on the SAME form as long as you adjust your data source to reflect a query rather than a table.

traingamer

 

[fastrunr]

Hmm, I want to link to another table, so the tabs option is probably my best bet if they will allow me to use a different data source.

I thought about putting it in the same form, but the purpose of the form is to update some underlying tables, so the query option won't work (at least I don't think so).

So I'll try using tabs. Thanks for your help!

 

[fastrunr]

How do you get a tab to recognize a second table as a data source? I tried inserting a combo box in a tab, but it gets confused when I try to link it to a table other than the primary control source.

Thanks again.

 

[PHV]

How do you get a tab to recognize a second table as a data source
Create a subform control in this tab.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[fastrunr]

Okay, but that brings me back same problem I was having when using subforms earlier. Is there any way to display one and only one record in a subform?

Or maybe I am misunderstanding your advice. I don't see any benefit from introducing the tab...

Thanks again.

 

[PHV]

For a 1:1 relation both tables have same Primary Key.
If the subform is properly linked to the mainform you can't create more than one record.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[fastrunr]

I think they're linked properly - you can't *actually* create more than one record. However, the appearance of a blank record at the end looks misleading and just aesthetically unpleasant. : )

The only way I've been able to get rid of it is to disable the "allow additions", but then I can't use the form to populate this field for the records that are still empty - the drop down box just doesn't appear for these records, and thus a value can't be selected...

 

[traingamer]

Don't use the 2nd table as the data source as it may not yet exist.

use a query like:

SELECT T1.ID, T1.LastName, T1.FirstName, T2.*
FROM tbl1 T1  LEFT JOIN tbl2 T2 ON T1.ID = T2.ID;

Then as soon as data is entered in a field from tbl2, the record will be populated in the database.

traingamer

 

[PHV]

I guess an outer join make the query not updatable.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244