I'm a long time user of both products.
What you want is called a "Portal". When reading the documentation on filemaker, look up this term.
You will need a relationship that links the records in the Layout (Think Form) to the records in the portal (think sub form). The relationship will do the same job as defining a record source for an Access subform.
Note that the relationship can be a self join.
Also, Access limits subforms to forms that display only one record at a time. You may be pleasantly suprised to find that Filemaker allows you to put portals into the body of a list view layout--That's a feature I frequently wished for when developing Access solutions.
I am new to portals and I have been able to display information in table form but I am wondering how to organise the information into a layout (in form view) which enables me to create new records into a number of different tables in such a manner that I have a tree of primary client information, subforms (portals), and a further layer of sub-subforms(so to speak). I know this sounds overly complicated but it works so well in my current Access Solution I did not want to change it too much because I want to bring all the existing information across to FMP10.
Thank you for your post.
I have not used Access, but the information from PhilModJunk helps me understand what you are trying to accomplish.
Assuming you have two tables (for this example, I'll call them MAIN and SUBFORM), pull down the File menu and select "Manage -> Database..." Click on the Relationships tab, and you will see a graphical representation of your tables. If you haven't already created a link between the two tables, then click the "key" field in the MAIN table and drag it over to the matching "key" field in the SUBFORM table. A line will now connect the two tables together. Halfway on that line is an icon. Double-clicking on this icon displays information about the relationship. On one side will be information pertaining to MAIN and the other side information pertaining to SUBFORM. At the bottom of the SUBFORM side, check the option "Allow creation of records in this table via the relationship". Click OK a couple of times and return to the layout that has your portal. Go into Browse Mode (View menu) and you can now enter records into SUBFORM while viewing records in MAIN.
This should help you get started and point you in the right direction.
If you need clarification for any of the above steps, please let me know.
Adding to TSGal's excellent step by step instructions: Once you've defined your relationship, enter layout mode and use the portal tool at the top of the screen to draw a rectangle on your layout where you want your portal (subform). A dialog box will pop up where you can select SUBFORM as the "record source" for your portal. You can then select the fields you want from the SUBFORM table and select various options for how the portal appears and behaves.
Once you've dismissed the dialog box, you'll probably need to adjust the layout elements of the portal to better suit your needs. This process shouldn't be terribly different to you compared to how you'd set up a subform in Access--only easier and quicker in my opinion.
Now return to browse mode and try entering data into the first blank line of your portal.
Thank you TSGal. This has been really helpful. I have got that far and I am pleased to know that what I have done is exactly as you describe. I have tried to enter a new record but it does not seem to automatically enter the key field into the new record so I can immagine a circumstance where the record could end up in someone else's file. How do I get the new record to automatically put the ClientID from the main form into the ClientID related field of the subform?
Thanks PhilModJunk. Interestingly I think the tools for laying out forms in Access are actually easier than in FMP but maybe I will find it easier when I get to know them better. I will keep plugging away at it and if I draw a blank I will post another question.
Are you referring to the key field for the parent, or the echoed foreign key in the child?
For the parent's unique key, when defining (as Type: Number) in Manage Database, after you create the field, go into Options ... and under the Tab 'Auto Enter' select both 'Serial number' and 'Prohibit modification of value during data entry'. In addition, under the Tab 'Validation', select 'Always', D-select 'Allow ...', select 'Strict data type Numeric Only', select 'Not empty', and select 'Unique value'.
Using portals with record creation allowed in the child table (via the many side in the relationships graph) should automatically take care of the echo of the parents unique key (when working on a layout based on the parent) ... though scripts can be used to insert the "echo" into the child table as an alternate method... which I haven't started doing that yet myself...
( Been reading about bash lately, and currently I like the idea of thinking of the foreign key as being an "echo". We don't really think of a child's parental dna as being "foreign", instead its really just an "echo" that resides in the child. It's native, but its still an echo. Two echo's intermixed with slight mutations result in unique id's for bio-child entities perhaps, but they're still echo's. Though the term 'copy' or 'clone' could be used, for the foreign key, I like echo better. )