Filling in a text field

[terwilligerjones]

I'm such a clueless noob that I can't even determine what I should be searching for. Hopefully, someone can set me on a path such that I can do more thorough research. I'm getting nowhere right now.

The project is "The giving tree". Each Christmas season, my charity collects names of kids who won't have a Christmas gift. The database is MySQL, and we're using BASE as the front end. We collect parent(s) name(s), address and such like in tblParents:

Code: Select all

Drop TABLE tblParents;
CREATE TABLE tblParents 
(ID int(10) PRIMARY KEY NOT NULL auto_increment,
AFirst VARCHAR(25) NOT NULL,
ALast VARCHAR(25) NOT NULL,
ASSN VARCHAR(15) NOT NULL,
BFirst VARCHAR(25) NULL,
BLast VARCHAR(25) NULL,
BSSN VARCHAR(15) NULL,
Address VARCHAR(25) NULL,
Address2 VARCHAR(25) NULL,
City VARCHAR(25) NULL,
Zip VARCHAR(11) NULL,
Phone1 VARCHAR(14) NULL,
Phone2 VARCHAR(14) NULL,
Type = InnoDB;

We collect information about the child in tblChildren:

Code: Select all

Drop TABLE tblChildren;
CREATE TABLE tblChildren 
(ID int(10) PRIMARY KEY NOT NULL auto_increment,
ParentID int(10) NOT NULL,
Identifier VARCHAR(1) NULL,
First VARCHAR(25) NOT NULL,
Last VARCHAR(25) NOT NULL,
Age int(10),
Gender VARCHAR(1),
SSN VARCHAR(15) NOT NULL,
WishList VARCHAR(250) NULL,
NeedsCoat tinyint(1),
ShoeSize VARCHAR(10),
PantSize VARCHAR(10),
ShirtSize VARCHAR(10))
Type = InnoDB;

First, I can't get relationships, even though I'm specifying InnoDB tables. That's probably a separate question.

Secondly, the relationship between kids and parents is many-to-one. I specified datasheet view in the form, but filling in a 250 character wish list in datasheet form is pretty messy for the volunteers. What I'd like to happen is when the focus moves to a child in the datasheet subform, a sub-sub form (I know -- I told you I was a clueless noob) or something should open where we could format that data neater than we can in a datasheet view.

I don't even know how to pose the question to the search engines, as I lack the terms to search. Where could I start?

terwilliger jones

[Villeroy]

Quick shot in the dark: Each child has one or zero mother as well as one or zero father.

Children.Pa Null [n--1] Parents.ID (where gender=1)
Children.Mom Null [n--1] Parents.ID (where gender=0)

Orphans = SELECT * FROM Childen WHERE Mom IS NULL AND Pa IS Null
Listbox "Father" source: SELECT Name, ID FROM Parents WHERE Gender=1
Listbox "Mother" source: SELECT Name, ID FROM Parents WHERE Gender=0

[terwilligerjones]

Um...I didn't follow you, and that means I probably didn't explain the problem well.

The problem arises when a volunteer fills in the form for a family.. In the main form are the fields from tblParents -- parent(s) names, address, phone, etc.

The fields from tblChildren appear in datasheet view in the subform. The family may have one child, or fourteen. We don't know ahead of time, of course. The children are identified in the field "Identifier" by letters A-Z Filling in name, age, gender and such like isn't a problem, but when we go to fill in the wish list (what the child wants for Christmas), we have a 250 character field. Filling that in in datasheet view is going to involve a lot of horizontal scrolling. Some of our volunteers are uh...mature. That, I fear, will throw them.

Ideally, I wouldn't use a datasheet view at all. I'd use a subform where things are laid out nicely, but I don't know how to do that in a many-to-one relationship.

terwilliger