Form Subform enabling

  • Thread starter Thread starter scottyboyb
  • Start date Start date
S

scottyboyb

Greetings,

I use Access 2000 and I have a main form that I want to use to open subforms
(12) for editing and adding records. The procedure I like is that one created
in the "Create form by usng Wizard" where you use a linked form.

First problem, the wizard only offers the choice if you have two tables
chosen. Second, I have been unable to figure out how to duplicate the buttons
to have more than one linked subform available to be opened. Is ther any way
to do this?

I am not a coder but I regularly take created code and change it to work for
me. But the code created for the linked table is more complicated than my
abilities. Any help would be appreciated. Please let me know if you need more
info.

Best,
Scott Brasted
 
Greetings,

I use Access 2000 and I have a main form that I want to use to open subforms
(12) for editing and adding records. The procedure I like is that one created
in the "Create form by usng Wizard" where you use a linked form.

First problem, the wizard only offers the choice if you have two tables
chosen. Second, I have been unable to figure out how to duplicate the buttons
to have more than one linked subform available to be opened. Is ther any way
to do this?

I am not a coder but I regularly take created code and change it to work for
me. But the code created for the linked table is more complicated than my
abilities. Any help would be appreciated. Please let me know if you need more
info.

Best,
Scott Brasted
Click to expand...

Whoa!

A Subform isn't "opened". It's a permanent part of a form, just like a textbox
or any other control. It *contains* some other form (typically for a table
related one-to-many to the mainform's table), but you don't need to "open" the
subform, and you don't need any code, and you don't need any buttons!

Do you really mean *a subform*? or are you popping up separate standalone
forms?

And why twelve related forms? It's possible - I've had a few forms with that
many - but it may not be your best design. What are the tables involved and
how are they related?
 
John W. Vinson said:
Whoa!

A Subform isn't "opened". It's a permanent part of a form, just like a textbox
or any other control. It *contains* some other form (typically for a table
related one-to-many to the mainform's table), but you don't need to "open" the
subform, and you don't need any code, and you don't need any buttons!

Do you really mean *a subform*? or are you popping up separate standalone
forms?

And why twelve related forms? It's possible - I've had a few forms with that
many - but it may not be your best design. What are the tables involved and
how are they related?
Click to expand...
Sorry, you are right. I should be more careful with my phrasing. They are
not subforms. they are standalone forms. There are so many because I am
creating a parish register for my Church and there are many parties involved
who want data points and they are somewhat disparate and many can be
considered individual. For instance, there are tables for baptism info,
confirmation info, death and burial info and organizationmembership info. All
distinct occurances. If you have thoughts on this I am all ears.

So what I am trying to do is replicate the action of the link table function
of the make form wizard.

These table are each related to the table (tblNames) 1- many. So tblNAmes to
tblBirth as a one - many. I have a parent key -nameKEY in tblNames and a
child key - nameSubKEY in each of the other tables and the relationship
window set up like this).

Does this help make it clearer? Thank you so much for your time.

Best,
Scott
 
Sorry, you are right. I should be more careful with my phrasing. They are
not subforms. they are standalone forms. There are so many because I am
creating a parish register for my Church and there are many parties involved
who want data points and they are somewhat disparate and many can be
considered individual. For instance, there are tables for baptism info,
confirmation info, death and burial info and organizationmembership info. All
distinct occurances. If you have thoughts on this I am all ears.
Click to expand...

I do have a church membership database that I'm happy to provide free of
charge. It doesn't have the baptism, confirmation etc. info but that of course
could be added.

However for organization membership, I suspect you have a table for each
organization - am I right? If so, consider how my Members database handles
this. There's a table of Members; a table of Lists (organizations, such as
choir, deacons, etc.); and a third table of Memberships. This third table has
a MemberID as a link to the Members table (who is in this organization) and a
ListID as a link to the Lists table (what organization are they in). If a
person sings baritone in the choir, is a Deacon, and is also on the Buildings
and Grounds Committee, he'd have three records in the table.
So what I am trying to do is replicate the action of the link table function
of the make form wizard.
Click to expand...

Well... why!? That's a wizard for creating forms. You're not creating forms, I
hope!
These table are each related to the table (tblNames) 1- many. So tblNAmes to
tblBirth as a one - many.
Click to expand...

That would imply that each person has many births. Sure, scripture says that
we must be "born again" - but that's not to be taken literally in this
context! Wouldn't the birth date and other birth record information be unique
to a person? if so, should it not simply be incorporated as fields in the
people table?
I have a parent key -nameKEY in tblNames and a
child key - nameSubKEY in each of the other tables and the relationship
window set up like this).
Click to expand...

