Creating Forms

[Rick_DeSimone via AccessMonster.com]

I have spent many hours trying to create a form. I have created Forms that
are read only and others that flat out dont work. What I am trying to do is
create a form that updates 1 table but relates information from 2 other
tables(Customer Table and Asset Table into an Event Table). I have developed
queries so I can imput an Asset Number(Primary Key of Asset Table) and
retrieve asset and customer information. When I put everything together in
the Form I get either just a read only Form or if the Form does accept data I
get an error message(that information in one table has to be in a related
table).

 

[Guest]

First make sure that you have joins between the tables defined in the
Relationship Window. If you can enforce Referiential Integrity, that would be
great.

Then you need to describe how the tables are related to each other. I'm
guessing that the Event table might be a joining or bridging table between
Customer and Event. However without knowing for sure, I wouldn't want to lead
you down the wrong path.

 

[Douglas J. Steele]

For problem 1 (read-only form), are you certain that the underlying query is
updatable? See
http://msdn2.microsoft.com/en-us/library/aa198446(office.10).aspx for a
discussion of why queries may not be updatable. (Another possibility is that
you might have set the form's AllowEdits property as False.)

For problem 2 (missing data from related tables), the implication is that
you've turned on Referential Integrity, which does require that data exist
in the parent table before you can use it in the child table.

In general, monolithic forms that update multiple tables at once aren't that
practical. Typically you should use a form with a subform. Take a look in
the Northwind database that came with Access, specifically at the Orders
form, which uses the Orders Subform form as as subform. It handles updating
the Orders (from the Orders form) and Order Details tables (from the Orders
Subform form), while also pulling in data from the Products table.

 

[Rick_DeSimone via AccessMonster.com]

For problem 1 (read-only form), are you certain that the underlying query is
updatable? See
http://msdn2.microsoft.com/en-us/library/aa198446(office.10).aspx for a
discussion of why queries may not be updatable. (Another possibility is that
you might have set the form's AllowEdits property as False.)

For problem 2 (missing data from related tables), the implication is that
you've turned on Referential Integrity, which does require that data exist
in the parent table before you can use it in the child table.

In general, monolithic forms that update multiple tables at once aren't that
practical. Typically you should use a form with a subform. Take a look in
the Northwind database that came with Access, specifically at the Orders
form, which uses the Orders Subform form as as subform. It handles updating
the Orders (from the Orders form) and Order Details tables (from the Orders
Subform form), while also pulling in data from the Products table.

I have spent many hours trying to create a form. I have created Forms that
are read only and others that flat out dont work. What I am trying to do

[quoted text clipped - 8 lines]

get an error message(that information in one table has to be in a related
table).

Douglas
Thanks for the response. While I was waiting for your feedback, I redefined
my relationships between tables. In doing so I now get the form to accept
data, not all the data is updating the tables

 

[Rick_DeSimone via AccessMonster.com]

First make sure that you have joins between the tables defined in the
Relationship Window. If you can enforce Referiential Integrity, that would be
great.

Then you need to describe how the tables are related to each other. I'm
guessing that the Event table might be a joining or bridging table between
Customer and Event. However without knowing for sure, I wouldn't want to lead
you down the wrong path.

I have spent many hours trying to create a form. I have created Forms that
are read only and others that flat out dont work. What I am trying to do is

[quoted text clipped - 5 lines]

get an error message(that information in one table has to be in a related
table).

Jerry

Thanks for your rsponse. While I was waiting on your respose, I changed the
relationships between the tables. I deleted the relationship I had between
the Customer Table and Event Table to relate the Customer Table to the Asset
Table and the Asset Table to the Event Table. In doing this I was able for my
form to accept the data, but the customer data is not transfering updated
into the Event table. I did go back with your suggestion about referential
integrety and changed the relationships to the way I had before. Still no
luck the Event table is not recording the updated customer data.

 

[Guest]

You need to create subforms on your main form so that new data will be
updated. I'm thinking that the Event table needs to have a subform. With the
proper relationships defined, the subform wizard should help create the
proper links.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

First make sure that you have joins between the tables defined in the
Relationship Window. If you can enforce Referiential Integrity, that would be
great.

Then you need to describe how the tables are related to each other. I'm
guessing that the Event table might be a joining or bridging table between
Customer and Event. However without knowing for sure, I wouldn't want to lead
you down the wrong path.

I have spent many hours trying to create a form. I have created Forms that
are read only and others that flat out dont work. What I am trying to do is

[quoted text clipped - 5 lines]

get an error message(that information in one table has to be in a related
table).

Jerry

Thanks for your rsponse. While I was waiting on your respose, I changed the
relationships between the tables. I deleted the relationship I had between
the Customer Table and Event Table to relate the Customer Table to the Asset
Table and the Asset Table to the Event Table. In doing this I was able for my
form to accept the data, but the customer data is not transfering updated
into the Event table. I did go back with your suggestion about referential
integrety and changed the relationships to the way I had before. Still no
luck the Event table is not recording the updated customer data.