RE-ASK: Editing Records in a Form Issue

[Julie]

I have done some searching, and I cannot find this error repeated anywhere. I
am thinking that when I let Access build the database it did something I am
not smart enough to find and change. So the following I hope is enough
information to at least get a conversation going on the issue. If I have not
provided enough information let me know.

I have 2400 records. My control source is a Query that is pulling from 4
tables. The form seems to be showing all the correct information for each
record from all the sources. It is the Editing and Adding data that is giving
me trouble.

If I change/add information on the form for one record, it will add that
information to all the other records, the only difference is: if a record
already had this information populated from the import it will not overwrite
it, it will skip and go to the next empty record. As of right now with the
imported records, they ALL have the following information already imported so
none are blank: primary key (auto generated) a unique id number that never
changes. a name (can change) a use code (can change) amount (can change) area
(can change), it is the: a date (can change) a document number (can change)
and notes (can change or be added to) plus three check boxes, that are not
all populated which I will need to do as the information comes available.
These are the ones that are giving me trouble. I also think it is not just
these fields, that if any one of the already populated fields was empty it
would be doing the same things, so it is the forms that were made.

The Tables: (Please note they were generated by Access when I did the import.)

TABLE NAME: PARCEL
TABLE FIELDS:
ID
PARCEL
LAND
C
C/F
ACRES
LOOKUP TO OWNER (GENERATED BY ACCESS)
LOOKUP TO APPLICATION (GENERATED BY ACCESS)
LOOKUP TO LEASE (GENERATED BY ACCESS)

TABLE NAME: OWNER
TABLE FIELDS:
ID
NAME

TABLE NAME: LEASES
TABLE FIELDS:
ID
LESSOR
LESSEE
COMMENCEMENT
TERMINATION
RENTAL RATE
LEASE NOTES

TABLE NAME: APPLICATION
TABLE FIELDS:
ID
APP DATE
DOCUMENT
IRS PROOF (CHECK BOX)
OTHER PROOF (CHECK BOX)
LEASE (CHECK BOX)
NOTES

QUERY THAT PULLS THEM ALL TOGETHER:

AGCOMPLETE

FORM: AGCOMPLETE WITH CONTROL SOURCE: AGCOMPLETE QUERY

The form has a command button that takes the user to another form that is
linked to another
query and will only show the current record that is showing on the main
form. I can add data
to it and change data and it will only change that record. But it also
filters to show only
that one record.

The other form I need to be able to edit is one I also go through them all
(not filtered).

Thank You
Julie

 

[Golfinray]

In my opinion, not being able to actually see your data, you have your query
wrong. In general, when I try to hook up more than two tables, I run into
this type of problem somewhere. I would start by tying two tables together
and then see if your form works. Go back and tie that new query to one more
table at a time. Make sure along the way that your queries are editable (have
the black arrow and asterisk at the bottom.) Good luck!

 

[Julie]

After gaining more insight I was able to build myself some tables and
relationships without using what Access did for me and it works just as it
was supposed to. I have since linked the forms to the new table/query and it
works fine.

Thank you for all you help.

 

[John W. Vinson]

After gaining more insight I was able to build myself some tables and
relationships without using what Access did for me and it works just as it
was supposed to. I have since linked the forms to the new table/query and it
works fine.

Just bear in mind - it is usually *not* appropriate to create a Grand Master
Query joining all your tables, and base a form on that query. Multitable
queries are often not updateable, and when they are, you may only be able to
update the "manyest" table, or you'll see confusing repeats.

Instead, use a Form (for the "one" side) with Subforms (including perhaps
sub-subforms) for the related tables.

you may already be doing so, just wanted to warn you!

 

[Julie]

Thank you for the input. I will look into how exactly I am doing it and
change accordingly if needed.