Suform not linking

[Chris]

Help! I cannot see the problem. My forms don't link.

Parent form's record source is tbl2PO, InvNum PK, ClientID is in the table.
Subforms's record source is tbl1ScheduledPO, ClientID PK.

The intent of the form is to update/add/edit tbl2PO while displaying (only)
the ScheduledPO data.

Using the wizard, I added the subform to the parent with Master/Child
linking ClientID.

When I open the form it works fine for the parent, but there is never any
display in the subform -- when a record really does exist. There is no
ClientID displayed in the control so I assume it is not linking.

Can someone shed some light on what I may have unknowingly done to inhibit
the link?

 

[Golfinray]

I would start at the table. Does the table have the data you are trying to
pull to the subform? Is client ID in the table supplying the mainform? Here
you say that you are using ClientID PK . Is there a ClientID PK in the tbl2po
table? There appears to be a clientID but no ClientID PK. It can't link if
the tables don't have a mutual field.

 

[Chris]

The parent form RS table has ClientID NOT as Primary Key; the child form RS
table has ClientID as Primary Key.

Each form has a control whose Control Source is the ClientID field in their
Record Source table. (Parent form calls ClientID from tbl2PO; Child form
calls ClientID from tbl1ScheduledPO)

The Master/Child link is ClientID in each case and was supplied via the
Subform Wizard.

When the parent form opens both the Parent and Child forms are displayed.

The Parent form allows data entry/edits as intended.

The Child form only displays the form -- without data where data really does
exist. That would include not displaying the ClientID, which is why I assume
no link.

Why, if the field exists in each table and their respective controls are
resident on each form and the wizard linked the subform -- will it not work.
What would cause non-link in this case?

 

[Golfinray]

Try quickly just writing a basic form (main form, not subform, and use
tbl1scheduled as the record source using the form wizard. See if clientID
comes up on the form. If it does, your table is probably ok and the linking
SHOULD be ok. Then try to just make sure you don't have something weird
changed accidentily in the form or subform controls. If all else fails (and
I've had to do this many times) just delete the subform and insert it again
letting the wizard link. Make sure the wizard links the coreect two fields.
Do that by going to the Data tab on the form properties.

 

[Chris]

I did as you suggested -- even though I have done the same several times.

This form will not link and I cannot understand why. The wizard did it with
data that is available for use.

What inhibits its linking? -- consider this question in view of the fact
that it is linked correctly.

 

[Chris]

I have related the tables. No joy.

I tried with the join each of the three ways.

You suggested to relate on the primary key. The ClientID is the primary key
in the suform's table. It is not the primary key, but does exist in the
parent's table.

What else can it be?

 

[Chris]

Do you recall that I am adding the parent record? I call the form with a
docmd...add the record.

 

[Golfinray]

Two other possibilities that I can think of. 1. Make sure that client
datatype is the same in both tables. If one was set to number and one to text
they would not link.
2. I know this is a lot of trouble, but build the form from the table you
are using as you subform and build your subform from the table you are using
to build the form. It is possible that your form is the "many" side and the
subform is the "one" side, in which case it won't link. I'll have to try to
do some deeper thinking if that doesn't work.

 

[Chris]

The types are the same in both tables...text.

If I understand, even though the record is in process of creation (the main
form is the one adding a record) it should link.

Parent form: a client can have many records.
Subform: each client may have only one record. A client may have none,
though.

Is mine an example of what you say will not link? I mean, Many parents
possible (one being created with this form) and only one Child for a given
client?

I am so lost. I thought for sure it was an adding record issue.

 

[Golfinray]

Yes - you need to make the subform the mainform or redesign your tables. If
you could get both tables to have the same primary key, that would help also.
Get ACCESS to asign a primary key, usually just a field of numbers. That way
any forms or queries you build will always have the same primary key. I
queries, forms, or reports you don't have to display the primary key but use
it to link on and query against.

 

[Chris]

I do appreciate the time you have given this issue of mine. I'll figure
something out that will work -- more than one way to skin that cat!

 

[Chris D]

I seen this happe too many times.
There are at least 2 reasons:
1) You have set the Data-Edit value to no and the link requires the subform
to be editable.
2) You have a field reference in the subform data query that is not editable
but which the parrent form is editing.

Both of these will make the subform "gray out"