Apache OpenOffice Community Forum

I am struggling with getting forms built from linked databases to work properly. Rather than trying to describe all the places I've stumbled, it might be easier for anyone replying to this message to explain, and easier for me to grasp, if you just assume that I'm an untrained monkey and you're going to explain from scratch.

Here is what I need:
A primary table (call it 'ATable') to hold client records. Fields: An auto-increment Integer ID field (call it 'AID'), a Client name Text field, a Client account Text field, and linked fields to the rest of the tables (call them 'BLink', 'CLink', and 'DLink').
Record 1: Do not enter data for record 1. I want to leave this table empty and begin filling it using an integrated form, adding records and having the linked tables supply part of the data entry.

A linked table (call it 'BTable') to hold police records. Fields: An auto-increment Integer ID field (call it 'BID'), a City/Region Text field, a Dept name Text field, and a Phone number Text field.
Record 1: 'Dalton, GA'; 'Dalton Police Dept'; '706-555-1210'
Record 2: 'Calhoon, GA'; 'Whitfield County Sheriff Office'; '706-555-1212'
Record 3: 'Chicago, IL'; 'Chicago Police Dept'; '847-555-1213'
Record 4: 'Highwood, IL'; 'Cook County Sheriff Office'; '847-555-1214'

A linked table (call it 'CTable') to hold fire records. Fields: An auto-increment Integer ID field (call it 'CID'), a City/Region Text field, a Dept name Text field, and a Phone number Text field.
Record 1: 'Dalton, GA'; 'Dalton Fire Dept'; '706-555-1215'
Record 2: 'Calhoon, GA'; 'Calhoon Volunteer Fire Dept'; '706-555-1216'
Record 3: 'Chicago, IL'; 'Chicago Fire Dept Station 1'; '847-555-1217'
Record 4: 'Highwood, IL'; 'Chicago Fire Dept Station 12'; '847-555-1218'

A linked table (call it 'DTable') to hold States and their USPS Abbreviations. Fields: An auto-increment Integer ID field (call it 'DID'), a State name Text field, and a State abbreviation Text field.
Record 1: 'Georgia'; 'GA'
Record 2: 'Illinois'; 'IL'
Record 3: ..., etc.

An integrated form that displays:

• 'BTable'.'City/Region' but saves the selected 'BID' into ATable's 'BLink' field when a new ATable record is created

• 'CTable'.'City/Region' but saves the selected 'CID' into ATable's 'CLink' field when a new ATable record is created

• 'DTable'.'State' field but saves the selected 'DID' into ATable's 'DLink' field when a new ATable record is created.

The form would also display 'BTable'.'Dept' as Read-only, 'BTable'.'Phone' as Read-only, 'CTable'.'Dept' as Read-only, 'CTable'.'Phone' as Read-only, 'DTable.State' as Read-only, 'DTable.Abbr' as Read-only, 'ATable'.'Client' as Add/Edit/Delete, and 'ATable'.'Account' as Add/Edit/Delete. The form must be able add new data to all ATable fields (even though the new record does not yet contain data in the 'ALink', 'BLink', and 'CLink' fields) and allow the user to select the appropriate records from BTable, CTable, and DTable to populate the 'ALink', 'BLink', and 'CLink' fields.

Now, I've been able to manually pre-populate the 'ALink', 'BLink', and 'CLink' fields in an ATable record by entering data directly into ATable without a form, and I've been able to create forms that display the appropriate BTable, CTable, and DTable records, but I have not been able to create a form that allows creating new ATable records while selecting the appropriate records from BTable, CTable, and DTable.

I want to create a form that REDUCES stress and the time data-entry takes for an novice data-entry users.

Can anyone tell me, step-by-step, from table creation to table linking to form creation to field (list box and/or combo box) creation to SQL statements that will produce a stable, working form that allows both existing records review and new records addition?

This is a case where it is easier to show than to tell. See if the attached example file is close to what you need.

Open the form in Design/Edit mode by right-clicking on the form name and choosing "Edit". Once the form is open, you can switch Design Mode on and off by clicking on the toolbar icon that looks like a triangle and a pencil.

Look around the form with the Form Navigator (make sure the toolbar "Form Design" is visible in View→Toolbars; the icon to open the Form Navigator is the sixth from the left or top on the Form Design toolbar and looks like a rectangle with a compass in the upper right corner; it can only be selected if Design Mode is 'on').

Look through the properties windows of the controls, forms, and subforms (in the Form Navigator, right-click on a name and choose "Properties" to open the properties window; once open, single-left-clicking on a control or form name will switch the focus of the properties window to that new control or form).

Let us know what questions come up and we can explain things on a case-by-case basis.

This works perfectly, as I had envisioned. But, I do have questions. (Of course I do!)

First, when I've tried to create forms with linked tables (using the form creation wizard), I've been able to include only ONE linked table. How did you put three linked tables onto the form?

Second, when I opened the Relationships window, it doesn't look like the tables are linked or related. How does the form create the links if they are not established at the Table level of the database?

Third, when I view the properties of the individual fields of ATable in the Form1 form, I see that the columns for BLink, CLink, and DLink are List Boxes. I understand the SQL statements. But when I look at the "properties" of the BLink, CLink, and DLink fields in "Edit->ATable" there are no new properties. When I open the ATable directly for data entry and right-click on the column headings for BLink, CLink, and DLink, there are no new properties. How did the columns become Link Boxes in the Table Control for ATable?

