LO querry not saving records from form

English
#1

These last few weeks LO 7.3.7.2 has been erratic at saving new records I have entered into a form. Sometimes it does but more often it doen’t, and I have to re enter the record /information again. I always make sure I save the form and also the database file. The new record is in the table, but not the query, and when I enter the record again, it is duplicated in the table!
Can anyone help here please.

#2

Refresh the (sub-)form that should display the newly added data.
I can offer a simple macro doing this automatically for you: Apache OpenOffice Community Forum - [Base, Python] Tiny Macro Refreshing Forms, List/Combo Boxes - (View topic)

  1. Install the macro.
  2. Assign the macro to the form event “After record action”.
  3. Configure the macro by adding a hidden control named “AutoRefresh” to the editable form and add the element(s) to be refreshed to the hidden control’s “Value” property. Form navigator → right-click the form and choose New > Hidden Control. Get the properties, rename to “AutoRefresh” and add the element or a semicolon separated list of elements. Refreshable elements are (sub-)forms, list boxes and combo boxes.
    Subform refreshes this form’s element “Subform”.
    Subform;Table Control/Listbox1 refreshes this form’s element “Subform” AND the column named “Listbox1” within this form’s element named “Table Control”.
    ../Combo Box1 refreshes “Combo Box1” within this form’s parent form.

The “After record action” is triggered when a record has been saved, inserted or removed.

#3

Thanks for the information about macros and sub form, but my odb file is quite simple. The main form has a combo box at the top to select a year, and that is all.
I could upload the file if you wish, so that you could perhaps have a look on how i would use a macro and sub form in it.

#4

Of course. I can not even imagine, how to select a year from a combo box and what should happen then.

#5

Thanks and the files is included.
To select a year / month, open the combo box select what year / month, then press the ‘GO’ button.
Just to note that I did not make the combo box as it was done for me on here about 18 months ago by another member.
LeedsWXData.odb (961.0 KB)

#6

LeedsWXData.odb (960.7 KB)

Alright, I changed a few things:

  1. I simplified the query. Now it is a parameter query comparing numbers rather than strings:
SELECT "tblWXdataLeeds".* FROM "tblWXdataLeeds" 
WHERE YEAR( "ReadDate" ) = CAST(LEFT(:paramYearMonth, 4) AS SMALLINT)
    AND MONTH("ReadDate") = CAST(SUBSTRING(:paramYearMonth, 6, 2) AS SMALLINT)
ORDER BY "ID1" ASC
  1. In the form navigator I moved the “Main” form to the filter form.
  2. Now being a subform, I linked the filter form’s “YearMonth” with “paramYearMonth”.

Now, everything worked as expected.
I did some cosmetical/practical changes:
4. I simplified the listbox source a tiny little bit (the concatenation is null when the field value is null).
5. I replaced the filter table with a more versatile one having several fields of various types, an integer primary key “FID” and a description field “DESCR”. This filter form can be used to filter many different record sets in the same database while storing some documentation.
6. I added the “FID” and “DESCR” to the form. You may hide them (Visible =No) when the form is ready to use.
7. Changed the column name from “YearMonth” to “C1” as used in the new filter table.
8. Added a cancel button to cancel criteria input.

For the versatile filter table I have an AutoText in Writer. The column names indicate only the data type since each row serves a different purpose with different queries, forms, reports, spreadsheets, serial letters etc.

CREATE TABLE "FLT"(
    "D1" DATE,
    "D2" DATE,
    "INT1" INTEGER,
    "INT2" INTEGER, 
    "INT3" INTEGER, 
    "DEC1" DECIMAL(7,2),
    "DEC2" DECIMAL(7,2), 
    "C1" VARCHAR(50), 
    "C2" VARCHAR(50), 
    "C3" VARCHAR(50),
    "BOOL1" BOOLEAN, 
    "BOOL2" BOOLEAN, 
    "DESCR" VARCHAR(200),
    "FID" SMALLINT NOT NULL PRIMARY KEY
);
#7

Thanks for that, and it will take a bit of getting used to now ):
Just a couple of questions:
Why 2 date fields D! and D2
And also what is the name of the filter form, because as you have moved the main form navigation to the filter form, I will need to add new records every day.

#8

This is something I do quite frequently:

SELECT * FROM "somewhere"
WHERE (:paramFromDate IS NULL OR "date column" >= :paramFromDate)
  AND (:paramUntilDate IS NULL OR "date column" <= :paramUntilDate)

Filters a subform by a two from…until dates stored in D1 and D2 passed through parameters paramFromDate and paramUntilDate. A missing parameter :param IS NULL returns True, therefore this condition is ignored.

The columns you don’t need right now, are inexpensive since we always load only one distinct filter row.

#9

Ok, I see now. Yes I have to pres the GO button each time I open the form before the navigation buttons appear.

thanks

#10

My version loads the correct records right from the start. The important change was step 2.

#11

Sorry for being a bit of a dumbo, but when I open the Leeds Weather form, if I just wait, ie don’t do anything, then nothing happens, So then I press the ‘GO’ button and the navigation buttons appear so I can add a new record.
Is that how it should work?

