Button to open a form based on the record chosen

Discuss the database features
Post Reply
however
Posts: 160
Joined: Fri Mar 02, 2012 3:46 pm

Button to open a form based on the record chosen

Post by however »

Good day everyone,

I am trying to open a form (Patient Record) by clicking a button (Open Patient Record button) based on the patient I have chosen on a previous 'Main List' form.

Searching through the forum I found what i need however, the SQL code is to complex for me to be able to adapt to my DB, as I have no much knowledge of SQL programming.I have attached the DB.example that I found in this forum and with the 4th form (Patient-Table Control-TextBox) I would solve my problem.

Could anyone tell me what info do I need to post in order for someone to help me write the appropriate SQL code?

Regards,
Attachments
Open_Form_Filtered.odb
(83.63 KiB) Downloaded 561 times
ApacheOpenOffice 4.1 on Slackware 14.1
User avatar
DACM
Volunteer
Posts: 1138
Joined: Tue Nov 03, 2009 7:24 am

Re: Button to open a form based on the record chosen

Post by DACM »

Those examples are primarily a demonstration of various macros used to open a Form filtered to the selected patient/contact/employee. You've chosen the example that reads the patient ID from a Table Control selection, then opens a Form filtered to that patient. There's no SQL associated with the embedded macros in this case (although it is possible to dispatch SQL through a macro). The Macro code is used to manipulate built-in Form filtering mechanisms. This is more flexible than SQL filtering because with Form filtering, the user can simply toggle the 'apply filter' icon, or click the more permanent 'delete filter/sort' icon to gain access to the entire record-set available to the Form.

So perhaps you're referring to the SQL used to build the Forms themselves. That's outlined in another example:
[Example #1] Filter/Search with Forms (leveraging SubForms)

Either way, maybe post an example of your database with all sensitive data removed for specific replies.
Last edited by DACM on Sat May 26, 2012 10:47 pm, edited 1 time in total.
AOO 4.1.x; LO 4.2.x; Windows 7/8 64-bit
Warning: Avoid embedded databases --> Solution: Adopt a portable 'split database' folder
Soli Deo gloria
however
Posts: 160
Joined: Fri Mar 02, 2012 3:46 pm

Re: Button to open a form based on the record chosen

Post by however »

Hello DACM,

thank you for your reply.

I think I understood your explanation regarding the form/filter that I have chosen, however i'm not quite clear how to apply it to my form. There are a lot of 'red' strings which are supposed to be changed, according to the comments on the script but it's not very clear which table fields I should use/replace to apply it to my case.

I have reduced my DB to 80Kb so that I can attach it here.

In the 'Patient List' form i have a 'Go To Patient Record' button which should open the form containing that patient's record (the one that I initially choose in the main table control).

Obviously, as you will notice, I just simply copied and attached that entire SQL code used to build the forms thinking that it would be the solution. Newbie :ouch:

Anyway, I would like to thank anyone/everyone for helpful tips.

Regards,
Attachments
ClinicDB_test.odb
(81.42 KiB) Downloaded 440 times
ApacheOpenOffice 4.1 on Slackware 14.1
however
Posts: 160
Joined: Fri Mar 02, 2012 3:46 pm

Re: Button to open a form based on the record chosen

Post by however »

Apologies for confusing and mixing again the word 'SQL-code' with 'Macro'.
ApacheOpenOffice 4.1 on Slackware 14.1
User avatar
DACM
Volunteer
Posts: 1138
Joined: Tue Nov 03, 2009 7:24 am

Re: Button to open a form based on the record chosen

Post by DACM »

however wrote:...i'm not quite clear how to apply it to my form. There are a lot of 'red' strings which are supposed to be changed...I just simply copied and attached that entire SQL macro code used to build open the forms...
Hi however,

Coding of any kind can be difficult. As a minimum, the example Forms and Macros require knowledge of Forms (object model and tree structure as revealed by the Form Navigator), as well as, database objects (field/column names). Ultimately, generating code for a database application also requires knowledge of the macro syntax, SQL, and some API specifics such as database connection services/interfaces.

That said, you've chosen the most advanced example because it involves a Table Control (grid). A Table Control contains Columns that require a right-click to reveal their properties (unfortunately, Table Controls do not reveal their columns/objects in the Form Navigator tree-view). What's more, the example uses a rather advanced technique in which the Patient 'Name' column of the Table Control has been replaced with a List Box. This allows us to bind the List Box to PatientID while displaying the associated Patient name from the Patient table. The benefits of this technique are two-fold: (1) it allows us to display the Patient name even if the Form is based on a related table containing only the PatientID; (2) it provides the PatientID to the Macro, used to filter the opened Form.

However (no pun to your username ;) ), you're Form is based on the Patient table itself -- as opposed to a related table containing only the PatientID -- so you're able to display the Patient name directly using a simple Text Box column(s) in the Table Control. That's fine but you'll need to add a PatientID column to your Table Control in order to supply the PatientID to the Macro (or perhaps we could change the Macro to filter by name rather than ID, but that would be slightly slower and likely imprecise). We can then 'hide' the PatientID column within the Table Control, so there's no change to your Form display. 8-)