"like this" - I would guess you tried to post a graphical image? Please don't.
Text is ample.
 
I took your advice and changed my table setup to consolidate data. You're
right that many tables had unique personal data. So now I have 8 tables (some
improvement).
1 personal info table
2 confirmation table
3 received as Episcopalian table
4 marriage table
5 Vestry table (church council)
6 organization table
7 org member

I also tried to implement your suggestion about organizations. I created the
org table and the org member table. the organization table has a primary key
(organizationKEY), a link to the personal info table (nameSubKEY), a link to
the Org Member table (orgMemberSubKEY) and a text field to hold the
organization name this person is a member of. I know this must not be
correct, because Icannot get it to work. I can work with one relationship,
but I have never understood how to do it with more than 1. This is a perfect
example.

I connected the personal table with the OrgMember table with the primary key
of the personal table and the personal table sub key in the org member table.
I also connected the org member table to the organization table with the org
member primary key and the orgmemberSubKEY.

So now I am lost as to what to do with these connections. I think the
personal table to org member table is a one to many relationship and the org
member to organization is one to many relationship. If so, I still cannot
figure out how to enter data for these tables.

Best,
Scott
 
I took your advice and changed my table setup to consolidate data. You're
right that many tables had unique personal data. So now I have 8 tables (some
improvement).
1 personal info table
2 confirmation table
3 received as Episcopalian table
4 marriage table
5 Vestry table (church council)
6 organization table
7 org member

I also tried to implement your suggestion about organizations. I created the
org table and the org member table. the organization table has a primary key
(organizationKEY), a link to the personal info table (nameSubKEY), a link to
the Org Member table (orgMemberSubKEY) and a text field to hold the
organization name this person is a member of. I know this must not be
correct, because Icannot get it to work. I can work with one relationship,
but I have never understood how to do it with more than 1. This is a perfect
example.

I connected the personal table with the OrgMember table with the primary key
of the personal table and the personal table sub key in the org member table.
I also connected the org member table to the organization table with the org
member primary key and the orgmemberSubKEY.

So now I am lost as to what to do with these connections. I think the
personal table to org member table is a one to many relationship and the org
member to organization is one to many relationship. If so, I still cannot
figure out how to enter data for these tables.
Click to expand...

You're on the right path, but you still have some misconceptions!

Organization membership is a classic "many to many" relationship. There are
three tables involved: a table of People (with *NO* membership or organization
information); a table of Organizations (with *NO* information about people);
and a third table of Memberships, which contains a link to the other two
tables. The foreign key goes in the "many" side table - that is, you would not
have the orgMemberSubKEY in the Organization table, but rather the
OrganizationKey in the org member table!

Also, I'm not clear why you need separate tables for confirmation and for
received as an Episcopalian. As I understand it, those events can happen only
once for a person; why not just have fields in the Person table for
ConfirmationDate and ReceivedDate? Is there more info that you record for
those events?

And again... isn't the Vestry just another organization?

I'd suggest the following structure:

PersonalInfo
MemberKey <primary key>
LastName
FirstName
MiddleName
DOB
<perhaps baptism date, confirmation date, other one-value personal info>

Organizations
OrganizationKey <Primary Key>
Organization <e.g. Choir, Vestry, etc.>
<any other info about the organization as a whole>

Memberships
MemberKey <link to PersonalInfo, who's a member of this org>
OrganizationKey <link to Organizations, what are they a member of>
Role <e.g. Chairman, Baritone, ...>
DateJoined
DateEnd <e.g. if someone is on the vestry for a three year term put the end
date of their term>

Marriages
<not sure what you want to track here; you'll need to have either a
MemberKey or other way to identify both the bride and the groom, marriage
date, marriage location, perhaps other info - what about a non-church
wedding?)

I'm a Presbyterian myself so I may not be up to speed on all the governance
issues, but I hope you'll forgive me my trespasses (and I'll agree to forgive
your debts!)
 
The recieved as episcopalian is a one time event. But you can be confirmed
many times. If you were confirmed as a prebyterian and you left to join the
mormon's you would be baptised and confirmed again. Likewise wth the Southern
Baptists. Not all chiurches recognize each other's confirmation or baptism.
In the Episcopal church we reconfirm you as part of the process of joining
the church. So you could argue that the original baptism does not count and
you do not need to reocrd it here, but yo were confirmed and it be that your
ancedsters will want to know? I see your point and I will think on it.