Thanks

#12

I download my file https://ask.libreoffice.org/uploads/short-url/aYi2qoK59yWfqO5yI8sNRnl9P22.odb to my download directory, open the file, open the “Leads Weather” form and get data filtered by “2022-06” which is the criterion I used before uploading.
Try downloading again.
My office version:

Version: 24.2.7.2 (X86_64) / LibreOffice Community
Build ID: ee3885777aa7032db5a9b65deec9457448a91162
CPU threads: 4; OS: Linux 5.15; UI render: Skia/Raster; VCL: x11
Locale: de-DE (de_DE.UTF-8); UI: en-US
Calc: threaded

Plus a second computer, same version on Windows 10.

#13

No difference with 2nd downloaded odb file, I still had to press the GO button before the navigation buttons appeared.
I am using Linux Mint Victoria, and don’t have any Microsoft products here.

#14

Now I understand. When the form is loaded, no form control is focussed. When you focus the month selector, no navigation toolbar is shown neither, because the toolbar is disabled for this form. The toolbar makes no sense editing a single value in a single record.

When you click any form control belonging to the subform, the toolbar is shown for the subform having the focus. The OK button belongs visually to the filtering form, actually it belongs to the filtered subform. It takes away the focus from the filtering form, which saves the filtering form on the fly, before refreshing the filtered subform where it belongs to.

#15

LeedsWXData_2.odb (988.4 KB)
Demonstration of different filter sets in one filter table with various types of criteria.
Contains additional queries “qry_WXdataLeeds_Filter1” and “qry_WXdataLeeds_Filter2”, filtering the forms “Leeds Weather1” and “Leeds Weather2”.

#16

Ok thanks for the explanation, but for me as you probably realise, I enter new records or data every day, and I don’t edit or alter anything once it has been entered and saved. So I need some form of navigating to the next or new day in the said month.
The 1st odb file you did for me is fine, and it is no trouble or problem to press the GO button.

Now regarding the different filter sets, I found Leeds Weather1 to work ok for me rather than Leeds weather2 form. I found that with the 2nd form the From date is the one that gives the data for that date.
I hope I have explained it ok.

Thanks again

#17
  1. Add filter record with new FID=4.
  2. Make a copy of the form document “Leeds Weather2” which is the one with the date filter (copy form icon, paste and enter name)
  3. Edit the copied form document.
    3.1. Filtering Form:
    3.1.2. Change the source record of the filtering form: SELECT * FROM "tbl_Filter" WHERE "FID"=4
    3.1.2. Remove the second date field “Until Date D2”.
    3.1.3. Make the remaining “From Date D1” mandatory (“Input required”=Yes)
    3.2. Filtered Subform:
    3.2.1. Set the the subform to “Add data only”, so it loads at the new record and connect it directly to the Leeds weather table rather than a query.
    3.2.2. Link the filtering “D1” to the filtered “ReadDate”. For a simple “X”=“Y” filter we don’t need a parameter query.

Now you have a form which is set up to load with a preset “ReadDate” value in the new record. You may remove the “ReadDate” from the filtered subform. It inherits from the filtering form.
The last button on the navigation bar shows a docked window with a table grid of the form showing the records that have been newly added in this session.

If you abstain from step 3.2.1, you get a form filtered by that date. For a new record with that date, just navigate to the new record. The new record inherits the date anyway.

#18

What do I choose for Field Type when I add a filter record FID=4

Thanks

#19
  1. Open the filter table, move to the row for a new record, enter 4 (or any other unused integer) into the FID column, which is the integer primary key of that table. It identifies this particular record. You can’t have 2 records with the same ID. All the other columns in that table are optional (nullable). I use to enter some text describing the purpose of that row into the DESCR field.
  2. Now bind a filter form to that partiular row: SELECT * FROM "tbl_Filter" WHERE "FID"=4. You can do that right in the form properties with “Source type”=SQL. The button with the ellipsis […] opens a query editor if you need one.
  3. Disallow everything but modification, so you can’t mistakenly navigate into another record by Tab key nor can you delete that record when using the form. All you need to do with the filter form is editing this particular record.
  4. Add a date control and bind it to column “D1” for a simple date filter and a cancel button with action “Undo data entry”. This is useful when you want to undo a mistaken filter entry.
    Bind a subform to your actual data table and connect the subform’s date field to the parent form’s D1 (property “Master”=D1, property “Slave”=ReadDate).
    Add an OK button to this form with action “Refresh form” and “Take focus on click”=Yes.
    Now the subform is filtered by the parent form’s “D1” stored in the distinct row with FID 4. The OK button takes away the focus from the master form with it’s modified D1 value which saves that form before refreshing the subform. When moving the filtered form to the new record, the master form’s date value is transferred as a default value to the filtered form.
1 Like
#20

Thanks, but what do I use for the Field Type for FID=4.

I have just Text[VARCHAR] now

I have got as far as 3.2.2 on your previous post. Where or how do I link D1 to the filtered ReadDate.

On 3 of your 2nd post you say to ‘Disallow everything but modifications’. Does that include No to add data only
Pardon me for the questions as .I am not that knowledgeable about LO.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.