I have a database that contains 9 s 1

[rlh777]

I have a database that contains 9 subforms. The main form is called, "Customer Information". This form is linked to the tbl_Customer. Two of the columns in this table are Customer ID and Customer Name. When I open the "Customer Information" form and select a customer, certain information defaults throughout the form. I would like certain information to default to my 9 subforms as well. All subforms have a field called "Customer Name". I would like to be able to open the Cusotomer Information form, select the customer, and when the information defaults, I would like to take it a step further and have the customer name go to each subform. Right now the customer id is the only thing that goes. Any thoughts? HELP!

 

[sonso]

You should link the subforms with a customer ID #, not name, as this will cause the tables to be out of sync (if you expect the names to always be the same). You could use the After_Update event and post to each subtable, but that would be inefficient.

 

[CJTyo]

How are all your subforms linked to the main form?

C *~*~*~*~*~*~*~*~*~*~*~*~*
Insanity is a matter of Perception.

 

[JeremyNYC]

First off, NINE SUBFORMS? Are you mad? Does it take a week to load this form? What do these forms hold? My guess is that there's a bettter way to organize this.

Next, why not just add that field to the parent/child field properties for each of the subforms?

Jeremy =============
Jeremy Wallace

http://www.AlphaBetCityDataworks.com

Designing, Developing, and Deploying Access databases since 1995.

 

[rlh777]

The nine subforms are necessary. All the information in the subforms are tied to the customer information. There are only 2343 records total. The customers will always keep their same id number and never change (unless their deleted). New customer can be added. The tables will grow, but the form opens VERY quickly. I don't know if the subforms are linked to the main form. Should they be? If your talking about what field do they have in common, that would be customer id. The customer ID information does default on the tables of each subform, I was just trying to get the customer names to default. If it's not a big deal, I won't do it.

 

[JeremyNYC]

Sorry for coming off so harsh the first time around, it just sounds a little odd to me.

Can you post your table structure? We might be able to make things easier for you.

Also, it would be easier if all of your forms and tables shared the customer number, not the customer name.

Jeremy =============
Jeremy Wallace

http://www.AlphaBetCityDataworks.com

Designing, Developing, and Deploying Access databases since 1995.

 

[CJTyo]

If the main and sub forms all have the customer id in common, and it's the primary key so it's a unique value - you'll want to look up synchronization between main-subforms. Look at your Child/Master properties for the forms to make sure they're linked properly. If the customer id is the linking field and all forms share that field AND if all the subforms have the customer name as a field as well, you should be able to get all the subforms to display the customer name. WHY you would want all these forms to display the same name is totally beyond me, but you can do it if they're all linked and contain the field. I'd think just having the name on the main form would be okay.

C *~*~*~*~*~*~*~*~*~*~*~*~*
Insanity is a matter of Perception.

 

[CJTyo]

Though Jeremy is right, if you post your table structures I'll bet we can help you streamline things in some fashion. 9 subforms smacks of non-normalization to me in a big, big way. And trust me, proper normalization (that's table design and relationships) makes ALL the difference in how easy your life as a DB developer is.

C *~*~*~*~*~*~*~*~*~*~*~*~*
Insanity is a matter of Perception.

 

[rlh777]

Let me rephrase some of my earlier posts. I have a database that has a customer information form. On that form, I used tab control. I have 9 tabs created and each tab contains it's own subform. Each one of these forms pulls it's data from it's corresponding table. The one field that all tables and forms have in common are their customer id and the customer name. What happens is this...I open the customer form, enter in my customer name and loads of data for various fields defaults. From here, the first tab is visible. Therefore, I enter my necessary information. Next, I click the second tab and enter that information. This process is done until I have all my information entered into all nine forms. When complete, I click the save button. My problem right now is this...After I save my data, I go to the tables that are assigned to each subform. Some of the forms are adding in the customer id, some are not. The column for customerid is present in all tables. The properties are the same. I need the customer id to default into all of the tables I have entered information into. Can anyone help? Signed...desperate.

 

