Simple forms design question

Topic

New Reply

I need a little guidance on setting up a database. I’ve done this stuff before, but I’m rusty. If I can get some guidance and direction, I think I can do what I need to do. I just don’t know where to start.

Basically I need to set up a form for one table that includes selected data from another table.

I have a 75-record table named tblShips. Each record has a unique field named ID.

I have a form named frmShips, which gets its data from tblShips. It shows most of the data for one record from tblShips. There’s a lot of extra space on the form.

Each ship is associated with from 1-5 people. The people are all in an 80-record table named tblPeople. Each record has a unique identifier field named Handle. The records contain contact information (name, address, phone, etc.) for a given person.

Each person in tblPeople is associated with at least one ship. A few of them are associated with several ships.

I haven’t done it yet, but I can put fields in tblShips to hold the Handle of each person associated with the ship. I’d also like to include a field showing the title that each person holds on that ship. (The person with Handle john.brown can be President on one ship, and Treasurer on another)

I want frmShips to show the contact information for all the people associated with the ship from which it gets its data (max 5 — there is plenty of room on the form to show this data).

How do I get there from here? I think a subform may be involved, but I’m not sure. If VBA is involved, I can probably handle it after a little memory refreshing.

Reply | Quote

Replies

>>I haven’t done it yet, but I can put fields in tblShips to hold the Handle of each person associated with the ship.<<

NO! This is the absolute WRONG way to do it! You need to create another table, call it tblShipHandle or something like that. It can have as few as 3 fields: ID (from the Ship), the Handle, and the position. The information would be presented on frmShip as a subform (the parent and child fields are the ID field). Use a combobox to select the person, and a textbox to enter the position (Don't show the ID field).

Reply | Quote

Ah, yes! It’s coming back to me now.

In the subform, I want to show all the people associated with the ship, all at one time. There’s enough room on the form to do that.

The main contact for that ship should appear first (regardless of his title), the second contact should appear second, etc. Five contacts would be the absolute max for any ship.

Maybe tblShipHandle could have a field showing where the person should be listed for that particular ship. (A person could be #1 for one ship, but #3 for another.)

Does that sound reasonable?

Reply | Quote

Yes, you can do that. The subform containing the info from tblShipHandle will be a continuous-form or a datasheet, so you can see everyone associated with that ship. When you set the master/child linking fields in the subform properties, this limits the contents of the subform to only those handles associated with that ship.

Reply | Quote

I will need to approach this step-by-step, hopefully fully understanding each step. I’ve been reading about subforms in Access 2010 Inside Out and Access 2010 All-in-One for Dummies. I understand only a little bit of it, but this should improve as I work and re-read.

I’ve just designed frmContactInfo, which gets its data from tblPeople and displays it in a compact way. For each person involved, tblPeople has a Handle (john.smith) plus a bunch of contact information. I’m guessing that I will ultimately be able to use frmContactInfo in constructing a subform or subsubform that I can use in my main form.

I think I’ve got the tables I need, and at least a couple of the forms. Tips on the next step would be appreciated. I think it might include setting up relationships between tables.

Reply | Quote

Lou,

Check out the Access templates that are available when you create a new database. They are great for demonstrating form design techniques. I suggest the Invoice Template. Also make use to select the DESKTOP database versions and not the web database version.

Reply | Quote

I don’t immediately see the Invoice template. There are tons of them, in tons of folders.

In the meantime, I’ve set up tblShipHandles, which lists the people who are associated with the ships. It has four fields: ID (the IDs of the ships with which the handles in this table are associated), Handle (the handle of a person in tblPeople who is associated with this ship), Seq, (the sequence from 1-5 in which this person should be listed in the main form with the people on this ship), and Position, this person’s title on this ship (VP, Treasurer, etc.)

I think my next step is to set up some one-to-many relationships between tblShips, tblShipHandles, and tblHandles. I’m not sure what to do or how to do it. Guidance would be appreciated.

This project might just be the sort of thing where it’s easier for somebody to just do it than for us to go back and forth until I’ve figured it out. Maybe easiest would be for me to upload a database with dummy data and just let somebody up here do the relationships, etc. I have a feeling it’s very easy for somebody who knows what they are doing.

Reply | Quote

Lou, I just sent you a private message.

Reply | Quote