Form based on three tables won't update (Access 2000)

[ssampier]

I created a form using the Form Wizard. I selected our main table as the source table. I added the fields I need from the main table, then I added two different fields from two separate tables.

The form moves from record to record just fine. I cannot, however, modify, any data in any record. The form doesn't let me change or modify any data (the add record option is greyed out).

Are all forms based on a query read-only? Is this is the intended behavior, how can I view and modify these separate tables at once?

I appreciate your help

 

[PHV]

Be sure to use INNER JOINs and to select the Primary Keys of the 3 tables to have a chance the query becomes updatable.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ssampier]

I updated the form to include the three primary keys. I went into the RecordSource property. Both joins are inner joins. I saved the query and tried to update the form. No luck.

To reiterate, I have three tables related through the primary key phone number. The first table is customer information (name, address, etc). The second table is a junction table of email addresses and the third contains billing information (bill phone number, etc). Both tblEmail and tblBillingInfo contain the phone number as a foreign key.

If needed, I can use the customer information and email address (tblCustomer and tblEmail). I could access the billing information in a separate table which is rarely updated.

 

[traingamer]

Sounds like you really need to have a form with subforms. Which of the possibly many email addresses would be displayed? Can there be multiple billinginfo records for a single customer?

Let the form wizard build a form with subforms for you. Define the relationships and run the wizard. Modify as needed.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[ssampier]

I appreciate your help. I ended up creating the form from one table. Then I added a email subform and it worked.

It was frustrating that the other method *should* have worked, but didn't for some reason. I didn't want to mess with it, so I'll just use this method for the moment.

Again, thanks for your help.