Creating group membership database

[reglarh]

I am new to this game! I am having problems developing a membership database - help would be welcome. I have three main tables.

1. A table of 600 members of an organisation

2. A table of the subgroups these members may join, about 80 in total.

3. A table of the members of each group.

The members do not have a unique ID - complicated reason for this so I use a system assigned ID. Group ID does have a unique Id but I chose to use a system assigned ID.

Table 3 records effectively consists of just two fields, memberID and groupID. When I create a form and subform to enter these values all is well. But I cannot expect users to know these values, so I have been trying to create a subform that creates/lists/removes members from groups, using a Group main form with a member tabular subform with a surname search through a combobox. Groups have between 5 and 20 members.

e.g enter 'smit' in the combobox on the subform and a list of smiths is displayed together with the full name, from which the user selects the correct entry. At this point the record showing for instance, Paul Smith belongs to Group 17 is written to table 3. All sorts of issues arise, too many to document. Any advice on how to achieve this, preferably without resorting to VB?

 

[Mile-O]

Sounds like you are binding your forms to tables rather than to queries based on your tables.

Take a look at my example. Think this is what you are trying to do.

 

[reglarh]

Can you help on downloading? When I try to download the file type gets changed to php. Do you know why?

 

[Mile-O]

Sorry, no idea. It downloads fine for me. Even if you right click and save as?

Tried uploading it inside a .zip file, but that method is failing.

 

[reglarh]

I renamed it and it loaded correctly. Goodness knows why it was rename in transit!

I have had a 12 hour day tearing my hair out, so I will have a look tomorrow.

Thanks for the response.

 

[reglarh]

This is just the function I want, but can you please advise me on why it works with a query but not a table?

Thanks

 

[Mile-O]

The subform uses a query to join two tables and this is used as the source for the form. The join brings together the members table and the GroupsToMembers table to use fields from each.

The parent form uses a query on groups because it's a better practice.

The reason it's better practice is that, in a query, you can join tables, sort records, group records, and create new fields through maths or other functions before binging them to a form. Binding a table to the form you only have the raw data from that one table.

The forms are linked on fields (master and child) on the group is which appears in both queries.

 

[reglarh]

I am still having trouble replicating your design into my environment. You have defined no relationships between your tables. Why not? How did you set up your subform? Was it a standalone form which you dragged onto the main form or did you use the subform wizard in situ? When I create a combobox on a subform it only allows one row, unlike yours.

Sorry for so many questions! I created the tables and sample screens and reports and populated the Groups to members table throuw a datasheet using known system assigned IDs. I thought I was nearly there until I failed to provide the missing link of assigning members to groups.

 

[Mile-O]

You have defined no relationships between your tables. Why not?

Because I was just throwing the database together as an example for you. Not going into full-blown design.

How did you set up your subform?

I used the subform control on the form and then set its SourceObject to another form. Also made sure the subform was viewable only as a Datasheet. Linked to the main form through the Master/Child properties. Needs to ba related field between the two forms.

When I create a combobox on a subform it only allows one row, unlike yours.

In my combobox I bound it to a query, to allow a selection of members. Also changed the ColumnCount to 2 and set the first column width to 0, so that it didn't show. Users don't need to see the auto-generated IDs - it's all meaningless outside the database.

 

[reglarh]

Try as I might I cannot get the combobox to be the basis of multiple rows. The subform allows me to step through the members of a group but displays only one at a time, in a single row. What is the setting(s) that create multiple rows?

 

[Mile-O]

Any chance of posting up an example of what you have? Maybe a bit of dummy data in it.

 

[reglarh]

Sorry I have been slow to follow your request to upload a sample database. I have been rather busy on other matters and also been for a fitting of a wig made necessary by my having torn all my hair out!
Your solution works of course, but through lack of knowledge I do not appreciate the steps you took to make this happen. In particular why the settings were necessary and when in the development of the demonstrator they were applied.
I am uploading a cut down version of what I am trying to achieve. In my full system the members table runs to 40 fields with another 10 tables linked in various ways.
In the attached I have given up any thought of using a combobox to select members of a group and am reverting to using a membership number via a subform. This is indexed and unique. The members table also users a system generated ID.
I populated the GroupMembers table via a datasheet and the Groups form works fine for reading only. When I try to add a Member to a Group I get an error message telling me that a duplicate key or index or…whatever…. will occur and the record cannot be saved.
All I want the subform to do is to enable the addition of a member to a group via the member number, with the member name being displayed to ensure the correct member number has been input. I don’t want the user to be able to change data on the Members or Group tables.
I have been using Access 2000 – The Missing Manual. Do you have any recommendations for a better tutorial? The thrust of the book is all about retrieving data not creating or updating data.

 

[jdraw]

As a start, in your GroupMembers junction table, make a unique composite index of the combination
GroupId and MemberId (to prevent duplicates)

 

[reglarh]

Thanks for the suggestion. This will stop a future problem but I still can't move forward at all. I also meant I was using a 2010 book, not 2000!