Using a Button to activate Subform. Relationships

users
#1

Hi All

You will have seen my question to Noel about Relationships and his reply.

I just tried again as he suggested ...

Open Relationship Window.
Add my two Tables
Click on 'New Relationship' (There are no fields listed)
Select "id" in Main Table
Select "id" in Second Table
Press Close (Window does NOT close so press 'X' to close it)
(There are NO lines or any other change to the Tables display in the Relations Window)
Press Save
Close Window
Press Save again
Open Main Form

But I'm afraid the sub-Form does _NOT_ work still!!

Can anyone see the error I can't see??

Thanks a lot.

IanW
Pretoria RSA

#2

Hi All

I posted the question below on the 13th but have seen no comments or replies.
Surely someone has an idea or a thought on this??

Thanks a lot.

#3

You are using Base with MySQL. Tools > Relationships opens the Relation Design dialog. You can use this dialog if you have an embedded database but not when connected to a MySQL server. That is why you are having problems.
      There are 2 ways to define the relationship between a form and its sub form: Tools > SQL, and edit the form, itself.
      1) Tools > SQL: For this you will have to read your MySQL user manual to see how to define the foreign keys using SQL. (It is probably similar to how the HSQLDB user guide says you should do this.) Perhaps you could get the information from "Base Tutorial" by Mariano Casanova. It discusses how to use Tools > SQL.

      2) Edit the form:
         a) Open the form in Edit mode.
         b) Click one of the controls in the sub form.
         c) Open the Form Controls toolbar. (View > Toolbars > Form Controls)
         d) Click the Form tool to open the Form Properties dialog.
         e) Click the Data tab.
         f) Link master field: Click its ellipse. (The Link fields dialog opens.)
         g) Select the foreign key of the sub form table in the left drop down list.
         h) Select the primary key of the main form table in the right drop down list.
         i) Click OK closing the Link fields dialog.
         j) Use the ESC key to close the Form Properties dialog.
         l) Save and close the form.

--Dan

#4

Bear in mind as well that if you are using MyISAM as your table memory storage engine then referential integrity will not be enforced between tables. The switch to use the InnoDB storage engine by default, which supports intertable relations and referential integrity, was only announced fairly recently, so depending on your version of mysql server, you may, or may not, have InnoDB as your default engine.

In other words, if your default storage engine for your tables is MyISAM, the only way to enforce relations is through your own programming logic or that of the application you are using to access the tables. AFAIK, the various connector drivers know nothing about this, i.e. you can not enforce referential integrity through an option in the driver.

http://www.yolinux.com/TUTORIALS/MySQL-InnoDb-Transactions-ForeignKeys.html

Alex

#5

Thank you Dan and Alex!!!

This is the kind of help I was needing to understand the problem. I knew one of you DB "Boffins" would point me in the right direction.

Dan I will try your method but first I must investigate what Alex says about MyISAM and InnoDB.

So - That leaves me now with two questions ...

1) Dan - can you give me an address/link for the "Base Tutorial" by Mariano Casanova you mention??

2) Alex - I have checked my DB with MyAdmin and I'm on MySQL 5.1.55 and the "default Style" is listed as MyISAM. So can I just change this with MyAdmin or is it a more deep seated problem??

VERY many thanks for the help

IanW
Pretoria RSA

#6

Hi Ian,

2) Alex - I have checked my DB with MyAdmin and I'm on MySQL 5.1.55 and
the "default Style" is listed as MyISAM. So can I just change this with
MyAdmin or is it a more deep seated problem??

You can change the default engine on a per table basis (this avoids you
having to fiddle with your my.cnf file), see the relevant command in the
mysql official documentation, including some considerations to bear in
mind before making such a switch :

http://dev.mysql.com/doc/refman/5.1/en/converting-tables-to-innodb.html

Alex

#7

1) Dan - can you give me an address/link for the "Base Tutorial" by Mariano Casanova you mention??

If you search for "Base Tutorial by Mariano Casanova" (without the quotes), you will get the link you need. When I did, the top link opened the PDF of this document.

--Dan

#8

Thank you both (Dan and Alex)

I will follow these routes.

Appreciate the help!!

IanW
Pretoria RSA

#9

Hi :slight_smile:
There seemed to be tons of things at
http://wiki.openoffice.org/wiki/Database
that might be worth looking into
Regards from
Tom :slight_smile:

#10

Thanks !!

You're a Samaritan !!