Trouble with multiple combo boxes on data entry form 2

[CantDo]

I am trying to create a data entry form (that can also serve as a view and edit form, if required). The form would be accessing data from 4 tables. Table 1 contains Family level data; Table 2 contains Family Member data and includes a linking Family ID field; Table 3 contains Group data with linking fields to the ID Keys of each of the Family Member table and the Group Position Title table; Table 4 contains Group Position Title data. Each Family can have multiple Family Members. Each Family Member can belong to multiple Groups. Each Group can contain Multiple Group Position Titles (i.e., each group can have, for example, volutneers, chaiman, vice-chairman, etc. positions).

I'm having trouble thinking of how to achieve my intended result. I am envisioning a form containing one combo box (or list box) containing group names. The form would have a continuous sub form (linked by family member ID?) showing three more combo boxes: one for Family name, one for member data and one for position titles. That way, for data entry purposes, the user would first pick a group from the form, an after update code would requery the subform, and the user could choose from family, family member (requeried after family update) and position title (no need to requery). Does that sound right?

Anyway, I can't get it to work. Any ideas on whether I've designed this wrong or ideas on ways to get this to work? Any help is much appreciated. Thanks.

 

[TheAceMan1]

How are ya CantDo . . . . .

Table [blue]relationships are not clear[/blue]. Post them back in the [blue]following format[/blue] (other that PK & FK, include all other fields of interest):

[purple]Format:[/purple]

[blue]<TableName>
PKname as Type
FKname as Type FK to TableName.PKname (if FK exist!)
FieldName as Type[/blue]

[purple]Example:[/purple]

[blue]<tblCompanies>
CompanyID as AutoNumber
CoName as Text

<tblAccounts>
AccountID as Text
CompanyID as Numeric FK to tblCompanies.CompanyID
AcctName as Text
AcctType as Numeric

<tblAcctDetail>
DetailID as AutoNumber
AccountID as Text FK to tblAccounts.AccountID
StartDate as Date[/blue]

[purple]Can't tell without the above . . .[/purple]

See Ya! . . . . . .

 

[CantDo]

Hi Aceman. Thanks for replying again. I'm sure this is a simple problem I'm missing, but I can't for the life of me figure it out despite having thought about it for about two weeks now!

Table 1 : Families
FamilyIDNumber as Autonumber
FamilyNameSalutation as Text
{other family specific fields not relevant to this question}

Table 2: FamilyMembers
MemberID as Autonumber
FirstName as Text
FamilyID as Number FK to Families.FamilyIDNumber
UniqueLastName as Text
{other member specific fields not relevant to this question}

This is where my structure breaks down. I have two other tables, one called Groups and one called GroupPositions, each with an Autonumber PK and a text field containing pre-populated choices of existing groups and positions to choose from.

I know these are the problem, but right now I have the following for these last two tables:

Table 3: Groups
Group ID as Autonumber
GroupName as Text
MemberID as Number FK to FamilyMembers.MemberID
PositionID as Number FK to GroupPositions.PositionID

Table 4: GroupPositions
PositionID as Autonumber
Position as Text

All of the relationships are currently one to many. I think I need a fifth table or query to actually link all these together, but I can't figure out how to structure it so I get the results I want. When I create a query, I get no results as no members have been entered into groups as of yet, so a form based on that query has no choices in the combo boxes. Should I create a table to just "lookup" values in the other 4 and then base a form on that table? But then how would a change to that table update the underlying 4 tables that need to be updated?

The first two tables are working perfectly right now, until I try to connect the members to groups they belong to. That's where I'm currently stuck.

I hope all that was understandable! Thanks!

 

[TheAceMan1]

CantDo . . . . .

So far so good. I uderstand.

Can any [blue]Group[/blue] have any [blue]Group Position[/blue]?

So far it looks like what you really want (instead of a many to many relationship) are [purple]LookUp Tables[/purple]. Depends on your return post . . .

See Ya! . . . . . .

 

[CantDo]

Aceman:

