Is there a way to have a form field's data in a base query for parameter (criteria)

[jimlo9]

Like in Microsoft Access, the image has a form fields data. I already know about the colon parameter (:something), but I’d like the criteria from an open form field like The image. Thanks.

Or is there a way to insert the parameter in a query from a macro, if there is I just don’t know the code, but I can write macros for other things, I.E., importing csv data into mysql. So I’m not new to macros.

I also know in a macro I can use data from a form, but this query is for a report so the query is not written in a macro.

Or is there a way to base a report on a query in a macro?

I have searched and read various forum post on this already. These things are hard to find.

An example, regular parameters wasn’t even working, like :param, I had to do this:

All my Sub Forms stopped working after switching to JDBC connection

I had a working base application using MySQL (JDBC) connection type. I tried switching to a JDBC connection type to add SSL security as described here . The connection is ok, all tables, views and Base queries work fine as well as the Forms without sub forms. When opening any of my forms containing a sub form, I get the following error message: SQL Status: S1009 Parameter index out of range (1 > number of parameters, which is 0). It seems to match the problem described in this Stack Overf…

1. Unzip the .odb
2. edit content.xml
3. replace
   <db:driver-settings db:parameter-name-substitution=“false” />
   by
   <db:driver-settings db:parameter-name-substitution=“true” />
4. repack (rezip) the odb with the new content.xml

So it can be tricky to use, but not complaining, it’s free.

[Ratslinger]

Please when asking a question include OS, specific LO version and for Base DB used (MySQL eluded to).

[Ratslinger]

Hello,

There is no direct link from a form field to a query in Base; see-> In Base, referencing a text box from a Query

You can do this in Base with a form using a table for filtering. Basic information on this is here → Filter/Search with Forms (leveraging SubForms)

Edit:

Have a sample using a form with date fields and dropdown calendar for each. Dates already in table are from December 1-9 of 2019. Once selected press button to generate report. Also included macro to position/resize the form and remove toolbars.

Sample ----- ButtonToStartReportWithEntry.odb

[jimlo9]

Wow

There is no direct link from a form field to a query in Base.

Why not?

I am used of using a popup calendar in access, I guess now that is also out of the question, as I need dates entered.

OS = Windows 10, specific LO version = LibreOfficePortable_6.3.3_MultilingualStandard.
Base DB used = MySql 8.*, mysql jdbc connection.

So other L.O. developers have their users manually enter dates without the aid of a popup calendar?

[Ratslinger]

There is no direct link from a form field to a query in Base.
Why not?

Very simply, nobody has offered to write the code for Base to do so. Typically changes to the software is done through volunteers.

So other L.O. developers have their users manually enter dates without the aid of a popup calendar?

That is a choice. As stated, you can obtain data from a control on a form, have that go into a filter table and use the query to access the value to be used as a parameter. A date control on a form has the option to display the mentioned calendar.

Also of note, you can run a report from a form by using a small macro. This code attached to a push button will run the specified report:

ocontroller = Thisdatabasedocument.currentController
if not ocontroller.isconnected then ocontroller.connect
Thisdatabasedocument.reportdocuments.getbyname("YOUR_REPORT_NAME").open

[Ratslinger]

The posted sample contains two small macros. One is to run the report from the button - code noted in previous comment. The second is an abbreviated version of setting the form size and position. For the full version and more information on this see this post → Adjust size and placement of a form in Base

[Sasquatch]

sadly attempt to use a filter table in a query with linked mysql database gives read only results.

edit Ratslinger proved me wrong.

[Ratslinger]

@Sasquatch you seem to give up easily. Saw your original post and have been reviewing situation. Have working SQL with form using query based upon a filter table selection.

In your main question it appears you do not mention the version of MySQL you are using. You should update.

Will post there when I have more.

[jimlo9]

Fixed with:

A query in a query, bdate is a field and did similar for edate. Just created a table called rdate for report dates.

Hope this helps someone, as I know L.O. is community and depends on forum answers for help.

Thanks for the replies.

Edit: I basically

• Added another table with just a bdate and edate field and of course id
• Made a form with two input date fields
• A button to do the rest
• In code update the table with new dates.

Rough draft but works:

Sub OpenMyReport '(oEvent As Object)
DIM oDatabaseContext
DIM oDatasource
DIM oConnection
oDatabaseContext = createUnoService("com.sun.star.sdb.DatabaseContext")
oDatasource = oDatabaseContext.getByName("transactions")
oConnection = oDatasource.GetConnection("root","zzzzzzzzzz")
oStatement = oConnection.CreateStatement()

DIM oForm as Object
Dim mynum1 As string
Dim mynum2 As string
Dim vid As Integer

oForm = ThisComponent.DrawPage.Forms.getByName("MainForm") 'Get Form'

DateStr = oForm.GetByName("bedate").Text
mynum1 = CStr(DateStr)
DateStr1 = oForm.GetByName("enddate").Text
mynum2 = CStr(DateStr1)
	
dt1 = Format(mynum1, "YYYY-MM-DD")
dt2 = Format(mynum2, "YYYY-MM-DD")
vid = 1


stSql = "UPDATE rdate SET `bdate` = ?, `edate` = ? WHERE `id` = ?"
oStatement = oConnection.prepareStatement(stSql)

oStatement.setString(1, dt1)
oStatement.setString(2, dt2)
oStatement.setInt(3, vid)
oStatement.executeUpdate(stSql)
oConnection.close

RptName = "report1"
ThisDatabaseDocument.ReportDocuments.getByName(RptName).open

End Sub

Note having the two fields directly tied to database did not work for me, hitting the show report still had the table of dates not updated yet. So That’s why I update them in code.

But basically this is similar to MS Access now, just a little more work.

[Ratslinger]

It is probable that it did not work for you because the push button was on the same form as the controls. If you examine my sample you will see the button is on a subform. When the button is selected, and because it is on a separate form, the table fields are set and the query works. Saves much coding.

Also with the set up used in the sample it is relatively easy to expand the form to many reports with very little coding effort.

[jimlo9]

I went ahead and did the form with subform also, it works great also.