SubForm not linking to master form

DougKMiles · December 31, 2016, 6:16pm

I have a subform created from a query that includes a simple calculation, then a subsubform that totals those calculations.

I’ve been able to produce a total that updates with the master records in one early version of my form, but in a newer version I can get a total for the first master record, but it will not update when moving to the next master record. The SQL Command for both is identical, so I can’t figure out why one works and the other doesn’t.

Here’s the code for the functioning version:

SELECT SUM( “SubTotal”.“SubTotal” ) AS “Total”, “Trade”.“TradeID”
FROM “Trade”, “SubTotal”
WHERE “Trade”.“TradeID” = “SubTotal”.“TradeID”
GROUP BY “Trade”.“TradeID”

Here’s the code for the one that provides only the first master record total (column names are slightly different in the new version):

SELECT SUM( “Subtotal”.“Subtotal” ) AS “Total” , “Trade”.”Trade_id”
FROM “Subtotal”, “Trade”
WHERE “Trade”.“Trade_id” = “Subtotal”.“Trade_id”
GROUP BY “Trade”.“Trade_id”

Many thanks in advance for any thoughts or assistance.

Ratslinger · December 31, 2016, 6:42pm

Your problem is probably a setting in the subForm or subsubForm property on the data tab. Make sure Analyze SQL command is set to Yes.

In the future (or if you are still having a problem) be sure to include just where the problem is - Form, subForm, subsubForm; how linked, etc. I only had an idea of your problem because of a previous question.

DougKMiles · December 31, 2016, 7:13pm

Thanks once again for the rapid response Ratslinger, (or RRR for short!)

I figured it out. It wasn’t the Analyze SQL Command; but it was in the properties data tab – the Link Master/Slave field setting. I had been using “table”.“column” for the fields in there. When I removed the prefixes it worked. I guess it doesn’t want those table names because they’re already linked in the tables Relationship set-up?

Thanks again and Happy New Year!

Ratslinger · December 31, 2016, 7:18pm

Sometimes it’s the smallest things. Happy New Year to you also!

EasyTrieve · February 3, 2017, 6:00pm

“SubForm not linking to master form” can happen for one other reason as well.

If connecting to a MariaDB (or possibly a MySQL) database, there are two possible methods to connect using JDBC, and one of the methods works, and the other method does not.

You need to first select MySQL, then JDBC, not simply JDBC. If you don’t get this right, you will be able to see your tables, but if you try and create a form with a linked subform it won’t work. Also if you try and select Tools,Relationships..., you won’t be allowed to view or edit your relationships.

(Note for MySQL/MariaDB your database must be using the InnoDB database engine, which natively supports maintaining table to table relationship integrity without the use of transactions, and not something like the ISAM database engine, which is faster but doesn’t have native support for relationships without transactions.)