Is it possible to create a form field with auto-type / auto-populate?

[jim.thornton]

I have two tables:

Households
- Name
- Address
- City
- Province
- PostalCode

Members
- FName
- LName


- HouseholdID


Households - Members is a one-to-many relationship.

I've created a form for Households -> No problem.

I've created a form for Members -> However, when entering members, it is impractical to know what the ID # is for the correct household. Is there an easier way to input members? Or, maybe is there a way to enter a member from within the household form?

[Bob Fitz]

The usual way is to have a main form bound to Households and then have a sub form, bound to Members, on the main form.
If the forms are linked correctly, when you create a new subform entry, Access automatically fills the joining field in the sub form.
I can't see a Primary Key for the Household table. I assume that is a typing omission.
If you open the main in design mode and you the wizard to add the subform control, Access will do all the work for you. Without the wizard you would need to set the "Link Master Fields" and the "Link Child Fileds" properties.
I have attached a basic example for you:

[jim.thornton]

The usual way is to have a main form bound to Households and then have a sub form, bound to Members, on the main form.
If the forms are linked correctly, when you create a new subform entry, Access automatically fills the joining field in the sub form.
I can't see a Primary Key for the Household table. I assume that is a typing omission.
If you open the main in design mode and you the wizard to add the subform control, Access will do all the work for you. Without the wizard you would need to set the "Link Master Fields" and the "Link Child Fileds" properties.
I have attached a basic example for you:

Thank you for helping with that. You are correct in the omission for the primary key (I do have one).

I have created the form and subform (actually I like the linked form better), but it works the same.

However, now I have a new issue. There is more to this DB but I was trying to simplify it for what I needed. But, I will expand:

What I'm trying to create: Households contain multiple members, but each member can only be assigned to one household. Then, each household will will have multiple scenarios. In each scenario you can assign one or more multiple incomes to each member. For example, one member might have employment income of $50,000 but they might also have pension income of $20,000 and might have rental income of $15,000. So, I need the ability to have multiple income sources assigned to a member.

I've tried to create this and when I go to enter a member into the linked form (or a subform) it throws me an error "You can not add or change a record because a related record is required in table "Households" (which is the name of my table).


This is the income table that I have:
Tbl_Income:
- IncomeID (primary key)
- Incomesource (short text)
- IncomeAmt (currency)
- IncomeFrequency (short text)

So... For clarification, I now have three tables:

Tbl_Households
Tbl_Members
Tbl_Income

NOTE: I also have a link table for Members/Income

Relationships: See image.

Any advice on how to fix this?

[jim.thornton]

It seems the problem is being caused because the HouseholdID field is not pre-populating with the correct household ID from the linked form.

How do I get that to pre-populate?

[Bob Fitz]

How do I get that to pre-populate?

Waht do you mean by "the linked form"?

Is that form an actual subform control on the main form or is it a separate form which can be opened and closed independently ?

The method for getting the HousehouldID from the first form to populate a field on the second form are very different for each scenario.

[Micron]

IMO your member table should be linked to memberid in memberincome. You have it on incomeid, and both are PK fields. That means you have a 1 to 1 relationship, and it is very rare that this is acceptable. You will not be able to have more than one income value for a member as a result.

EDIT - in addition, if the pk fields are autonumber, there's no way to guarantee that these autonumbers will remain in sync between 2 tables. IMO, you never want to join on 2 autonumber pk fields.

[jim.thornton]

IMO your member table should be linked to memberid in memberincome. You have it on incomeid, and both are PK fields. That means you have a 1 to 1 relationship, and it is very rare that this is acceptable. You will not be able to have more than one income value for a member as a result.

EDIT - in addition, if the pk fields are autonumber, there's no way to guarantee that these autonumbers will remain in sync between 2 tables. IMO, you never want to join on 2 autonumber pk fields.

I noticed that error after I posted it. I've corrected it. Would you say these relationships are now correct?

[jim.thornton]

Waht do you mean by "the linked form"?

Is that form an actual subform control on the main form or is it a separate form which can be opened and closed independently ?

The method for getting the HousehouldID from the first form to populate a field on the second form are very different for each scenario.

Hi Bob... I mean that when I created it, instead of doing a subform, I did a linked form (with the wizard). I've included a screenshot below.

[Micron]

Would you say these relationships are now correct?

Sometimes it's not possible to say with 100% certainty unless you fully understand the purpose. Off the cuff, I'd say that I don't see the point in memberincome - it has no data fields, just relationship fields. I imagine that a memberID could link to memberID in income (save for that fact that it's not there) allowing one member to have several income definitions along with all the supporting data you need. I don't see the need for strategy field in income either - it's a duplication of the same data elsewhere, plus if you had memberid as a FK in income, you wouldn't need strategy in income at all. Again, all according to how I interpret the process it's supposed to support.

[jim.thornton]

Sometimes it's not possible to say with 100% certainty unless you fully understand the purpose. Off the cuff, I'd say that I don't see the point in memberincome - it has no data fields, just relationship fields. I imagine that a memberID could link to memberID in income (save for that fact that it's not there) allowing one member to have several income definitions along with all the supporting data you need. I don't see the need for strategy field in income either - it's a duplication of the same data elsewhere, plus if you had memberid as a FK in income, you wouldn't need strategy in income at all. Again, all according to how I interpret the process it's supposed to support.

I'm not following you there, but the purpose (or what I'm trying to accomplish) is this:

I'm trying to create a household. Within the household you would have multiple members (think spouse/spouse). Then you can create different strategies (or scenarios) that you can work with. So the idea would be to create one strategy where Spouse 1 is earning employment income & rental income while Spouse 2 is earning pension income. Or... Scenario two, Spouse 2 continues to work. So in this case Spouse 1 would is earning employment income & rental income while Spouse 2 is earning employment income (no pension).

The purpose for all of this is because I'm working on a cash flow/budgeting DB. It will allow the me to pull up different scenarios. There is a lot more to the DB than just the members/income tables. I've also got assets, debts, mortgages, expenses, etc. But the concepts will just multiply out so I am only asking about these tables to understand the concepts.

To further explain strategy:

Each household can have multiple members as well as multiple strategies. Each strategy would use the same members (but if that is too difficult, then I can just make the relationship between strategies and members, instead of household and members.

[jim.thornton]

@Micron, I have changed the relationship between household and members. Hopefully this simplifies things. Now a Household can hold many scenarios and a scenario can have many members. I did it as a one to many relationship, that way if I delete a scenario it will delete the corresponding members, but it won't affect another scenario. Also, I can have one scenario with one member, and another scenario with 2 members and if I change a member it won't change the other scenario.

Do you think this is better?

I guess there is probably no need for ScenarioID in the Tbl_Income table then, is there?

[jim.thornton]

OK... Scratch that. I think this is what you meant... ???

[June7]

No tutorials I viewed for 2016 form wizard show a dialog with those options.

[jim.thornton]

No tutorials I viewed for 2016 form wizard show a dialog with those options.

Hello June7,

To replicate do the following (after the tables are created):

CREATE > FORM WIZARD > Select the fields from the HOUSEHOLDS table and then add the fields from the MEMBERS table

Once you do that and click NEXT it will give you the screenshot that I posted.

[jim.thornton]

June7, I have tried doing it a different way. This time I created the base form (Households) and have designed that how I want.

Then I went into the design and added a SUBFORM and it said it was going to link it via HouseholdID.

In the design view I can see the subform added, but in the Form View it is not showing the subform.

Any ideas?