Problems with subforms

[John V]

I am building what is supposed to be a simple database for a project for my wife. Being new to database design I am running into a few problems. I have extensive experience as a user with a large and complex database for tracking an extensive rental inventory, but designing a database is new to me.

This is a database to track patient visits to various clinics within a region.

Basically I have a Contact Information Table that has all of the usual suspects for fields including a Patient ID number (This is a unique number for each patient assigned by a government agency) I am using this as my PK for this table. I then have various other tables to track information for things like Targeted Care, Referrals, etc. Each of these tables generates a PK using the Autonumber format, with the Patient ID from the Contact Information table being the FK in each.

I have setup One to Many Relationships between the Contact Information table and all of the other tables. There are no relationships among the other tables (i.e. all of the other tables relate to the Contact Information Table only). I have separated the other information into various tables for data analysis purposes later.

I found another thread here that said to use subforms to get the tables to auto-populate the Patient ID (FK) from the Contact Information Table (PK) to the other tables. This was working very well at first as I was playing and experiment with the various table layouts, etc. The wizard would pop up asking what linked fields I would like between the tables, which I set, and I was able to enter the information and have it turn up in my various tables. Wonderful.



After a few hours of experimenting, creating master forms and deleting them again, I decided to jump in and lay out my real master form. Suddenly when I went to insert my subforms, the wizard still comes up but it never asks me what linked fields I would like. What the!

I have tried using the forms (for subforms) I used in my experiments, as well as some forms I didn't. Each time the wizard pops up it skips the questions about the linked fields.

I thought, no worries, I'll just assign the Master Fields and Child Fields in the table properties menu. When I try to do this (by clicking on the elipsis in the field properties) it says I cannot do this with unbound objects. Aargh!

What am I doing wrong here? Am I missing something stupidly obvious? I have laid out my tables and forms (to use as my subforms) just the way I want them. Is there some way around this with out starting all over again?

If the solution is to start again can I import my tables and forms.

Help me please! This plugging my nose and jumping in is harder than I thought.

Thank you for any and all replies.
John V

[amrut]

If your table relationships are correct following should work -
Create the bound form based on Master and Child tables. Open the Master form in design view. Drag the child form from navigation pane to this form. With the sub form (child form) selected, go to the Properties Sheet > Data > Link Master Fields. Click the elipsis to bring up the dialog to link the forms. Select your master and child fields from respective forms. This should set up the Master-Child relationship.

When you drag the child form to master form, the wizard should automatically pop up the dialog to link the forms.

[rpeare]

Check the data source for your main form. If you don't have a data source you'll get that message every time.

Your main form should be based on your client (if that is the record from which everything else flows)

[John V]

If your table relationships are correct following should work -
Create the bound form based on Master and Child tables. Open the Master form in design view. Drag the child form from navigation pane to this form. With the sub form (child form) selected, go to the Properties Sheet > Data > Link Master Fields. Click the elipsis to bring up the dialog to link the forms. Select your master and child fields from respective forms. This should set up the Master-Child relationship.

When you drag the child form to master form, the wizard should automatically pop up the dialog to link the forms.

Thanks Amrut. Unfortunately this is what I am trying to do and it is not working. I appreciate you taking the time to respond.

John V

[John V]

Check the data source for your main form. If you don't have a data source you'll get that message every time.

Your main form should be based on your client (if that is the record from which everything else flows)

That's it!! Fantastic! Thank you so much.

John V