Relationships do not work as expected

[JohnB]

I have set up some tables as shown in the diagram below but I am having a problem with queries.

I want to end up with a continuous form that will hold a substantial number of fields (51) for each shipment (most of the data will come from other tables but 11 pieces of data come from the left hand side of the relationships shown above.

I have managed to succeed in designing a query that can show all fields for all the records where data exists, and even allow me to add data for all other fields in the DB, but when I try to add data to any fields which requires new records to be created in any of the left hand tables the corresponding ID (T_xx_XXId) in the Details Loading Table is not updated.

I have read up on this, but can not resolve the issues. All the PKs are Autonumber, which I suspect may have something to do with it at I believe this stops cascade update related fields, but try as I mite I have not been able to find out how to get around this.

Interestingly the form accepts the updates but I have found that once the form is closed and reopened the data is missing. (because the Details Loading table is not being updated.

The data is being stored in the correct left hand tables but the relevant ID's are not being updated in the Details Loading table. As such the query shows blanks when there is data (it's just not related correctly!)

In the query design page I was not able to relate the tables in quite the same way as above ( it wont let me do referential integrity) so one table has 1 to Many shown and the others just get an arrow.

I have succeeded in writing a macro that manages to write the Id to the Port Load table when the data is entered, set values , go forward a record , go back a record set value again!!!! stop macro. This is a messy way of doing things and I'm sure someone will have a much better way of doing it.

If you are able to help I would be most grateful.

Brgds John

 

[MarkK]

Aren't the tables on the left simply functioning as lookups? And if so, can't you bind a ComboBox to the main table field and set it's RowSource to return the data from the lookup?

 

[JohnB]

Thanks, The "document location" and "which invoice" tables could be set up as you suggest (I had not considered using a combo).

Unfortunately the laydays table hold dates (T_LD_Beglay and T_LD_Endlay) and a yes/no (T_LD_LaydaysAccepted).

It is the Layday fields that I have been working on for the last few days! And need the help on (I had assumed the other fields would give me the same issues.

 

[georgedwilkinson]

Why do you think that the Laydays will be any different? A combo box can display multiple fields from the associated lookup table.

 

[JohnB]

The laydays are not a lookup. I need to be able to insert the beginning and end dates and record that they have been accepted by the relevant party by making the Accepted field yes.


There are 3 sets of Laydays:

• Inital: Which are agreed at the start of the year and cover about 2 weeks spread,
• NarrowedOwner: a shorter period agreed with the Ship Owner,
• NarrowedPort: another short period this time agreed with the port.

Each set has a beginning date and end date and has to be accepted. The dates can all be different. My idea was to have one table with all three beginning, end and accepted fields feeding the details loading table with the ID relevant to each record in the laydays table.