[Solved] Key Fields not updating some tables of single form

[Dalke55]

I have a form with multiple tables setup as sub forms with most tables updating the key and foreign key fields on six out of eight tables and two tables not updating links. Tables 'Entry', 'Time Chart', 'Pressure Indicator', 'M&M', and 'TNT' update their respective ID fields, where table Run and Candle which is a Table grid do not. I also noticed the table Time Chart did not fill in the foreign fields associated between the tables. If I could get help with the table linking in the form.

The field TCHRTID in table Candle was entered manually.

I have attached a sample file and a relation document.

Thank You in advance for any help and advise.

[UnklDonald418]

What you have presented is very confusing. Your form document frm-FOREX-BKTST-03 does not reflect the table relationships you have established.
For instance, looking at SubForm-PRESIND the Slave link is PIID and the Master link is TCHRTID on SubForm-TIMECHRT. That contradicts your relationships diagram where tbl-Time Chart.PIID links to tbl-Pressure Indicator.PIID.
I see a similar issue with SubForm-M&M and SubForm-TNT
Either your relationships diagram is wrong or you form design is wrong. I suspect it's the form.

On the other hand, the Slave link for SubForm-Candle is CNDLID and the Master link is CNDL-CLRID on SubForm-TIMECHRT does match your relationships diagram. But I don't find anywhere on your form that allows entry/selection of a value for the CNDL-CLRID on SubForm-TIMECHRT. The table control on SubForm-Candle allows entry of new data into tbl-Candle but it doesn't provide a way to enter a value into CNDL-CLRID on SubForm-TIMECHRT.

If as I suspect the problem is with the form design then each of the foreign key fields in tbl-Time Chart needs either a list box or other numeric control on SubForm-TIMECHRT. so that an entry can be made.
Only then can the sub forms be properly linked.

[Dalke55]

Thank you for your reply. After reading it the first thing that struck me was, did I attache the correct relation chart; I did not. I have attached the most resent relations chart.

I will try to explain the process that this form is made for to try too help with the flow to make sense of the form layout.

The top line is made up of the first three tables 'CUR-PAIR' 'Entry' and 'Time Chart' once these three groups are addressed you move on the 'Pressure Indicator' table (Left Mid area), 'M&M' table (Middle area), 'TNT' table (Right Mid area). Then the next table to be addressed is going to be the 'RUN' table and then 'Candle' table. I am uncertain whether the 'Support Resistance' table is coming into play.

I had also made some minor changes to the data base before I read your reply and have attached version 4..

Besides a poor design, I struggle with the Key fields and how they are to be populated to link the tables. If you could take a new look at the attached I will go from there.

Thank You again for your assistance.

[UnklDonald418]

I'm still struggling to understand what you are doing.

Your original post indicates that frm-FOREX-BKTST-04 is working to your satisfaction for SubForm-PRESIND, SubForm-M&M and SubForm-TNT. Looking at your form it appears you have a 1 to 1 relationship between tbl-Time Chart and the 3 tables tbl-Pressure Indicator, tbl-M&M and tbl-TNT.
The effect of that would result in a relationship diagram something like.

But looking at your relationships diagram there is a n to 1 relationship between tbl-Time Chart and the 3 tables tbl-Pressure Indicator, tbl-M&M and tbl-TNT.

If frm-FOREX-BKTST-04 is correct and you DO have a 1 to 1 relationship, there is a potential problem with your table design. It presumes that the auto value primary keys in tbl-Pressure Indicator, tbl-M&M and tbl-TNT will remain synchronized with the value in TIME-CHRTID in tbl-Time-Chart which is a risky assumption. Anything that causes them to lose synchronization can result in a mess that can be very difficult to sort out.
For a 1 to 1 relationship, it will save you some serious grief if you move all the fields except the Primary Key fields to tbl-Time-Chart and eliminate those 3 tables.

Also, looking at your relationship diagram I see a 1 to 1 relationship between tbl-CUR-PAIR and tbl-Entry and another between tbl-Entry and tbl-Time-Chart that will have similar problems.
Looking again at frm-FOREX-BKTST-04 it appears that you have a list box for selecting a value from 1st-Currency-Pair and placing it in tbl-CUR-PAIR.
I don't see the point of tbl-CUR-PAIR, it would be simpler to add an integer field to tbl-Entry that would be a foreign key related to CUR-PAIRID in 1st-Currency-Pair. Use that integer field as the Data field in the list box.
Then change the List content of the list box control List Box CUR-PAIR to

Code: Select all

SELECT "CUR-PAIR", "CUR-PAIRID" FROM "lst-Currency Pair" ORDER BY "CUR-PAIR" ASC

When you make a selection from the displayed list of CUR-PAIR options from 1st-Currency-Pair the related CUR-PAIRID will be saved in the new integer field.

It appears to me that you could use a single table to replace tbl-Entry, tbl-Time-Chart, tbl-Pressure Indicator, tbl-M&M and tbl-TNT.
I am also wondering if tbl-RUN might be better included in the enlarged table.

I'll reserve discussion of tbl-Pressure Indicator and tbl-Support-Resistance until the above mentioned issues have been resolved.

[Dalke55]

I made the changes as suggested and now have a working form. I setup a 1 to 1 relation between 'Entry' (combined as suggested) and Time Chart' (also combined as suggested). I put a combined key in table 'Candle' and set a 1 to n relation ship. The form is functioning as expected.

Thank you for helping on this part of my project. I waisted so much time in not posting this sooner.

[UnklDonald418]

Congratulations on getting your database working!
Be sure to backup frequently, particularity when using an Embedded Base database (database type is displayed on the bottom line of the main database window). That type of database is prone to data loss when the .odb file becomes corrupt. The risk of this happening seems to be increased for laptop users.
A JDBC (Split) Base database significantly improves reliability by moving the tables from inside the .odb file to files in a sub-directory. For a discussion of the pro's and con's of a JDBC connection, and a wizard for creating the connection see
[Wizard] Create a new 'split' HSQL 2.x database

[Dalke55]

Thank you, i will read that document.