[CJTyo]

Is CustomerID the primary key in each table? That is probably your problem. You'd want each table to have an autogenerated primary key that is NOT the CustomerID, that way the CustomerID field will behave just like your other data fields and be populated with whatever you enter in the Tab form.

C *~*~*~*~*~*~*~*~*~*~*~*~*
Insanity is a matter of Perception.

 

[rlh777]

C,

Some tables have the system generated autonumber id as the primary key and some have two keys assigned. One being a system generated autonumber and a secondary of the customer id. Should I add two primary keys to them to make them work?

 

[sonso]

Primary keys are a way of ensuring uniqueness. It makes no sense to combine 2 fields if one is unique. The problem with autonumber primary keys is that you will have to guarantee uniqueness *programmaticly*. This can cause a lot of programming overhead, which is often unnecessary. If, particularly, you are going to interact with the data through forms, then the JET database engine will take care of linking and filling in the necessary values "behind the scenes".

 

[rlh777]

I will be happy to assign my primary key to each customerid field in the tables, the only problem is that it won't allow me to use the feature of Indexed: Yes (Duplicates Okay). I need the capability of adding duplicates to these forms. One customer form with no duplicates and subforms that allow duplicate records.

 

[CJTyo]

You do NOT want your CustomerID field to be the primary key, no duplicates. Especially seeing that it's being entered into numerous tables.

Now... I've a question about how you're actually putting it in to the tables. Is it being entered on each Tab manually or is that field being filled in from the main form?

If you're entering it manually, that's bad. If you're auto-filling it from the main form are you positive on the subforms that you've got the correct field from the appropriate table? When you've a number of fields with the same name it's very easy to slap the wrong one on a form. For instance, instead of putting tab1.CustomerID you accidentally put main.CustomerID and THAT would explain why some of the IDs are being entered and some are not. ESPECIALLY if it's always the same tables that are NOT getting the data.

Make sense? Hopefully it's that easy to fix.

Also, make sure the tables are all related to each other properly. It's possible the ones not getting the CustomerID aren't linked to the main table.

C *~*~*~*~*~*~*~*~*~*~*~*~*
Insanity is a matter of Perception.

 

[rlh777]

What is the proper way to related the tables? I do not have any relationships established.

 

[CJTyo]

In the relationships view you add each table and then click and drag from one field in one table to another field in another table. A box appears asking about the type of relationship. Generally it's okay to accept the default. The only thing you'll want to consider is whether to use referential integrity (definitely check the HELP files and other threads here on relationships and referential integrity).

It sounds like you'll probably want all the subtables related to the main table by the CustomerID field. So you'd click on CustomerID in the main table, hold and drag to the CustomerID field in the first subtable and let go. Repeat for each subtable.

Having the tables relate to each other will make it easier to keep your forms synchronized (another term to search for in HELP and on these forums).

With proper table design and relationships you can actually get a HUGE amount of automatic synchronization in subforms. Also look up NORMALIZATION - and check out JeremyNYC's web site for his document on Normalization.

Hope that helps,
C *~*~*~*~*~*~*~*~*~*~*~*~*
Insanity is a matter of Perception.

 

[rlh777]

When I try to create the relationships, the relationship type appears as: Indeterminate. Because of it, I am unable to enforce referential integrity.

 

[CJTyo]

In the mainTable is the CustomerID field unique, no duplicates (having nothing to do with primary keys)?

Is the CustomerID field unique, no duplicates in all the subTables too?

C *~*~*~*~*~*~*~*~*~*~*~*~*
Insanity is a matter of Perception.

 

[rlh777]

The main table: Customer
The unique field is named: RCustomerID

"RCustomerID" is the unique field of all subtables also.

Relationship types for all is shown as: Inderterminate

All are spelled correctly.

Six of the tables default the proper ID information into their tables. Three do not.

 

[rlh777]

One more thing...

All are showing Yes (Duplicates Okay) When I change it to Duplicates: No. My subforms won't allow me to add multipal records.