Linking form & subform information

[PotatoChip]

Ok, I have a form and a subform. The form shows a document with it's pertinent information. The subform has some of the same fields as the form and shows where the document was sent.
The main form fields are:
Number, Type, Title, Version, EffectiveDate, Status, Owner, Reference, ChangeRequestNo , Comments
The subform fields are:
DDL_ID, Type, Number, Version, DocCtrlNo, Location, Returned, ChangeRequestNo

I know it is redundant to have some of the same fields but long story short, it's the easy way round. So, through the wizard that pops up when you add the subform to the main form I was able to specifiy that the Type, Number and Version should be the same in both the form and subform. How do I get it to also update the ChangeRequestNo ? So, if the ChangeRequestNo is updated in the main form it will show up in the subform. I'm driving myself sideways trying to work out what is probably a very simple thing.

Many thanks,

PotatoChip.

[NeoPa]

Why is it, I wonder, that some of the most complicated issues are from people looking for the easy way out? A clue: Never take short-cuts without a map!
When you know the answer to that you're unlikely to get yourself into such situations.

On to the "simple" question then.
The picture you paint is reasonably clear, but what wasn't was why you chose to omit the [ChangeRequestNo] field from your Master/Child links?

It may be pertinent later, but is the subform a single record display, or does it list all matching records?

[PotatoChip]

I didn't choose to omit [ChangeRequestNo ']; the wizard maxes out at linking three. I link the [DocumentType], [DocumentNumber] and [Version]. The subform lists all matching records as the subform shows where the documents were sent to so there can be multiple records.

[NeoPa]

Ah, that's exactly what I was after :) Now we can progress.

I suspect the wizard is where the restriction of only three connecting fields is found, though you should check as I've never myself used more than three.

Once you have the form created (after the wizard) edit the form in Design View, and click on the subform area. Now, sometimes this will select the Subform/Subreport control, but it's easy to click on the wrong area so if your properties box indicates any other type of control is selected then simply try again until a Subform/Subreport control IS selected.

Properties showing now should include both Link Child Fields & Link Master Fields. These should both be updated to add the extra linked field in. Use a ";" to separate the individual fields.

When you specify more than one field or control name for these property settings, you must enter the same number of fields or controls for each property setting and separate the names with a semicolon (;).

PS. Press F1 after selecting either of the properties to get a fuller explanation of how these two properties work together.

[PotatoChip]

It worked! I wish I was as smart as you at Access. I didn't even know that property existed. I've taken 3 courses in Access and the more I work with it the more I realise the courses didn't even scratch the surface in terms of what can be done and how to do it in Access.

Thanks heaps!!

PotatoChip.

[NeoPa]

You're very welcome - and thanks for the compliments :)

[PotatoChip]

So once my boss saw how I had managed to link the ChangeRequestNo (and that it worked perfectly), she asked if I could add another field for MemoNos and link it in the same way. So, I added the fields to the appropriate tables, created the relationships, added them on my form & subform and linked them as I have learned how to do. However when I update either of the fields(MemoNo or ChangeRequestNo ), they update perfectly until I go to a new record or exit the form. Then if I go back to the record I've already fully updated, the subform doesn't show the data. It's still in the table; just not the subform. It should be noted that only one record will have either a ChangeRequestNo or a MemoNo. No record will ever have both at the same time. Also, the form will work perfectly when you simply link one field. For some reason, it only gets upset when you link the two.
What am I doing wrong????? Please help!

[NeoPa]

I'm afraid your description isn't clear enough for me to know the answer to that one :(

I would suggest a .Requery, but I don't even know if you're using any code in your project. What are the circumstances where a record is added but not reflected in the subform? If you go out and back in again, does the new record appear then?