Having some subform fields auto fill from mainform fields (1 Viewer)

[borisbruno]

How can I get some mainform fields' data to be the first entry in a subform? From the mainform, I would like the Head of Household name and date of birth to be carried over to the first entry in the HouseholdMembers subform.

The two forms are tied to separate tables. For each household member I need to be able to enter full legal name, date of birth and some additional pieces of information such as income and source of income.

What I'd like to avoid is making the users enter the Head of Household's name and date of birth twice. Is there a way to carry that information over to the subform?

Thank you, Charlotte

 

[RuralGuy]

Base the SubForm on a query that includes the table with the other information. You should *never* have that type of information in more than one table in a normalized database anyway. Your SubForm table should have the Main table key as a ForeignKey in one of it's fields. Use that field as one of the Link Master/Child Fields.

 

[borisbruno]

You should *never* have that type of information in more than one table in a normalized database anyway.

I know you are right about that. And believe me the Head of Household's name and DOB went back and forth between the two tables several times. I finally decided to leave it in the Household Info table after reading some threads here.

The advice was to include in one table all of the data that defined an object on a one-to-one relationship. In this case, I thought that the Head of Household name and DOB was a one-to-one with the household information such as address, phone number and signature. I put the names of the household members into a separate table along with some additional data that pertains to each individual including the Head of Household. I felt there was a one-to-many relationship between the Head of House (and the other household information) and the members of the household.

I've attached a copy of the database; thought it might help to see what I've tried to do. I'm open to any suggestions.

Thanks, Charlotte

 

[borisbruno]

Sorry, I should have told you this was Access 2000

 

[RuralGuy]

In looking at your table structures it seems to me that the [Household Info] table should just have a [MemInd] from the [Names of Household Members] table in a HeadOfHouse ForeignKey field rather than duplicating all of the data. What do you think?

 

[borisbruno]

I'm going to think about this over the weekend; I'm too befuddled right now and I gotta go.

Thanks!

 

[RuralGuy]

Thinking is good! I'll wait.

 

[borisbruno]

Okay, help me break the mindset that I'm in. Since the [MemInd] is unique for each member of the household, how do I structure the [Names of Household Members] table so that only the HeadOfHouse [MemInd] value is passed to the [Household Info] table? Won't it try to pass each members' [MemInd]?

Do I need to pass the value as an argument based on the relationship value that is entered? In which case I have to limit the choices they can enter into this field.

 

[RuralGuy]

Let's see if I can explain it clearly and briefly. Your Food Pantry Data Entry form is bound to query HouseHoldInfoQ. That query would have the [Names of Household Members] included and linked by the [RecipID] field. You replace the HHfields with just one [HeadOfHouse] field and fill it as a ForeignKey with the [MemInd] of the individual you wish. You could use a ComboBox to select a different Head or even add another person to the household.

 

[borisbruno]

RuralGuy,

I'm working on this, but after several hours of hitting my head against the laptop, I'm not sure it's within my ability. I've removed the HH fields from the HouseholdInfo table and replaced them with a single HeadOfHouse field. I've tried linking the HouseholdInfo table and the [Names of Household Members] table by linking the HeadOfHouse field to the MemInd field. Is that what you were suggesting?

This causes a second [Names of Household Members] table to appear on my table relationship screen with the new relationship defined. Why can't I create this relationship from the existing [Names of Household Members] table?

If I'm not being clear, let me know. As you predicted, I have other questions! However, my head hurts and I think I'll just quit for today.


PS What does the hth mean? I've seen this with other signatures as well, and just wondered!

 

[RuralGuy]

Hi Charlotte,
I don't have an answer to your relationship question off the top of my head. If you want to post another db I'll look at it. I think you are on the right track!!

Note: hth = Hope That Helps!

 

[borisbruno]

Hi, RG!

Thanks for defining hth for me.

I will post another copy of the database in a bit. I'm a little bullheaded and just have to keep dogging it for a while longer before I give up.

In addition to this issue, I have some query questions too that will probably be easier to answer if you can see what I'm trying to do. However, you may want me to start a different thread in the queries section for those questions. I'm not exactly sure of the proper etiquette used by the forum for keeping questions separate.

I'll talk back at you later! Charlotte

 

[borisbruno]

Okay, here is a copy of the most current version I'm working on. Question: should I add another table that will draw together the [RecipID], [HeadOfHouse] and [MemInd]? The way I have it now causes errors when trying to add or edit a record to either table. Error states that a record is required in the other table.

Thanks, C.

 

[borisbruno]

I'll try this again!

I seem to be having problems today!

 

[RuralGuy]

Hi Charlotte,
Go to Tools>Options>General tab and turn OFF Name Auto-Correct and turn ON Compact on Close. You'll thank me for it later. I have some chores to do today so I'll be gone for several hours. I'll tackle it when I get back.

 

[borisbruno]

Are these two things something that should always be done when using Access? (Just in case I need to know it for the future. The church that I'm volunteering at has two other database projects that I'm working on along with this one. I just haven't been able to give them as much time, so they are not as far along, i.e. I haven't had the opportunity to make mistakes, yet!)

Also, want to ask you about Option Explicit being set on. I seem to remember from the class I had on Visual Basic that this was something that should always be done in VB code general declarations. I think there was another option that was always supposed to be set too, but I can't remember what it was right now. Guess I'll have to pull out the old textbook. As I remember it though, it seemed like you were sort of forced at some point to choose whether or not you wanted these options on. I guess Access doesn't come with training wheels, huh!

Thank you for all of your help, including those answers that I've gleaned from your responses to other people's threads. Please, don't ever feel rushed to respond; I know that all of you here have lives outside of this forum. I just appreciate that this site exists, and that all of you are willing to give some of your time to help those of us who are Access challenged.

When I leave here today, I may be out of reach of the Internet myself until Monday. Have a good weekend!

Charlotte

 

[RuralGuy]

Are these two things something that should always be done when using Access?

MVP Allen Browne has a few comments on Name Auto-Correct that I'll let you read for yourself. You already know my opinion. As for Compact on close the jury is divided on that one. I keep it on during development because dumb code can cause bloat too. I also leave it on after release but ghudson recommends only doing it when the db gets to a certain size. There are differing opinions on the topic and you need to decide for yourself.

Also, want to ask you about Option Explicit being set on.

That option forces you to declare *all* of your variables in your procedures. It catches a ton of typo's and I use it religeously. From any code page goto Tools>Options>Editor tab - Require Variable Declaration. My acXP does not always put it as the 2nd line in the code module so I add it manually.

Hope you have/had a great weekend.

 

[RuralGuy]

Here's your db back after I make a few changes. See if it doesn't get you a little further along in the development process.

 

[borisbruno]

RG Hi,

Just got back to this, sorry it's taken me so long. Haven't had a chance to look at the improvements you've made, but wanted to let you know I'll look at it first thing tomorrow morning. Too much other stuff going on these last few days. But I do need to get this project finished as I'll soon be starting back to work, and the volunteers want this by Jan. 1.

Thanks for the directs and input on my questions. I'll do a search to see what debate about compact on close I can turn up.

I did have a nice weekend, thank you, even though I did spend a good chunk of it working for my sister.

Talk to you later, Charlotte

 

[borisbruno]

RG,

It still won't let me add or change a record because a related record is required in table [Names of Household Members]. Is there a major flaw in my design somewhere?

I had hoped that from the data entry form a name could be typed in, and if a record existed that matched, the form would be populated with that info. Then I hoped to allow edits of phone numbers and address, etc. and allow a current date of service to be entered. Is it possible to do this with what I have now?

Please, let me know what you think. Charlotte