Any Group can have any position; in fact any group can have any number of positions. For example, (and these aren't necessarily actual examples) the Primary Group could have positions like President, Vice-President, Secretary, Treasurer, etc; the Secondary Group could have positions like Chairman, Executive, Volunteer (there could be many of these), etc. Some groups could have complete or partial cross-over positions, e.g., Groups 3 and 4 could each have Chairman, President, etc. but Group 4 could also have Volunteers while Group 3 has none.

Does that answer the question you were asking?

Thanks.

 

[CantDo]

Aceman: I should also tell you that at one stage, there was a fifth table (that also didn't work) which was structured something like this:

Table 5: GroupMembership
ID as Autonumber
Group as Lookup to Group table
Position as Lookup to Position table
MemberFirstName as Lookup to FamilyMembers table
MembersLastName as Lookup to Families table

but I couldn't sort out how to get this to work so I gave up on that structure.

 

[TheAceMan1]

[blue]Any Group can have any position; [purple]in fact any group can have any number of positions.[/purple][/blue]

According to the above, you have one to many relationships straight down the line with:

[blue]Families > Family Members > Groups > Group Positions[/blue]

Table Group Positions changes to:

[blue]Table 4: GroupPositions
PositionID as Autonumber
GroupID as Number FK to Groups.MemberID
Position as Text[/blue]

Form structure ([blue]by the forms wizard[/blue]) would be: [blue]mainform of Members[/blue], with [blue]subform of Groups[/blue], with [blue]subform of Group Positions.[/blue]
This gives you a [blue]mainform of Family Members[/blue] with [blue]subform of Groups[/blue], with [blue]subform of Group Positions[/blue].

The wizard will force [blue]Families & Groups[/blue] to be [purple]single view[/purple] forms, with [blue]Group Positions[/blue] as [purple]continuous[/purple]. If you'd rather have Groups & Group Positions as [purple]seperate, synchronized, continuous subforms[/purple], it can be done.

[purple]Your thoughts?[/purple]

See Ya! . . . . . .

 

[CantDo]

Aceman:

Thanks for the reply. I guess this isn't really what I was looking for. I was hoping to create a form where the user could first choose a group, then choose a group member and a position, all from combo boxes. The user would use this to add or edit field information into the tables to populate membership in groups.

Groups and positions are static (that's why you originally thought you could use a table lookup and that's what I've been trying -- and failing -- in doing). The concept *seems* simple enough: all I want is to take what I have: families and their members -- and assign them (if applicable) to Groups and position titles.

The form layout I'm trying to achieve is this:

Group: [combobox]

Continuous Subform: Members:

FamilyName [combobox] / FirstName [combox -- filtered after FamilyName updated] / Position [combobox]

I don't understand why, if I have four table all linked, this cannot be accomplished. It doesn't seem that much more complex than a student registry where you have students each taking any number of courses and each cxapable of achieveing any one of a finite set of grades in each course. The only wrinkle I've got is that I want to add an extra field (Families) to avoid duplicate entries and I want to be able to centre my form on the Group (course in the example) instead of the Member (student in the example).

Am I just dreaming that this is possible? Am I completely wrong? Man, I feel like I've dreaming this code for over a week now -- it's consuming everything I do and think. Thanks, Aceman, for whatever you can add.

 

[TheAceMan1]

CantDo . . . . .

I forgot to mention that tables [blue]Groups & GroupPositions[/blue] would [purple]require[/purple] respective lookup tables for the static [blue]GroupName & Position[/blue] you related to in your previous post.

As for the schema you seek:
[ol][li]A [blue]subform based on a parent table[/blue] can't be linked to a [blue]mainform based on a child table[/blue].[/li]
[li]The subform (as you want it) would have to include [blue]all four tables[/blue] (in order to get to positions) [blue]if the subform were bound.[/blue][/li]
[li]All the above indicate [purple]the comboboxes would do better if they reside on one unbound form.[/li]
[li]In any case, [purple]with comboxes alone[/purple], it would be [blue]easy enough to add a record[/blue] . . . . but for editing you need [blue]some way to know which records your editing[/blue]. I said records (plural) because you'll be editing at least three [blue]Members/Groups/Positions.[/li][/ol]
The problem your having is you can't can't get past certain [blue]rules[/blue] for Tables & Relationships, and if you juggle them around to fit your needs you screw-up the relational logic required for normal operations. I know what you want to do . . . but haaaaaay . . . rules are rules.

Out of curiosity! . . . with normal operations and mainform/subforms in full view, [purple]whats wrong with selecting a Member from mainform, then a Group in subform, then Positions for that group in subform?[/purple]

See Ya! . . . . . .

 

[CantDo]

Aceman:

Thanks. I think I understand all that and your explanation is very much appreciated. I guess that I thought with a relational database, you could start anywhere in the chain and go either down or up. Your explanation has shown me I'm wrong about that.

The idea with what I was trying to achieve was to eventually have two forms involving groups: the first as I've described for inital entry and editing purposes; the second would be tied to a previous form you've helped me with and would show groups to which a particular famiy and its members belong. This second form I had working a while ago when I had test data already entered diretly into the table. (I've since deleted that data and started creating the first form for that purpose.) I've been stuck ever since.

The concept would be to have someone enter and edit data by group but also be able to view and edit specific family data in another form. I suppose the second form could also be used for entry, but it would be much easier on the user if they could enter whole group information at once instead of having to go to each family individually.

My real surpirse at this not being possible stems from my crude understanding, I guess, but also from the fact that all the data is pre-populated (sort of) and all I'm trying to do is bring it together. What I mean is that the families and members data are handled separately and exist as fixed for any given time period; the groups are pretty much static -- as are the positions; they won't change much over time. So (except for some minor editing from time to time) all the data is known and all I'm trying to do is link them together appropriately.

But at this stage, I'm almost ready to give up and go with something less useful to the users. Thanks for trying.

 

[TheAceMan1]

CantDo . . . . .

Hang in there good buddy!

I didn't say it couldn't be done! Just not the way you were going. I had to somehow get you to see the rules, and if you understand, first thing to come to your mind should be that the [blue]Groups[/blue] form needs to be at the head of the pack (top parent!).

To save time (I know its killing you) goto the Microsoft: Access Tables and Relationships Forum

These guys & gals do this all day. Just be sure to explain the static state of Groups & positions.

See Ya! . . . . . .