Here's your Form with those changes applied:
Attachments
ClinicDB_test 2.odb
Open Form Filtered - by Table Control selection using ID from a hidden column in the grid
(81.45 KiB) Downloaded 495 times
AOO 4.1.x; LO 4.2.x; Windows 7/8 64-bit
Warning: Avoid embedded databases --> Solution: Adopt a portable 'split database' folder
Soli Deo gloria
however
Posts: 160
Joined: Fri Mar 02, 2012 3:46 pm

Re: Button to open a form based on the record chosen

Post by however »

DACM,

thanks very much. I'm still reading over your elegant explanation which I would like to understand so that I may be able to use again throughout.

I believe that in your example you used your second approach where the form macro was filtered by PatientName. If I understood correctly, I should have added a column in the 'PatientList Table Control' that displayed the PatientID and then perhaps hide it; and then follow your explanation. Is that correct?

Another question then comes to my mind; would I be able to use those macro again, as they are, without modifying the script? Or would I need to replace something?

But once again, thank you.

Regards,
ApacheOpenOffice 4.1 on Slackware 14.1
User avatar
DACM
Volunteer
Posts: 1138
Joined: Tue Nov 03, 2009 7:24 am

Re: Button to open a form based on the record chosen

Post by DACM »

however wrote:I believe that in your example you used your second approach where the form macro was filtered by PatientName.
I think I my explanation above was a bit confusing. In all cases, the Form is filtered by PatientID, including your Form re-posted above.
however wrote:If I understood correctly, I should have added a column in the 'PatientList > Table Control' that displayed the PatientID and then perhaps hide it; and then follow your explanation. Is that correct?
Yes, exactly. That's the steps/technique I used to integrate your Form with the macro logic. If you right-click near the top of the Table Control with your PatientList form in edit mode, you'll see the option to unhide the added PatientID column. The macro uses the value of this hidden column to determine the selected PatientID...which it then uses to modify the form-filter of the PatientRecord form.
however wrote:Another question then comes to my mind; would I be able to use those macro again, as they are, without modifying the script? Or would I need to replace something?
You would most likely need to replace various Form-Control names or Table-Field names. Each macro demonstrates Patient selection from a different Form Control. Only the 'OpenFormFilter_4' macro has been catered to your particular Forms.
AOO 4.1.x; LO 4.2.x; Windows 7/8 64-bit
Warning: Avoid embedded databases --> Solution: Adopt a portable 'split database' folder
Soli Deo gloria
however
Posts: 160
Joined: Fri Mar 02, 2012 3:46 pm

Re: Button to open a form based on the record chosen

Post by however »

Hello again,

DACM thank you for your time, however every time I use the macro/push button you scripted it modifies my page margins and layout.

Would you have any ideas how to repair this?

Regards
ApacheOpenOffice 4.1 on Slackware 14.1
however
Posts: 160
Joined: Fri Mar 02, 2012 3:46 pm

Re: Button to open a form based on the record chosen

Post by however »

Ignore my last post! problem solved by getting some sleep so that I could then remember to save it.
ApacheOpenOffice 4.1 on Slackware 14.1
RPG
Volunteer
Posts: 2250
Joined: Tue Apr 14, 2009 7:15 pm
Location: Netherlands

Re: Button to open a form based on the record chosen

Post by RPG »

Hello
however wrote:Another question then comes to my mind; would I be able to use those macro again, as they are, without modifying the script? Or would I need to replace something?
Hello

You did asked for more re-usable macros. Using and making more re-usable macros can only when you work real close to OOo.

I have made in this example three macros.

For opening a form. You can pass the form document name in the tagfield.

For when a dataform is reloaded in a form document. This copies some values from the dataform to the used table what is stored in the database.


For Activating a form document. Then some values are copied from the table in the database to the data form in form document.


I did make it only as example. The example is also not complete. This mean: It can only be used for tables and not for Queries. There is also no error trapping.

Explanation making filter data.
In this example is assumed you use the same table in the mainform of all form document who you want chained. In the first form document you the records you want with a filter. The filter is the builtin filter. Each time when this data-form is reloaded the filter values are copied to the table in the database. When you want not make a form it is not a problem in the datasource you can filter the table and then automatic the values are stored in the database.

Explanation using filter data.
As told the filter data is stored in the database for each table. I use this idea for form document. Each time when a form is activated I reload the filter data belonging to that table used in the mainform with the name: ControlForm.

Calling a form
With the button you open only the form and do not pass values. When the form is called the form is also activated. A other macro executed when the form document is activated copied the filter values to the place in the data-form with the name: ControlForm.

Pro and Contra
It is not so this is easy for people who has already problems with real easy forms. Making forms for databases is always difficult.
A problem is also you cannot select only a record. You have to filter the table until you have the recordset you want have. Now you can use the filter values for more then one dataform.

This idea is more an idea then a complete solution.
Attachments
ClinicDB_test 3.odb
(97.66 KiB) Downloaded 565 times
LibreOffice 7.1.4.2 on openSUSE Leap 15.2
warnockg90
Posts: 1
Joined: Fri Sep 14, 2018 1:01 am

