Ask Your Question
0

Base Form data uneditable [closed]

asked 2017-05-09 15:06:45 +0100

Avvy65 gravatar image

I am using this to get VAT and Totalcost results from a query:

SELECT "ID", "BuyDate", "item", "Quantity", "OrderID", "Cost", "Vat", "Delcost", "TotalCost", "SupplierPhone", "SupplierEmail", "Notes", "SupplierAddress", "SupplierName", "EbaySale", "A"., "A"."Cost" + "A"."Delcost" + "A"."VAT2" AS "TotalCost1" FROM ( SELECT "Table1Test"., "Cost" * .2 AS "VAT2" FROM "Table1Test" ) "A"

It does bring up the desired results ok , but I found that the data is not editable, neither in the query nor the Form. I do have a PK set in the table where the data is editable.

i have made up another table with just 10 records, and all the fields set accordingly, be it number or decimal. I created another query based on the said table, then used the 2 part statement to create the desired results. After doing so the fields were still not editable .

edit retag flag offensive reopen merge delete

Closed for the following reason question is not relevant or outdated by Alex Kemp
close date 2020-10-03 21:15:47.060075

Comments

What data are you trying to edit that you need a query?

Ratslinger gravatar imageRatslinger ( 2017-05-09 15:27:15 +0100 )edit

Also I should have said to make it clear is that I cannot enter a new record, and when on the form, I cannot select a new record to add new data I may or may not want to edit any existing data, as I don't know what will change yet.

Avvy65 gravatar imageAvvy65 ( 2017-05-09 15:44:43 +0100 )edit

You don't need a query to edit tables or add new records on a form.

Ratslinger gravatar imageRatslinger ( 2017-05-09 15:51:52 +0100 )edit

I'm not sure I am following you correctly, as I have always used queries in access to give or show the correct results on the form. If I don't need a query, how will the correct results be shown on the said form.

Avvy65 gravatar imageAvvy65 ( 2017-05-09 16:03:21 +0100 )edit

Don't know what correct results means in this context. Table maintenance (add, change, delete) is typically not done with queries. Since this is a major (and very lengthy) discussion, and all written down already in the documentation, once again, please refer to the LO docs - click here. Chapter 4 = Forms.

Ratslinger gravatar imageRatslinger ( 2017-05-09 16:23:53 +0100 )edit

Correct results means getting the results from a calculated query. What am I looking for specifically, as I know how to design forms and add text and list boxes etc.

All I want is for my existing databases work in base as they do in access. I might just call it a day now and go back to access, and transfer data from 2 databases to calc where I can easily get what I want from them

I apologies for wasting your time. Thanks anyways

Avvy65 gravatar imageAvvy65 ( 2017-05-09 16:32:21 +0100 )edit

It has nothing to do with your asking questions. Your last comment specifies calculated results. This has nothing to do with entering or changing data since any calculated information based upon other fields needs no entry/maintenance.

Ratslinger gravatar imageRatslinger ( 2017-05-09 16:54:11 +0100 )edit

No I don't think you are understanding me now. If you have ever used MS Access, that should help you to some degree. Calculated results are the result of calculating by whatever means 2 or more figures to give an answer. So if I want to know what the vat is on a £329 , in Access it is a simple multiplication: 329*.20 =65.8. All the data is editable whithout the TotalCost1 SQL statement you gave me, but is not editable when I use the said SQL and neither can I add a new record to the form

Avvy65 gravatar imageAvvy65 ( 2017-05-09 19:51:26 +0100 )edit

Why have a database when you cannot add new records.

Avvy65 gravatar imageAvvy65 ( 2017-05-09 19:51:57 +0100 )edit

Base is not Access. There are things which can be done in each which cannot be done in the other. There are no calculated fields in Base. This can be done with macros but that is way different in Base than in Access. Are you looking to display or store the amounts?

Ratslinger gravatar imageRatslinger ( 2017-05-09 23:08:09 +0100 )edit

3 Answers

Sort by » oldest newest most voted
0

answered 2017-05-10 12:59:24 +0100

peterwt gravatar image

updated 2017-05-10 13:24:15 +0100

If I understand your question correctly you have created a Form based on the Query you quoted. This Query was the subject of a previous question. The Query uses a calculated Field in another calculated Field and to do this the two step Query you now have is required. You find that you cannot add a new record through either the Query or Form.

I have done some tests. A Query with two calculated Fields both of which use Fields that exist in the Table and do not use one calculated Field in the other. The Query allows the creation of new records. A Form based on this Query gives error "Error writing to database. Input required in field (name of first calculated field). Please enter a value." Entering any random numbers in both the calculated fields adds the record and the calculated Fields random numbers are replaced by the correct calculated values. This is due to the Fields Data property - Input required - being set to the default Yes. Setting both the calculated Fields to No results in new records being able to be created.

With one of the Fields calculated values using the other calculated value a new record cannot be created, as you found. The Query has the New Record icon greyed out. It appears that this cannot be done.

