I’m trying to use a start date and end date on a main form as parameters in a query and display the results of some historical data in a subform on a second form. The table has only two fields: Date, and Remarks. I have searched for a solution here and found some related topics, but none that seem to work for me. I have placed Date boxes on my main form, but can’t seem to figure out how to link those Date boxes to the query. Any help would be appreciated.
You could only connect fields of the datasource of one form to field to the other form. When setting “StartDate” as connection to “Date” there will be only results for the “StartDate”.
You will need a filter table (like datasource in your mainform) and a query for the subform, which asks for the “StartDate” and “EndDate” of the filter table. So you could set the content of the second form to “Date” between “StartDate” and “EndDate”.
Without filter table I’m able to do it on opening ANOTHER form filtered by the 2 dates set on the 2 date boxes in form “main”.
So I wonder the way would be try to set the filter on the other form inserted, linked to the table of course.
This second (“sub”) form is already open, so refresh it (with another form must open > set the filter > reload it).
But don’t know really IF it’s possible and by this way (?)
It’s possible.
- Create the main form, datasource = theTable, and place the 2 data boxes + 1 button.
- Via navigator insert the subform with the Table control datasource = theTable
Date | Remarks - Get references to the 2 forms (main + sub), 2 date boxes, and write the string for the filter as
“Date” >= ‘start date as localized string’ AND “Date” <= ‘end date’
(check the string via print/MsgBox!) - Set subform filter
oSubForm.Filter = filter_string - Reload subform.
- Assign this sub to the button.
Thanks for the try. I already have tables that I’m working with and trying to decipher your method and edit my tables, queries, etc isn’t working. I appreciate the help though. Maybe if I provide you with the table names and field names, you could show me how to modify them:
Table name: “Historical Data”
Fields in table: “Date” and “Remarks”
I currently have a form called: frm_ProgramHistory
This form has a subForm that opens from a query (named “Qry_History”) displays the data in a grid format
The query selects both fields: “Date” and “Remarks”
I’ve added a “Start Date” field and an “End Date” field and a button to refresh the subform.
I would appreciate any help.
Why the query?
Can’t the Table View on subform display directly from table “Historical Data”?
But I think no matter datasource is a table or this query we can set the filter on it.
Do you have the 2 data boxes placed on main?
If so start the routine declaring the variables:
Dim…
Table View filtered from 30 April to 30 June.
Same structure of yours: | date | description |
Initially I thought I needed the query. I’m really new at using Libreoffice Base. I am attempting to reconstruct a database that I built over 20 years ago in Microsoft Access. I have since moved to the Mac environment, and thus recently found that the Libreoffice Base is a good replacement providing I can fumble my way through it. If you can show me how to configure it without the query, I say great! I have been working on this specific issue for several days to no avail.
Thanks, I appreciate the help.
At the (main) Form Properties you set the Data tab to Tables > “Historical Data”
At main controls “Start Date” “End Date” are not linked to the datasource.
REM: be VERY carefully with form’s and control’s names! Check them at the Properties windows (just like in Access).
On the subform you insert a Table Control and it will ask you about the datasource fields (just like Access) and you insert both. The fields from the Table, not query.
In the code first step should you check getting the dates from the date controls.
So get references to the main (just it for now) form and the 2 date fields.
Check via a MsgBox or print.
REM: CDateFromUnoDate is a string in your locale that can be afterwards inserted in the filter string.
Your instructions did not line-up with the controls on my form for some reason. After several trys, I started from scratch Using the Forms Wizard:
- Created form “frm_Program History”
- On the first screen in the wizard it asks to select a table and then add the fields. I added the table “Historical Data”. In the "Fields in the Form block I added: “Date” and “Remarks”
- I added a subform. I selected the “Historical Data” table and in the “Fields in the Form” block I add the fields: “Date” and “Remarks”
- On the “Select joins between your forms” screen, I selected “Date” for both the subform and main form.
- On tha “Arrange Controls” screen I left bot grids in place thinking I could delete the :Main Form" grid when I got things working.
- I skipped the rest of the options and finished the form.
- I added two date fields to the form: “StartDate” and “EndDate” and ensured they had the “drop-down” selected.
- I also added a default date to each date field.
- I added a button and selected “Refresh Form” in the “Action” block in “Control Properties”
I can change the dates in the date fields, but nothing else happens. I currently have the default dates of 01/01/75 and 12/31/75. The grid displays the only event in 1975. When I change the dates to any other year, nothing changes.
I don’t know how to make sure the start and end dates are linked to the form correctly.
If you can offer corrective actions, I would appreciate it.
Thanks.
No Wizard.
- Design - place the 2 date controls + Button (no need at this moment but to make the layout) at positions like mine. Give then specific names.
At the form “frm_Program History” Properties > tab Data select:
Table > “Historical Data”
Just this for now, ok?
I’m on phone now and the editor keeps changing the text!
Done. Form created without wizard. Date controls and button added. Properties>Data tab= “Historical Data”.
Form Navigator – if not showing up: View > Toolbars > Navigation – (the icon like a Compass) right click over form "frm_Program History” > New > Form. It’s the Sub now. Name it.
Created Sub form named: “ProgHistSub”
Insert the Table Control into “ProgHistSub”.
Make sure the Table Control is placed in the subform. You can check in the Navigator (if it is in the main you can drag to the Sub in the Navigator’s Tree View).
Now in this subform follow the hints to insert the fields present in the datasourse "Historical Data”:
Date
Remarks >> (insert just like Access).
Note that in main the controls have no link to datasourse. Yes in the subform.
Now on opening "frm_Program History” you’ll see all records of Table in the Sub.
And be able to select any date in the 2 date boxes of main (be sure to mark dropdown in Properties to have the calendar pick-up).
I inserted the “Table Control” into the Main Form and then dragged it to the subform. The entire list of items in the “Historical Data” table is now populating the subform. I can select dates in the date fields. However, the data on the subform does not change.
This is the general recipe for a reliably working filter table with optional criteria (ignoring missing criteria) returning editable records.
- Create a query selecting one table, including its primary key:
SELECT "your_table".* FROM "your_table"
WHERE (:paramD1 IS NULL OR :paramD1 <= "Your_Date_Field")
AND (:paramD2 IS NULL OR :paramD2 >= "Your_Date_Field")
- In order to create a filter table having many columns of various data types, one integer primary key (not automatic!) and a description field describing the purpose of each row for later reference, run Tools>SQL with the following SQL and then call View>Refresh Tables
CREATE TABLE "FLT"("D1" DATE,"D2" DATE,"INT1" INTEGER,"INT2" INTEGER, "INT3" INTEGER, "DEC1" DECIMAL(7,2),"DEC2" DECIMAL(7,2), "TXT1" VARCHAR(50), "TXT2" VARCHAR(50), "TXT3" VARCHAR(50),"BOOL1" BOOLEAN, "BOOL2" BOOLEAN, "DESCR" VARCHAR(200),
"FID" SMALLINT NOT NULL PRIMARY KEY);
INSERT INTO "FLT" ("FID") VALUES (1);
Have a look at the new table.
3. Create a main form based on the existing filter record at row 1. See form properties of my filter form.
Source type: SQL
Source: SELECT * FROM "FLT" WHERE "FID"=1
Disallow everything except modification of records. This is important.
4. Add as many form controls to this filtering form as needed to take your criteria set. In your particular case, this might be 2 date controls for the min. date and the max. date linked to columns D1 and D2 of our filter record.
5. Add a subform with a table control linked to the parameter query of step 1 and link D1 and D2 to paramD1 and paramD2.
6. Add a cancel button to the filtering main form with action = “Undo data entry”.
7. Add an OK button to the filtered subform with action = “Refresh form”.
What the setup does:
The result is a form where you can enter 0, 1, or 2 dates as filter criteria. Missing date values are ignored due to the filtering WHERE clause (:paramD1 IS NULL OR :paramD1 <= “Column”) which returns True (criterion is met) if the criterion is missing. If the criterion is given, the comparison decides if True or False is returned.
You can cancel the criteria input, which is important for the usability of the form.
You confirm data entry with the refresh button belonging to the filtered subform. When you click this button, it takes away the focus from the filtering main form which saves that form. Then the parameter query will be executed with the parameters substituted by the 2 dates D1 and D2 stored in the parent form. It is important that these values are stored somewhere, which is the reason why we need the filter table.
Of course nothing changes as we have done nothing yet!
Let’s recap.
After at 1st answer RobertG said
I mentioned it could be possible without the Filter Table. And afterwards started explaining that way.
So before proceed you need do decide for one methodology.
- Without Filter Table as I’m explaining.
- With Filter Table as @Villeroy is presenting.
- Yet another with parameter query (datasource = query not the table) on which you’ll be asked for the parameters just like Access.
As a side comment: you may not take notice but the Filter Table method is a clever trick to overcome Base apparent lack of a way to have queries pointing to forms, like Access
… WHERE date_column BETWEEN Forms![frm_Program History]![Start Date] AND …
So now is up to you to make the choice as also the form design depends on it of course.
I would like to continue with the way you were explaining without the filter table. If that doesn’t work, then I can move to the other method.
However, while I was waiting for your next response last night, I had created a query for a different form. I decided to save a copy as a backup because it crashed earlier and lost some data. I saved the database from the menu bar at the top of the screen and gave it a different name. I went back to working and the original database crashed. I tried to reopen and got the "recover’ screen. I clicked the option to recover the database and all I got was what looked like a document for Writer. When I tried to open the backup file I just saved, I got the same result.
Have you seen anything like this before?
Base is notoriously prone to crashes. @Villeroy has a method to set up an EXTERNAL database, that should be the option for real usage.
So pursuing this no Filter Table way we need to write the filter for the subform.
Tools > Macros > Organize Macros > Basic
select your .odb in the list at left > New > Module (will name “Module1” as default).
Write in it - of course the names you assign at your choice:
Option Explicit
Sub FiltroPeriodos
'declare variables for Main (oFM), SubForm (oSF) and date controls start and end period;
Dim oFM As Object, oSF As Object, oDFS As Object, oDFE As Object
'declare variables for the date fields contents (the dates inserted there):
Dim ds As New com.sun.star.util.Date, de As New com.sun.star.util.Date
'declare variable for the string of the filter:
Dim filtro As String
'get a reference to the main form:
oFM = ThisComponent.Drawpage.Forms.getByName("frm_Program History")
MsgBox "teste!"
End Sub
Save and execute the code (Macro) FROM THE FORM.
At this point it’s only to check for errors. It will only show up the message “teste!”
If there are no errors assign it to the button:
button Properties > Events tab > “on approve” (first option = Access OnClick) > Macro > this macro > Ok.
And check if the macro runs from the button of course.
(On a machine with W-11 now and LO updated without asking!)