Subforms and Master/Child Links. (1 Viewer)

Hello, I have very limited knowledge of access.

If it can do what I want can anyone tell me how, I believe it can be done using subforms but I'm just getting myself confused.

I work for a telephone service company.

I have a customer table, a telephone number(called CLI) table, and a registration table.

The customer table has a primary key called Account, which is related to the CLI table.
And the cli table must have one or more records per account. And the registration table has one or more record per cli.

So if someone uses this form. I want it so they can select the custID, and show only the clis relevent to that customer. And then when they select one of the CLI's be able to fill in the registration details for that one cli.

Thankyou anyone who replies, but please try to be fairly simple for me.

Liam Ingram XXX

I think I understand your structure, if not the exact mechanisms by which CLI is related to Registration.
However, that being said, as long as you have parent & child fields in the linked tables, you should be able to create subforms and Access will help you link them together. For example, you have [Account] as PK in your Customers table. If this is an autonumber, then there needs to be an [AccountNumber] field, formatted as Long Integer, in the CLI table. It it is instead a text field (say, AB-123-0789-R), use a text field in the CLI table. The two have to match or be compatible types.
The child field (in CLI) contains the same number or text, allowing Access to know which CLI records go with which Customer records. When you make your form, you will be prompted to link your subform to your main form via Access' best guess of what the linked field is. If that guess is wrong, you'll have to make sure it knows the proper parent/child fields. This sounds more complicated than it is, just follow the wizard.

The same thing applies to CLI->Registration, except in this case you will use the PK for the CLI table as the 'parent' field, and a corresponding 'child' field in the Registration table. You can nest subforms within subforms, although Access gets picky about continuous subforms within subforms.

Now, that being said, can you explain the difference between the Customer table and the Registration table? It sounds like you might have more tables than you need, if every CLI entry has one Registration entry.

HTH,
David R (please post back to here, not email, so anyone can see/help)

First of all David can I thank you for taking the time to look over my problem.

From what you have told me, the problem lies in when I created the tables.

I will sometimes have more than one registration record for each CLI because if the registration fails on the first attempt i would like to be able to do it again.

I am actually at work at the minute. I am in England you see and despite the time this site says I posted it it is really half past ten here.

So I am going to crack on and see if I can solve the problem.

Thanking you again.

To build on the relations of my table.

Customer table:
contains information about the customer.
Address, Contacts, Tarifs, and stuff like that. The PK is the account code assigned to the customer by the administrator. For instance the company is called JUST UTILITIES LTD. We would assign it the account JUL001.

CLI Table: Contains Fields purely based on the telphone lines of the customer.
Has a child field which links to the account code.Then the PK is the actual telephone number itself because this is always unique. Then the rest of fields are all based on options they want on each CLI.

Registration Table:
This holds information like, date attempt to register, Success/Failure, Comments on failure and stuff. The PK is just a autonumber. But as mention earlier may need more than one record for each telephone line.


Perhaps my problem lies within my relationships. Being honest with you I keep on seeing the "foriegn key" and dont know what it is or how to use it.

Thankyou for reading this, if you got this far without getting bored.


Kind regards, Liam

Actually I think your tables are set up correctly. I didn't understand the nature of your business before, but that was my density, not your lack of explanation.

Account Code (unique, obviously) should be the primary key on your main table, and then a regular text field (you can index it, but make sure Duplicates are OK) in your CLI table. PhoneNumber, in the same way, is the primary key (unique) of the CLI table, and a text field of corresponding size in your Registration table, to serve as the foreign key. Your registration table's PK is mainly internal, and can just be an autonumber, as you've got it.

Now you can set up the links in your Relationships table. This is accessible from the Tools menu when your main Database window is open. Draw links between each of the matching fields, and it prompt you to set up referential integrity. Check 'Yes' here, and probably Cascade Update and Delete. What those two things do is try to ensure that you don't leave orphaned fields in your subtables in the event that you delete entries/change primary key information in your main table. You should see a '1' next to the PK side of the join, and an Infinity sign next to the Child side. Save your relationships.
You may find you get error messages saying you cannot enforce referential integrity because there are records in table X without records in table Y. Make sure all your data is good so far (if you've already been entering data into this system), and try again. It can get maddening sometimes, but it's better than leaving them unchecked, especially because you're using non-autonumber primary keys. If you ever have someone typo a phone number or account code, you'll thank yourself later.

Now you can make your forms with few problems, hopefully. Talk to your end users about what structure works best, but what I would do is this:
Main form, Account data in the top half. In the bottom half, space for a Continuous Form (subform, really) holding one or more CLI entries. If your CLI table holds a lot of fields, use a Single Form view instead. I would make the Registration Form (subform) a Popup form, since you probably won't need to see it every time you pull up the account. Use the wizards to start with! They do a lot of the grunt work of starting up forms so you can just fine tune them later. The Insert Subform command is under the Insert menu on Form Design view.

Be careful about letting the Record Navigation buttons for the subform and form get too close together, you can easily confuse your users. Don't be afraid to scrap a form and start over, it'll take less time than fixing erroneously entered data later.