Fourth, the actual database I will be designing, once I've wrapped my head around my initial problem and your very well executed solution, will have 156 fields. The form I will be designing will display a single ATable record at a time. I notice that the BTable, CTable, and DTable subforms all exist in their own segments of the layout page. If my ATable form includes a block of data arranged by address, will I be able to display form fields for "Address", "City", "DTable"."StateAbbr", "Zip" or will the "DTable"."StateAbbr" be required to stay in the DTable segment of the form? (I *want* the police and fire fields to stay in their appropriate segments, by the way.)

Fifth, since I want to display a single record at a time for ATable, will I be able to create List Box fields for BLink, CLink, and DLink that still work properly or do they only work as part-and-parcel of a Table Control for ATable?

A quick note on a confusing way Base uses the word "form": this word is used to refer both to a "form document" - the entire document, which is actually a Writer file embedded inside Base - and to a "collection of controls and/or subforms" that is a subset of the "form document". In the case of the example, the "form document" titled "Form1" has a top-level "form" titled "ATable" with a gridcontrol titled "Table Control A" and the "form" "ATable" has three "subforms" "BTable", "CTable", and "DTable" which each contain a gridcontrol and a pushbutton control.

The form creation wizard is very limiting, including (as you've discovered) only being able to create a single top-level form and a single subform. Design mode is much more powerful: create an unlimited number of top-level forms by right-clicking on the label "Forms" in the Form Navigator window and choosing "new". Similarly for subforms, right-click on the name of the form you want to make a subform for and choose "new". Controls, forms, and subforms can also be moved between forms in the form navigator by left-click-hold-drag-to-new-location-and-drop.

I have set up linked fields between the subforms and the master form. If you open the properties window of one of the subforms, on the tab "Data" notice the fields "Link master fields" and "Link slave fields" are filled in. You can edit this by clicking the "..." button to open the link field wizard.

I made the listboxes by first creating a gridcontrol (it's on the "more controls" toolbar that you open by pressing the Form Controls icon with a circle and a square above three dots) - click the tablecontrol icon, left-click-and-hold where you want a corner to be, drag to the size you want, then release the mouse. I like having the controls wizard on (the Form Controls icon of a wand is depressed), so when I make a gridcontrol the table element wizard pops up. I selected all the fields I wanted to save and it makes the table. Next I right-clicked on the BLink field and choose Replace→ListBox. (If you didn't have the controls wizard on, you would right-click on the top bar of the table and choose Insert→ListBox.)

The cool thing about listboxes is they display information based on the first field in their SQL statement, but they save/read the information in the second field of the SQL statement based on the form they are part of. So in the case of the BLink listbox, the SQL refers to the table BLink, so that's the information it displays. The control "Table Control A" is located inside the form "ATable", which if you look at the properties window, tab "Data", has Content from "ATable". So the listbox is going to save its data into ATable. Specifically, if you look at the properties window, tab "Data" for the column BLink, the "Data field" is set to save to/read from the column named "BLink".

You could certainly create standalone listboxes that would have the same functionality, displaying one record at a time instead of a list inside a gridcontrol.

I don't understand your fourth question, would you explain a bit more what you're looking for?

MTP mentioned the Form Navigator in his post, but being new the Base you may not be familiar with this tool. It can be very helpful when designing/editing a form document. It shows the structure of the form document and it also has some editing capabilities. It is 4th from the left on the tool bar along the bottom of the Design mode window.

Re: My fourth question....
I work for a security monitoring company. When a security incident happens, we need to pull up the client's account to determine the appropriate response. Some clients want us to call the police. Some want us to call their own emergency points of contact. Some have guards posted and we coordinate with them. Some are close enough that we send our own patrol units. Some are too far away for our own patrol units but they have their own patrol units or have contracts with patrol companies. Sometimes the security incident involves equipment malfunctions and we need to contact maintenance personnel or IT personnel.

I'm trying to design a form that displays all the client information (as I said, there are 150+ fields per client) on one screen, one client at a time. This screen needs to display the local police and fire information that pertains to that client as well as the client's physical address so we can send patrol units, police, and fire. This screen also needs to be able to update police and fire links when adding a new client or if a large client builds a new facility that will be covered by a different police or fire record from the police and fire tables.

Here is a visual sample of what I'm talking about (a screen shot of the form I've got now that doesn't work properly with the subforms.)


https://www.facebook.com/photo.php?fbid ... =3&theater

This is a job for a professional developer with professional development tools.

Are you saying, Villeroy, that it can't be done in OpenOffice? Because I've done even more complex things in Microsoft Access. It's just that my employer does not have or use Microsoft products, so now I have to relearn everything in OpenOffice Base.

I think I understand a bit better now.

In my example, I put tablecontrols/gridcontrols in each subform because that was the fastest way to get all the fields into the form and I wasn't spending much time on it I see in your design it makes more sense to spend the extra setup time to have individual controls. With individual controls you can mix up the coordinate location of different form and subform controls in whatever way makes sense to your user. Which form or subform a control "belongs" to is entirely controled by the Form Navigator window and is independent of the coordinates on the form document.

I suspect your critical decisions will be less in form design and more in making relational design decisions (e.g. should the POC table have three columns for phone numbers with three text boxes for display on the form - or should there be a phone number table with a foreign key to the POC_ID with all phone numbers displayed in a tablecontrol - the phone number table would be more complicated to set up but would then easily accommodate the POC that has four phone numbers).

In Base, compared to Access, it is unfortunately much more technically challenging to set up forms with many relationships such as what you are looking at. Certainly doable, just takes some time and head-wrapping. You may find it beneficial to look at the examples in this thread: [Example #1] Filter/Search with Forms (leveraging SubForms)