dynamic update of primary key

[Aslan]

I have two tables linked by a text field defined as the primary key in each table. I want to dyanamically update the secondary table by typing information in one of the fields (not the primary key) and have the primary key automatically populate to match the linked table. I know I have done this before (a long time ago in Access 97) and it worked nicely.

For instance, my primary key in my main table might be 899456 and there is no associated field in the secondary table. However, since the datasheet view shows me fields from both tables, when I would type in one of the empty fields from the secondary table (which had no related records in the primary table) as soon as I went to another record, it would create a record in the secondary table with a matching primary key (899456).

This allowed me to add notes that would link to the primary table without having a record for every possible primary key in the primary table. I need to have this ability since the primary table is updated daily from an independent data source and I need my notes to stay in sync via this query and the secondary table which stores notes.

I don't know if this makes enough sense to respond to but I'd sure appreciate any thoughts.

Thanks!

Paul

 

[SteveSchapel]

Paul,

You can make a query based on both tables, and a Left Join from the primary key of the primary table to the primary key of the secondary table. Base your form on this query. Then, when you enter the data in the secondary table's "other field", the secondary table's primary key value will automatically populate.

 

[Aslan]

Steve, Thanks for the reply on this.

My query is as follows:

SELECT Orders.[Selling Loc], Orders.[Enter Date], Orders.[Order #], Notes.Order, Orders.[Customer Name], Orders.[Ship To Name], Orders.[Customer PO No], Orders.[Total Order Amt], Orders.[Who Enter], Orders.Status, Orders.[Ship to City], Orders.[Ship to State], Notes.Tech, Notes.Notes
FROM Orders LEFT JOIN Notes ON Orders.[Order #] = Notes.Order
ORDER BY Orders.[Selling Loc], Orders.[Enter Date];

[Order #] is the Primary Key for the Orders table
[Order] is the Primary Key for the Notes table
- both of these are text fields

When I attempt to enter a value in Notes.Notes and then go to a new record I still get the error message "Primary key cannot contain a null value".

Any other ideas on how I can make this work?

Thanks,

Paul