[Solved] Inability to add/edit data in subform

[Hezza1506]

Hi,

I hope that I am not duplicating an earlier query from another user, but I have tried searching both here, in the wiki and generally online, and I can't find anything that gives a solution to my problem.

Firstly, my database is intended to track showcats, their show placings, which catteries they came from etc. It has been created from scratch in Base, not copied from elsewhere. The whole thing was created following the guidance found in one of the tutorials on the Open Office site - http://www.openoffice.org/files/documen ... %20III.pdf. I have created my tables, and each one has a primary key. I had created some forms that were based on views from queries, but couldn't add data and have been told that this is because data cannot be added to forms based on queries. So I decided to try again using forms and subforms rather than query/views.

Right. I have created a form and subform using the Wizard. The form is from my 'CatInfo' table, and my subform is from my 'Breeders' table. The two are linked through the 'Prefix' field, which is the Primary key for the 'Breeders' table.

Once I had completed the wizard, I saved the form and opened it in data entry mode. I can enter data in the main form without issue, and I can click into the text boxes within the subform, but cannot type anything in them. I have added a form control to the subform to see if that helps, but all of the options on that are also greyed out. I have checked that all options are set to allow data entry, and have checked that the relationship is set up correctly (it should be anyway, having been put in place by the Wizard).

So then I thought I would try adding a second subform manually to see if the outcome was any different. So I went to the form navigator, right-clicked on the mainform and added a subform, which I called AddBreed. I set the table for this subform to 'Breeds' and the link fields to 'BreedNo' in both the 'CatInfo' and 'Breeds' tables. I then added three fields 'BreedNo', 'Breed' and 'Colour' from the 'Breeds' table. When viewed in the form navigator these fields are definitely housed within the 'AddBreed' subform. I then saved my form and opened in data entry view again.

As before, I can enter data into the main form, but neither of my subforms allow data entry. I can click into the fields but cannot type, and both navigation bars (I added one to the second subform to check it also) have all options greyed out.

If I click on the Data Sources button above the form, I can open the various tables and enter data there. Likewise, I can enter data if I go direct to the tables. None of the fields are set to disallow data entry and as far as I can ascertain, all relationships are correctly set up.

Both tables used for the subforms have a Primary Key that is used as a Foreign Key within the table used for the main form.

I am happy to upload my whole database design if need-be, but perhaps someone can see something obvious that I am missing. I am using version 3.0.0 in Vista, by the way.

Many thanks in advance,

Hezza

[Villeroy]

Record sets are editable if they are derived from a single table of an editable source and include the table's primary key.
Editable sources are the databases serverd by the built-in HSQLDB server and many other "true databases" you can connect to, including dBase (OK, dBase is special. It does not even require a PK to be editable, see "Bibliography")

Code: Select all

SELECT "Col7", "Col4", "Col1", "Col2" 
FROM "TableX" 
WHERE "Col6">0 
ORDER BY "Col8"

is editable if "TableX" is editable and the PK is included in the selected fields. The WHERE-clause and the order of columns and rows make no difference at all.

A record set with horizontal calculations is editable as well, except for the calculation result:

Code: Select all

SELECT "PK", "Count" * "Price" AS "Amount" 
FROM "Jobs"

"Horizontal calculation" means that each calculated result is derived from values in the same record. Edits in calculated field "Amount" are ignored.

Record sets derived from more than one table are not editable. Try this with 2 tables and be amazed:

Code: Select all

SELECT * FROM "Table1", "Table2"

This will return all columns (*) of each record in "Table1", combined with all columns of each record in "Table2". This is called the Cartesian Product of both tables. If the tables have 10 records and 3 columns each, the result is 10x10 records and 6 columns. Other databases may be able to identify idividual records by the included PK, but HSQL treats the Cartesian Product like a calculated result. The Cartesian Product is a read-only record set in HSQL.

A one-to-many relation can be derived from the cartesian product like this:

Code: Select all

SELECT * FROM "Table1", "Table2" WHERE "Table1"."ID"="Table2"."T1_ID"

which filters the related rows out of the read-only cartesian product.

Aggregating functions are Sum, Average, Count, Min and Max. They do "vertical calculations" aggregating several records to one row:

Code: Select all

SELECT "Company_ID", SUM("Revenue") AS "Sum of Revenues" FROM "Jobs" GROUP BY "Company"

Aggregate all companies to one row per company and display the (vertical) sum for each company. This can not be editable since the aggregation does not provide distinct rows anymore. Each row represents a collection of stored table rows.
In many cases you make this human readable by fetching the company name from another table. It's read-only anyway.