I understand the many to many concept and I am going to play with it
tonight. Any tips on how to structure the form to get the info into the
orgmember table.

I will let you know how it works out.

Best,
Scott
 
The recieved as episcopalian is a one time event. But you can be confirmed
many times. If you were confirmed as a prebyterian and you left to join the
mormon's you would be baptised and confirmed again. Likewise wth the Southern
Baptists. Not all chiurches recognize each other's confirmation or baptism.
In the Episcopal church we reconfirm you as part of the process of joining
the church. So you could argue that the original baptism does not count and
you do not need to reocrd it here, but yo were confirmed and it be that your
ancedsters will want to know? I see your point and I will think on it.
Click to expand...

Ok... so it might be very rarely used, but you could legitimately have a one
(person) to many (confirmations) relationship.
I understand the many to many concept and I am going to play with it
tonight. Any tips on how to structure the form to get the info into the
orgmember table.
Click to expand...

Sure - base a main Form on either the people table (if you want to focus on a
person and what organizations they're in) or on the organizations table (if
you want to focus on an organization and its members). You may well want to
have both forms! In either case you would have a Subform based on the
OrganizationMembers table; it would use the main form's table's primary key as
the Master Link Field and the corresponding foreign key as the Child Link
Field. On the subform you could have a combo box to select the person or the
organization, whichever is on the "other" side of the many to many
relationship from the mainform.
I will let you know how it works out.
Click to expand...

Don't hesitate to ask if you get bogged down!
 
I'm back. Everything has worked great. I have it down to 1 main person table,
vestry table and marriage table as well as the org member and org tables. A
lot easier to keep track of. The forms work and the data seems to be correct.
I just gave the data input table to my wife to break. And guess what. She
did. This is what she did. She typed a year in a date field.

Here is the puzzle. I have two date fields that I have a format of yyyy and
an input mask of "yyyy";0; in the table design. I have yyyy in the field
format tab format line. I only need the year. When I open the form and type
in a year I get an error that says "The value you entered isn't valid for
this field".

This does not make sense. I checked HELP on this and it says I did it right.
Any thoughts? Is this a known problem? Can I not do this?

Thanks for all the help, you are a God send. While I really appreciate the
offer of the db you built there is a cetain sense of satisfaction in doing it
myself. I will perservere.

Best,
Scott
 
I'm back. Everything has worked great. I have it down to 1 main person table,
vestry table and marriage table as well as the org member and org tables. A
lot easier to keep track of. The forms work and the data seems to be correct.
I just gave the data input table to my wife to break. And guess what. She
did. This is what she did. She typed a year in a date field.
Click to expand...

Cherchez le femme... said:
Here is the puzzle. I have two date fields that I have a format of yyyy and
an input mask of "yyyy";0; in the table design. I have yyyy in the field
format tab format line. I only need the year. When I open the form and type
in a year I get an error that says "The value you entered isn't valid for
this field".

This does not make sense. I checked HELP on this and it says I did it right.
Any thoughts? Is this a known problem? Can I not do this?
Click to expand...

A Date/Time field - regardless of format - is stored as a number, a Double
Float count of days and fractions of a day (times) since midnight, December
30, 1899 (since or before, actually; you can store dates from #1/1/100#
through #12/31/9999#). As such a Date field must contain a full date, a
precise point in time. 1937 is not a valid value for a Date field since it's
not a point in time!

If you want to store a year, store it in an Integer or Long Integer Number
field instead. You can use various builtin functions to go back and forth from
numbers to dates - type Ctrl-G to open the VBA editor, press F1 to get Help,
and search for help on Year(), Month(), Day(), DateSerial(), DateDiff() and
DateAdd().
 
I just keep on learning new things, but the things I think I know do not
work. I want to calculate the age and birthday of each person on the form. I
do not have fields for them in the table. I do not want to store something I
can calculate as needed. So I have query that has the lastname and birthday
of the person table and an expression for age that WORKS in the query: Age:
DateDiff("yyyy",[birthdate],Now())+Int(Format(Now(),"mmdd")<Format([birthdate],"mmdd"))
and this one for the birthday: Birthday: Format([birthdate],"mm dd") which
also works, but gives me the format 3/55 andI would like the format March
1955.

When I use the query fields as the controlsources on the form I get a #name
error in each field. There does not seem to be a recordsource line on the
unbound text box propertysheet to use for the queryname/field. I know this is
simple and I am just not seeing it.

Thanks,
Scott
 
I just keep on learning new things, but the things I think I know do not
work. I want to calculate the age and birthday of each person on the form. I
do not have fields for them in the table. I do not want to store something I
can calculate as needed. So I have query that has the lastname and birthday
of the person table and an expression for age that WORKS in the query: Age:
DateDiff("yyyy",[birthdate],Now())+Int(Format(Now(),"mmdd")<Format([birthdate],"mmdd"))
and this one for the birthday: Birthday: Format([birthdate],"mm dd") which
also works, but gives me the format 3/55 andI would like the format March
1955.
Click to expand...

I'm not sure I understand: do you want March 1955, or March 7 (for someone
born on March 7, 1955)?