The way to achieve what you require is not to use one calculated Field in the other. You can create a simple Query with "Cost" * .2 AS "VAT2" and "Cost" + "Delcost" + "Cost" * .2 AS "TotalCost1". The vat is calculated again in the TotalCost so you are using only fields that are in the Table. So your query would be -
SELECT "ID", "BuyDate", "item", "Quantity", "OrderID", "Cost", "Vat", "Delcost", "TotalCost", "SupplierPhone", "SupplierEmail", "Notes", "SupplierAddress", "SupplierName", "EbaySale", "Cost" * .2 AS "VAT2", "Cost" + "Delcost" + "Cost" * .2 AS "TotalCost1" FROM "Table1Test"

Also make sure you set the Input Required Data Property for the calculated Fields on the Form to No.

edit flag offensive delete link more

Comments

First, I thank you for your patience and understanding. Setting the Field Data property to No did not make the fields editable , and I checked and checked again field data property was set to No but still not editable. Thanks for the SQL statement above, which is quite tidy, but I found that for some unknown reason to me that only around a 3rd of the records were updated with a TotalCost1, that is from 351 records only 124 were given a TotalCost1 figure.

Avvy65 gravatar imageAvvy65 ( 2017-05-10 19:58:21 +0100 )edit
0

answered 2017-05-10 01:52:32 +0100

Ratslinger gravatar image

updated 2017-05-10 22:41:59 +0100

So, if you are looking at just displaying the two calculated fields, a sample is attached. The Query was modified to create two independent calculations within one statement. The fields are not connected to the table in any way and cannot be using this method. It would probably take macro coding to do that.

Sample: DisplayCalcFields.odb

If the query got more complex (as multiple SELECT statements) this is not possible.

Edit 5/10/17:

The following sample contains the form originally in the previous sample (display of calculated fields) plus an additional form which is based upon a macro to insert the calculated values into the proper fields for updating.

Sample: UpdateCalculatedFields.odb

Again, macros in LO are quite different than Access. For basic info on macros, see chapter 9 - Macros in the LO documentation. For a more complete guide on LO macros (quite large doc) there is Open Office Macros Explained. For OOME pdf - click here.

The macros in the sample need adjustment to your needs and table/fields names.

edit flag offensive delete link more
0

answered 2017-05-10 02:34:06 +0100

EasyTrieve gravatar image

updated 2017-05-10 02:38:09 +0100

Queries are not always editable, and there are a whole bunch of things that can make a query non-editable. Rather than go into that list, which you can google and find for yourself, I suggest you start with a simple query that works, and slowly add to it rather than start with a complex one and then try to figure out why it doesn't work.

Also I think your questions are too long. Please simplify your questions. Ok, let me tell you what I mean. When things aren't working, YOU, start by making a copy of the database, then opening the database copy in destroy is ok mode. In this mode you can start to remove things that don't directly relate to the problem you are having. In your SQL you can remove a whole bunch of fields, till you're down to just an ID, and two fields to add together, so you can prove out the basic functionality that you need, i.e. you don't need to list every field in your SQL, just key ones that relate to getting things to work.

This does two things, one it helps you see your own problem more clearly, and removes possibly complications which might be causing issues. Two, it allows you to upload your database here so others can look at it and give more helpful answers to you.


Here is what I've noticed. First "A"., (after "EbaySale",) doesn't look right to me. It looks like an error.

SELECT 
    "ID", 
    "BuyDate", 
    "item", 
    "Quantity", 
    "OrderID", 
    "Cost", 
    "Vat", 
    "Delcost", 
    "TotalCost", 
    "SupplierPhone", 
    "SupplierEmail", 
    "Notes", 
    "SupplierAddress", 
    "SupplierName", 
    "EbaySale", 
    "A"., 
    "A"."Cost" + "A"."Delcost" + "A"."VAT2" AS "TotalCost1" 

FROM ( 
    SELECT "Table1Test"., "Cost" * .2 AS "VAT2"  FROM "Table1Test" 
) "A"

Next, I don't think HSQLDB 1.8 supports sub-queries, at least not very well.

Also you haven't said anything about what type these fields are. Are they numeric? Do they allow nulls? Nulls anywhere in sums or concatenations often results in a null result. To avoid this you must convert any nulls to zeros (for numeric values) before doing your addition. Or you must ensure that all fields are populated with data. In Access we used this great little nz() function to do this. In MySQL / MariaDB I use the isnull() function. In HSQLDB 1.8 you must use the IFNULL() or COALESCE() functions. So it's important which database you're working in. (Always please say which database you're working in in your question.)

Now this works in HSQLDB 1.8 to add fields. It uses this SQL: SELECT "ID", "n1", "n2", "n1" + "n2" AS "total" FROM "Table1"

image description

So you probably have an issue in your FROM clause. Without having your table to try this out on, I can't offer SQL to you that will work. But I suggest you try to get rid of the subquery, or move to a database that fully supports them, like MariaDB, etc.

edit flag offensive delete link more

Comments

I wasn't aware I was using a subquery, and if you could point out where I may find it , that would be great.

Avvy65 gravatar imageAvvy65 ( 2017-05-13 10:31:23 +0100 )edit

@Avvy65, It's the

( 
    SELECT "Table1Test"., "Cost" * .2 AS "VAT2"  FROM "Table1Test" 
)

in your FROM clause. Read about them here.

EasyTrieve gravatar imageEasyTrieve ( 2017-05-14 18:45:15 +0100 )edit

Question Tools

1 follower

Stats

Asked: 2017-05-09 15:06:45 +0100

Seen: 706 times

Last updated: May 10 '17