Re: Button to open a form based on the record chosen

Post by warnockg90 »

DACM wrote:
however wrote:...i'm not quite clear how to apply it to my form. There are a lot of 'red' strings which are supposed to be changed...I just simply copied and attached that entire SQL macro code used to build open the forms...
Hi however,

Coding of any kind can be difficult. As a minimum, the example Forms and Macros require knowledge of Forms (object model and tree structure as revealed by the Form Navigator), as well as, database objects (field/column names). Ultimately, generating code for a database application also requires knowledge of the macro syntax, SQL, and some API specifics such as database connection services/interfaces.

That said, you've chosen the most advanced example because it involves a Table Control (grid). A Table Control contains Columns that require a right-click to reveal their properties (unfortunately, Table Controls do not reveal their columns/objects in the Form Navigator tree-view). What's more, the example uses a rather advanced technique in which the Patient 'Name' column of the Table Control has been replaced with a List Box. This allows us to bind the List Box to PatientID while displaying the associated Patient name from the Patient table. The benefits of this technique are two-fold: (1) it allows us to display the Patient name even if the Form is based on a related table containing only the PatientID; (2) it provides the PatientID to the Macro, used to filter the opened Form.

However (no pun to your username ;) ), you're Form is based on the Patient table itself -- as opposed to a related table containing only the PatientID -- so you're able to display the Patient name directly using a simple Text Box column(s) in the Table Control. That's fine but you'll need to add a PatientID column to your Table Control in order to supply the PatientID to the Macro (or perhaps we could change the Macro to filter by name rather than ID, but that would be slightly slower and likely imprecise). We can then 'hide' the PatientID column within the Table Control, so there's no change to your Form display. 8-)

Here's your Form with those changes applied:
I am trying to use the same method to load the record of a patient from a table that is populated using a query.
However I get the same error message / runtime error that no such element exists when trying to grab the ID of the patient from a hidden box in the table.

I will attach the database - any help is appreciated.
I do not have many relationships / tables etc and all information contained in tables is test data only.
I have a basic understanding of databases but have never done much with regards to functions / macros / SQL in the past. If you know of any other good resources to aid learning I would be grateful too.

Thanks again,

Geoff
Attachments
Pain Audit.odb
(40 KiB) Downloaded 344 times
Apache OpenOffice 4.1.5
MacBook Air, OSX 10.13.6
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Button to open a form based on the record chosen

Post by UnklDonald418 »

I opened "frm_Main" in the Edit/Design mode, and right clicked on the blank area to the right of your existing column names to get a selection list.
If there were hidden columns I would expect one of the selections to be Show Columns, but I don't see that, indicating that there is no hidden column named ID.
The reference on macros can be downloaded
http://www.pitonyak.org/oo.php
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Button to open a form based on the record chosen

Post by UnklDonald418 »

In case you aren't clear on how to repair your form so that the macro will work:

Open "frm_Main" in the Edit/Design mode.
Right click on the empty area to the right of the current column headings and choose Insert Column>Formatted Field to add the column to the table.
Right click on the new column heading and select Column to open the Properties: Formatted Field dialog.
On the Data tab select ID from the list of the available fields shown on the Data field.
On the General tab set both Name and Label to "ID".
Right click on the ID column heading and select Hide Column.
Save and Close the form.

I noticed on frm_NewPt, List Box 1 is not working.
In the Edit/Design mode right click on List Box 1 and select Column to open the Properties: List Box dialog.
On the Data tab I see the the Data Field is set to "Rescue Analgesia".
For Type of list contents select Sql
Click on the ellipsis to the right of List Content to open a query design dialog.
I'm assuming you want to use tblRescue so Add that table to the query.
Select Rescue Therapy for the first column, and rescueKEY for the second column. It is important that they be in that order for the list box to work properly.
Under the Rescue Theapy column select Sort > Ascending.
Close and Save the query, and notice the SQL command for that query now appears as the List Content property.
You might also want to change the column Label on the General tab of the dialog.

There is one other issued that you should address while in the Design mode.
Select the Form Navigator, (The 4th icon from the left along the bottom of the window) to open the Form Navigator dialog.
Right click on SubForm and select Properties to open the Form Properties dialog.
On the Data tab change the Navigation Bar setting from Yes to Parent Form. That way when you select something on the SubForm grid you won't high jack the MainForm navigation bar.
Close and Save the Form.
Now the list box should let you select from the available Rescue Therapy items in tblRescue

You can learn the basics of SQL from one of online tutorials. Just do an internet search for "SQL Tutorial".

Base is just a front end for a HSQLDB database engine providing some GUI capabilities. While an Embedded database using Base is a convenient learning platform it suffers from some issues making it unsuitable for long term use.
[Wizard] Create a new 'split' HSQL 2.x database
You can copy your tables, queries, forms and reports from an Embedded database into a split database using cut and paste.

If you have privacy concerns with your database, HSQLDB does offer data encryption but I've never used it and don't know about its reliability.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Post Reply