For the month name and day, use

Format([birthdate], "mmmm dd")

For the month name and year, use

Format([birthdate], "mmmm yyyy")

See the online help for Format; you'll need to keep drilling down in the
levels of help, but there's a page with all of the date format strings that
you can mix and match.
When I use the query fields as the controlsources on the form I get a #name
error in each field. There does not seem to be a recordsource line on the
unbound text box propertysheet to use for the queryname/field. I know this is
simple and I am just not seeing it.
Click to expand...

A text box doesn't have a recordsource - a *form* has a Recordsource. If that
recordsource contains a field (whether a native table field or a calculated
field) you can use that fieldname as the Control Source of a textbox.

Alternatively, you can do the calculation directly in the Control Source of a
textbox on the form: just set the control source to

=DateDiff("yyyy",[birthdate],Date())+Int(Format(Date(),"mmdd")<Format([birthdate],"mmdd"))
to show the age, for example.
 
John,

Geesshhhhhh! Here is what I used in comparison to yours. The only differnece
is the use of NOW() instead of DATE().
=DateDiff("yyyy",[birthdate],Now())+Int(Format(Now(),"mmdd")<Format([birthdate],"mmdd"))
They both give the same result. Any reason to use one over the other?

Mine did not work because I had a space I did not notice. I foxed the space
and voila! Geesshhhhhhh!

I cannot thank you enough for all the help.

Best,
Scott

John W. Vinson said:
I just keep on learning new things, but the things I think I know do not
work. I want to calculate the age and birthday of each person on the form. I
do not have fields for them in the table. I do not want to store something I
can calculate as needed. So I have query that has the lastname and birthday
of the person table and an expression for age that WORKS in the query: Age:
DateDiff("yyyy",[birthdate],Now())+Int(Format(Now(),"mmdd")<Format([birthdate],"mmdd"))
and this one for the birthday: Birthday: Format([birthdate],"mm dd") which
also works, but gives me the format 3/55 andI would like the format March
1955.
Click to expand...

I'm not sure I understand: do you want March 1955, or March 7 (for someone
born on March 7, 1955)?

For the month name and day, use

Format([birthdate], "mmmm dd")

For the month name and year, use

Format([birthdate], "mmmm yyyy")

See the online help for Format; you'll need to keep drilling down in the
levels of help, but there's a page with all of the date format strings that
you can mix and match.
When I use the query fields as the controlsources on the form I get a #name
error in each field. There does not seem to be a recordsource line on the
unbound text box propertysheet to use for the queryname/field. I know this is
simple and I am just not seeing it.
Click to expand...

A text box doesn't have a recordsource - a *form* has a Recordsource. If that
recordsource contains a field (whether a native table field or a calculated
field) you can use that fieldname as the Control Source of a textbox.

Alternatively, you can do the calculation directly in the Control Source of a
textbox on the form: just set the control source to

=DateDiff("yyyy",[birthdate],Date())+Int(Format(Date(),"mmdd")<Format([birthdate],"mmdd"))
to show the age, for example.
Click to expand...
 
John,

Geesshhhhhh! Here is what I used in comparison to yours. The only differnece
is the use of NOW() instead of DATE().
=DateDiff("yyyy",[birthdate],Now())+Int(Format(Now(),"mmdd")<Format([birthdate],"mmdd"))
They both give the same result. Any reason to use one over the other?
Click to expand...

Well, Date() returns today's date; Now() returns the current date and time,
accurate to microseconds. You don't need the time to calculate an age in
years. I doubt there's any performance difference.
Mine did not work because I had a space I did not notice. I foxed the space
and voila! Geesshhhhhhh!
Click to expand...

Gotta watch out for foxes... said:
I cannot thank you enough for all the help.
Click to expand...

Glad to be able to help.
 
Back
Top