Problem with form for Many-2-Many relationship...

[j_cocker]

I am in process of designing a DB for an after school club.

It has a children table, and a parent/guardian table.

It also has a many to many relationship between the above tables using a "junction" table which also holds information about the nature of the relationship and whether a parent/guardian is the preferred point of contact for that child.

I am currently designing a form to allow the user to maintain all parents/guardians for each child. I want the child details at the top, with a list of all the contacts (parents/guardian) at the bottom in a sub form. The list should be populated with the existing contacts, allowing the user to modify it.

I am also using a query to construct a full name string for each parent from the separate first and last name fields. This will make it easier for the user to add new contacts.

I am a newbie and despite looking at various examples on line I am still unable to achieve this. What I have done so far was to create the basic structure of the sub form for displaying the parent list , and then, based on the examples that I found, manually edit the "Row Source" field in the Property Sheet....

I attached a copy of a cut down version (to save on bandwidth) of the database to this message.

Any help will be much appreciated!

Thanks in advance,

J.

 

[MarkK]

In the rowsource of the ParentID combo on the subform you can eliminate the query and just use SQL like this ...

SELECT [Contact ID], [First Name] & " " & [Last Name] As FullName FROM tblParents

Then, on the Format tab of the property sheet of that same combo in design view you need to set two other things. ColumnCount needs to be 2 or the second column won't show up. ColumnWidths needs to be something like 0;2, which is a semi-colon delimited list of measurements, in this case zero and two inches. This hides the first column.
Your table design looks great. I would avoid, in the future, naming things with spaces in the name. Use ContactID and FirstName and LastName. This saves you having to use the square brackets everywhere.
Cheers,

 

[j_cocker]

In the rowsource of the ParentID combo on the subform you can eliminate the query and just use SQL like this ...

SELECT [Contact ID], [First Name] & " " & [Last Name] As FullName FROM tblParents

Then, on the Format tab of the property sheet of that same combo in design view you need to set two other things. ColumnCount needs to be 2 or the second column won't show up. ColumnWidths needs to be something like 0;2, which is a semi-colon delimited list of measurements, in this case zero and two inches. This hides the first column.
Your table design looks great. I would avoid, in the future, naming things with spaces in the name. Use ContactID and FirstName and LastName. This saves you having to use the square brackets everywhere.
Cheers,

Many thanks - all now sorted!

Also, thanks for pointing out the spaces in the names - no intention of putting them there, just a mistake...

BTW, I foolishly I started the work with an existing DB hoping that it would save me time (it didn't, just more hastle to remove all the unwanted stuff). How do I get rid of the switchboard message when opening the DB?

 

[MarkK]

What version of Office are you running? In 2007 and later there is an Office Button in the upper left corner of the screen. Hit that, and along the bottom edge of that window, next to the 'Exit Access' button, you'll see 'Access Options.' Click 'Currrent Database' and the fourth item in the first section, 'Application Options' is 'Display Form.' Change it from 'Swithboard' to '(none)' and when you reopen the DB it will not try to open the 'Switchboard.'
I forget how to do it in earlier versions, but it will be an option or a preference or something.
Cheers,

 

[j_cocker]

Many thanks again, all no sorted!

Sorry, I do use Office 2007.