Access auto fill-in works in query but not for update

[Guest]

I have tried every combination of joins and definitions in the use of the
Auto-Fill select query without complete success. The query Auto-Fills fields
properly, but when it closes, the primary (imported) fields are not updated
while the other new data inputed is.

I have followed the HELP directions for Creating an Auto-Lookup Query to the
letter (many times) and cannot find what I am missing. I tried re-creating
the Northwind example with the same negative results upon updating.

Is there a key or switch somewhere that needs attention?

wem3rd

 

[Guest]

To get this to work, I had to use the following type of join: "Only include
rows where the joined fields from both tables are equal."

1. Create these two simple tables with these fields

ONE - OneID, Item, AutoFill
MANY - ManyID, OneID

2. Add a couple records to the table named ONE
3. Then put this correspnding SQL code into the grid and run it:

SELECT Many.ManyID, Many.OneID, One.Item, One.AutoFill
FROM One INNER JOIN Many ON One.OneID = Many.OneID;

4. When it comes up, put a number of an existing record (probably a 1) into
the OneID field, it should populate.

Let me know if it works.

Thanks,
Michael

 

[Guest]

To get this to work, I had to use the following type of join: "Only include
rows where the joined fields from both tables are equal."

1. Create these two simple tables with these fields

ONE - OneID, Item, AutoFill
MANY - ManyID, OneID

2. Add a couple records to the table named ONE
3. Then put this correspnding SQL code into the grid and run it:

SELECT Many.ManyID, Many.OneID, One.Item, One.AutoFill
FROM One INNER JOIN Many ON One.OneID = Many.OneID;

4. When it comes up, put a number of an existing record (probably a 1) into
the OneID field, it should populate.

Let me know if it works.

Thanks,
Michael

Michael,

I now have the join correctly assigned and have reworked the database a bit.
Not the design was good design! The added fields now update properly.
Thanks so much.

wem3rd

 

[Tim Ferguson]

I now have the join correctly assigned and have reworked the database
a bit.
Not the design was good design! The added fields now update
properly.

If you haven't noticed the difference, the "key" is whether you included
the PK or the FK in the query...

If you have the PK (of the joined table), then Access will be able to
update the fields in the joined table, but you won't be able to change
which record in the joined table is updated.

If you have the FK, then you will be able to change its value, but you
won't be able to change any of the related fields (no "autofill").

Various different situations need either approach. for example, you mght
have a list of students allocated to particular residences; in this case
you would need the Students.AllocatedTo (FK) field in order to change
which building they were allocated to, but the Buildings.BuildingID (PK)
to be able to edit details about the actual residence (and those changes
would then be reflected everywhere that particular building is
referenced). Above all, the users need to know what data they are
updating and how widespread the changes are!

Hope that helps


Tim F