Code: Select all

SELECT "Company_Name", SUM("Revenue") AS "Sum of Revenues" 
FROM "Jobs", "Companies" 
WHERE "Jobs"."Company_ID"="Companies"."ID"
GROUP BY "Company_Name"
HAVING "Sum of Revenues" > 0
ORDER BY "Sum of Revenues" DESC

First the WHERE-clause filters related records from the Cartesian Product before the HAVING-clause selects from the aggregated results, such as "Sum of Revenues">0 in this example (disregard all companies without revenues).

Forget about views for now. Using Base with it's HSQLDB you rarely ever need them. I think they never provide editable record sets like queries can. SELECT * FROM "Table" is read-only when defined as view, but the same statement provides an editable record set when used as a query interpreted by Base ("Edit>Run SQL directly" turned off).

Edit: 2009-02-03: Today I learned that even the simpliest queries return read-only result sets in direct SQL.

OK, it's getting late here. Now I have a question: Do you have a link to the directory where I can get the other parts of your linked PDF-documentation?

[Hezza1506]

Villeroy,

Edit: I have found it whilst searching for the solution to another issue: http://documentation.openoffice.org/ser ... folderID=0

Firstly, I can't actually remember exactly where in the mass of Open Office documentation the tutorial actually came from. I retrieved the link to partIII from my history, and didn't bother trying to find the others because it's the only one that actually does any database building. Part I was an introduction to databases generally, and Base specifically, and included information on the different field types etc. Part II introduced the 'real world' example and taught the basics of database design and UML diagrams etc. I've actually managed to retrieve it from history too - http://www.openoffice.org/files/documen ... t%20II.pdf. Part III starts at the first point that you're actually supposed to do anything with the database program, so that is the bit that takes you through actually building the tables, forms etc. Most of it is done in SQL, which I have never used before - the last time I did any programming was my 1st year at Uni in 2000 and that was in Java. Before that, I had only used Visual Basic at school.

Anyway, thanks for your enlightening post regarding the use of queries. However, apologies if I've got the wrong end of the stick, but I'm not entirely sure how that provides a solution to my problem of being unable to add or edit records using my subforms? Am I missing something?

Sorry!

[Villeroy]

Open your form in edit mode.
Get toolbar "Form Design" and push it's 5th button to get into the "Form Navigator".
Get the data-properties of your form. I guess, it's bound to a table.
Pick your subform from the navigator. I guess, it's bound to a read-only record set defined by a predefined SELECT-query from the queries container or a SELECT statement stored in the form itself.

There is a database attached to http://user.services.openoffice.org/en/ ... 798#p66798
It has a certain design flaw, but anyway it demonstrates how forms can reflect one-to-many-relations and many-to-many-relations, so you are enabled to enter ID numbers into tables by their name or something. The form does not show a single ID number.

[Hezza1506]

Villeroy,

Both form and sub-forms are bound to tables. As far as I can see, there is no difference between the form in that other post and my own. I really have no idea where I'm going wrong. The properties are as follows:

MainForm
Content type.............. Table
Content.................... CatInfo
Analyze SQL command... Yes
Filter.......................
Sort........................
Allow additions........... Yes
Allow modifications...... Yes
Allow deletions........... Yes
Add data only............. No
Navigation bar........... Yes
Cycle...................... Default

AddBreeder (1st subform)
Content type.............. Table
Content.................... Breeders
Analyze SQL command... Yes
Filter.......................
Sort........................
Link master fields....... "Prefix"
Link slave fields......... "Prefix"
Allow additions........... Yes
Allow modifications...... Yes
Allow deletions........... Yes
Add data only............. No
Navigation bar........... Yes
Cycle...................... Default

Within this subform there are then two labels (lblBreeder and lblPrefix), two text fields (txtBreeder and txtPrefix) and a navigation bar. The text fields are linked to the Breeder and Prefix fields from the Breeders table. Prefix is the primary key for Breeders and a foreign key in CatInfo.

There is a second subform as well, but I'm guessing it's the same problem with both, and as the first subform had this issue before I added the second subform, I'm guessing its presence won't affect the outcome. Having said this, I can remove it if that would make things easier.

If it helps, I could upload the whole database - there's no data in it at the moment, so it's not like there's anything personal there. It is 87kb, but I don't think that's big enough to cause any issues, is it?

[edited to add:]
I've just thought that I perhaps ought to put in the code that is held in the field within mainform that ties to the subform.

This is a pull-down menu called 'Prefix'. It's data tab is as follows:

Data field................ Prefix
Type of list contents... Sql
List content............. "SELECT "Prefix", "Prefix" FROM "Breeders""
Bound field.............. 1

Maybe I ought to put in the details from the second subform for reference?
It is linked from another pull-down menu in the mainform:
Data field................ Prefix
Type of list contents... Sql
List content............. "SELECT "BreedNo", "BreedNo" FROM "Breeds""
Bound field.............. 1

Again, BreedNo is the Primary Key for the Breeds table, and a foreign key in CatInfo. The properties of the subform are:
AddBreed
Content type.............. Table
Content.................... Breeds
Analyze SQL command... Yes
Filter.......................
Sort........................
Link master fields....... "BreedNo"
Link slave fields......... "BreedNo"
Allow additions........... Yes
Allow modifications...... Yes
Allow deletions........... Yes
Add data only............. No
Navigation bar........... Yes
Cycle...................... Default

Hope that helps

[Hezza1506]

Okay, I've had a new development - don't know whether this will help anyone to figure out what the problem is?

Yesterday, I entered one record into the 'Breeds' table
BreedNo. - 68
Breed - Asian
Colour - Black

I opened the form just now and in the 'BreedNo' pull-down menu within the mainform, I selected 68 (the only record available). It didn't fill in the details in the 'AddBreed' subform until I clicked into the subform, but at least it did fill them in once I did that.

However, what I didn't expect is that I also instantly became able to type into the boxes in both that subform and the other subform ('AddBreeder'). I tried adding a record to the 'AddBreeder' subform, but the records typed don't seem to stay, and indeed, when I press the tab key to move from field to field it changes from Record 1 to Record 2, or sometimes from Record 1 to Record 0. I'm not sure whether there are certain things that mean that it goes 1 to 2 and other things that mean that it goes 1 to 0.

Then, I tried to enter a second breed into the 'AddBreed' form, and typed into the first field, tabbed to the second, typed into that and then tabbed into the third and typed there. However, when I then tabbed off the last field, I received a new popup window reading:

Error updating the current record

Integrity constraint violation For_Key_Breed_Cat table: CatInfo in statement [UPDATE "Breeds" SET "Breed" = ?,"BreedNo" = ? WHERE "BreedNo" = ? ]

For_Key_Breed_Cat is the foreign key that is supposed to link the CatInfo table with the Breeds table through the BreedNo field in both tables.

I then tried using the navigation bars attached to each form to save the current record, instead of tabbing off the bottom field, I clicked the save record button, and this seems to have worked fine. So I went to the 'AddBreeder' subform to try the same thing. I entered data in the first field and clicked tab, and instead of moving to the next field, it moves to the next record.

Also, if I then skip to a new record on the mainform, both subforms are re-set to saying 'Record 0 of 0' and not allowing me to enter any data. I can select a Prefix and BreedNo entered previously, but the subforms still do not display any data until I actually click into one of them, and then they both suddenly fill in with the Prefix & BreedNo from the mainform, along with the relevant other info.

Do these help with an understanding of what I might be doing wrong?

What is supposed to be happening is that the 'AddBreeder' form will allow me to enter a new prefix (and breeder name), which can then be selected from the 'Prefix' pull-down menu in the mainform, and the 'AddBreed' form will allow me to enter a new breed number (and corresponding breed & colour), which can then be selected from the 'BreedNo' pull-down menu in the mainform. Obviously this isn't possible if I have to select an option from the pull-down menu first, to be able to enter anything in the subforms!

[Villeroy]

Integrity constraint violation For_Key_Breed_Cat table: CatInfo in statement [UPDATE "Breeds" SET "Breed" = ?,"BreedNo" = ? WHERE "BreedNo" = ? ]

The whole problem has nothing to do with your forms. It's the database which does not allow to enter some duplicate value. Try to enter the values directly into the table view in order to reproduce the above error message.

Just another one: movies_2.odb by Drew Jensen with two databases in one. You can use it in a way where each movie belongs to exactly one genre or you can use another set of relations where each movie can belong to more than one genre. In the relations window you see how the many-to-many-relation between movies and genres is implemented.

[Hezza1506]

It turns out that the problem was that I was essentially trying to use the foreign key relationship 'backwards'. I've managed to get rid of all of the above issues by making my subforms mainforms and just having several mainforms on the one page. The only issue that leaves me with is how to refresh the listboxes pulling data from the other mainforms, but that's an issue for another post, I think.

[memilanuk]

Hezza,

Thanks for that little tidbit at the end... I think that may have just saved me some head-aches. More to come, I'm sure!

Monte