[Solved] My forms keep giving me errors

Creating and using forms
Post Reply
IHaveNoName
Posts: 20
Joined: Sun Apr 30, 2017 1:37 am

[Solved] My forms keep giving me errors

Post by IHaveNoName »

I've been trying to learn OpenOffice Base so that I can update our database at work. I've got the basics down - I can create tables and forms, modify things, and get everything set up more or less properly - but I'm running into a few small problems.

We do furniture restoration, so each job has a bunch of info about the particular piece we're working on, as well as customer information. I'm trying to set up "job info" (i.e., information about the piece itself) and "customer info" as separate but linked forms. Attached is the latest version; it's still a little crude because I haven't done all the formatting to make it pretty - I was trying to get it to work first, but Base keeps throwing errors at me and I can't find out what they mean.

So, could someone take a look at this, tell me what I'm doing wrong, and how to fix it? I've been trying to wrap my head around this for a week now, and I'm totally burned out. Any and all help would be most welcome.

(PS I'm using OO 4.1.3 on Win 7.)
Attachments
Database.odb
(33.24 KiB) Downloaded 342 times
Last edited by Hagar Delest on Wed May 03, 2017 5:21 pm, edited 1 time in total.
Reason: tagged [Solved].
Open Office 4.1.3 on Windows 7
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: My forms keep giving me errors

Post by UnklDonald418 »

You didn't detail what error message(s) you were referring to but the Relationship you have defined between your Customer Info table and Table1 appears to be problematic.
Relational databases are not intuitive, they require learning about proper design. Looking at your tables it is obvious you haven't done that because they violate the rules of normalization for relational databases. Once the tables are correct THEN you can define Relationships and finally design Forms and Reports.

Customer Info table.
CustID is the primary key, but it is a Text field, which is generally not a good idea. If you assign CustID values based on some predefined algorithm then add a new Primary Key like the one in your Job Info table. If you can live with the database automatically assigning a CustID then change it to an Integer and set the AutoValue property to Yes.
You have 3 Numeric Phone number fields which means that you should have a separate table for phone numbers. I'm not sure Numeric type is the best choice for phone numbers

Job Info table.
You appear to have repeated the customer name fields from the Customer Info table, which isn't good. Replace those with an Integer field that links to the Primary Key field in the Customer Info table.
You have a triplet of repeating fields which means you should have another table for bids.
The Departments field looks a little odd, but since I don't fully understand it I won't comment any further on it.

Table1 table.
I'm not sure why you have this table. I don't think you need it unless it is possible to have more than one customer for a given job.

Those are the issues that I noticed from a quick look, so there may well be others that I missed.

A tutorial on database design that I found helpful can be downloaded from:
https://wiki.documentfoundation.org/ima ... torial.pdf
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
IHaveNoName
Posts: 20
Joined: Sun Apr 30, 2017 1:37 am

Re: My forms keep giving me errors

Post by IHaveNoName »

UnklDonald418 wrote:You didn't detail what error message(s) you were referring to but the Relationship you have defined between your Customer Info table and Table1 appears to be problematic.

Relational databases are not intuitive, they require learning about proper design. Looking at your tables it is obvious you haven't done that because they violate the rules of normalization for relational databases. Once the tables are correct THEN you can define Relationships and finally design Forms and Reports.
Yeah. That's why I'm asking for help. :P

And sorry, I was tired when I posted this. Ignore Table 1 - I was just trying to do a short-form Jobs table so I could figure out the relationships.
Customer Info table.
CustID is the primary key, but it is a Text field, which is generally not a good idea. If you assign CustID values based on some predefined algorithm then add a new Primary Key like the one in your Job Info table. If you can live with the database automatically assigning a CustID then change it to an Integer and set the AutoValue property to Yes.
I'd be fine with using an auto-incrementing value for the table; all I need is the ability to look up a job and see who it belongs to (i.e., the customer name). I was thinking about using some kind of code for CustomerID, but then I'd have to keep some kind of list so we knew who was who, and... it all got muddled in my head. :knock:

Oh, and I tried adding a new Primary Key, but I get this error (see attachment).
Job Info table.
You appear to have repeated the customer name fields from the Customer Info table, which isn't good. Replace those with an Integer field that links to the Primary Key field in the Customer Info table.
I guess I don't understand how the relationships are suppsed to work. If I use an integer link, is that the only thing that'll show up when you pull up the job form? I mean, what I'd like is to have the customer information displayed when you pull up a job record - that way, we know who a particular piece belongs to. Likewise, we could look up a customer and see what job(s) they currently have, or have had in the past.
You have a triplet of repeating fields which means you should have another table for bids.
Huh? Triplet of repeating fields?
The Departments field looks a little odd, but since I don't fully understand it I won't comment any further on it.
That denotes which department(s) the job goes through. We have 7, so they'd be listed as numbers, depending on which ones are valid: e.g., 2, 4, 5. Thus, it's just a simple text field so
we can add commas.
A tutorial on database design that I found helpful can be downloaded from:
https://wiki.documentfoundation.org/ima ... torial.pdf
I tried reading that one, but it just went over my head. I'm reasonably intelligent, but this stuff is hard to grasp. All I really want is a simple two-table database that we can use to store and look up data. I'm not interested in reports, spreadsheets, or all the other bells and whistles. I know, it's like saying "I want to buy a Porsche so I can drive to work every day", but there it is. :P
Attachments
base error 1.png
Open Office 4.1.3 on Windows 7
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: My forms keep giving me errors

Post by UnklDonald418 »

Huh? Triplet of repeating fields?
Sorry, I now see there are 4 not 3 repeating fields

(1)Date, (1)Employee, (1)BidChg, (1)Act.Chg

(9)Date, (9)Employee, (9)BidChg, (9)Act.Chg
I mean, what I'd like is to have the customer information displayed when you pull up a job record - that way, we know who a particular piece belongs to. Likewise, we could look up a customer and see what job(s) they currently have, or have had in the past.
I made the recommended changes to your database and uploaded an example for you to try.
It now has 4 tables
The Customer Info form now has a table that displays an editable list of phone numbers from the PhoneNum table. It also has another table that displays a read only list of jobs, both completed and open.
The Job Info form has a ListBox that allows you to select a Customer by name from the Customer Info table, but it stores the selection as the CustID value. This form also displays the Customer address and any phone numbers for this customer in read only fields. It also has an editable table for any data in the Bids table for this job.

These are quick and dirty changes, but should give you a sample of what is possible.
Attachments
Database(1).odb
(28.68 KiB) Downloaded 339 times
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
IHaveNoName
Posts: 20
Joined: Sun Apr 30, 2017 1:37 am

Re: My forms keep giving me errors

Post by IHaveNoName »

That's awesome. I'm going to play around with this a bit and get a feel for it. Thank you so much for the help. I do have one question, though: I need to add the department number to the Bids table, because we need to know who's doing what work (the employees often do work in more than one department, so it's necessary to keep track). How can I do that? I added that line to the table, but it's not updating in the form.

Edit: Figured out how to add the column, and I even got a valuelist for it! :mrgreen: BUT.... when I turn off Design View and try to enter data in that column (i.e., hit the dropdown to choose a number), all I get is a blank space. Here's what I did: Right-click, Column, under the Data tab, enter Dept as the Data Field, choose Valuelist and listed some numbers (1, 1P, 2, 3, 4). Is there something I'm missing?
Open Office 4.1.3 on Windows 7
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: My forms keep giving me errors

Post by UnklDonald418 »

You will need to Edit the Job Info form to add a column to the table control.
In the Edit mode click on the table control showing the bids data, and expand the width of the table by dragging the handle in the middle of the right border to the right. There should now be a blank area along the top of the table control with the column headings. Right click on the blank area and you should see a pop-up to Insert Column. Click on that to get a list of available controls and select the type that matches the type of the new field you added.
Now right click on the new column and select Column from the list of options that pop up. You should then see the the Properties Dialog. Select the Data tab and click on the drop down arrow to the right of the Data field property and select your new field from the list. On the General tab change the Label property to the name you want to appear at the top of the column. You can rearrange the order of the columns using click and drag.
Be sure to Save your changes.
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
IHaveNoName
Posts: 20
Joined: Sun Apr 30, 2017 1:37 am

Re: My forms keep giving me errors

Post by IHaveNoName »

One last question (hopefully): I'd like the Customer dropdown in the Job Info form to be ordered by last name, then first name so it's easier to find people. I was looking at the SQL command and it looks right (according to this site: https://www.tutorialspoint.com/sql/sql-order-by.htm, but they're showing up as first name/last name.
Open Office 4.1.3 on Windows 7
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: My forms keep giving me errors

Post by UnklDonald418 »

I said it was quick and dirty.
The current SQL command for the List Box is:

Code: Select all

SELECT CONCAT( CONCAT( [FirstName], ' ' ), [LastName] ) AS "Customer", "CustID" FROM "Customer Info" ORDER BY "LastName" ASC,"FirstName" DESC
I was playing with that statement and apparently uploaded it before correcting the sort order. Change the final DESC to ASC to correct the sorting order of the first name.
If you want the names to appear on the list as “LastName, FirstName” change the CONCAT statements to

Code: Select all

CONCAT( CONCAT( [LastName], ', ' ), [FirstName] ) AS "Customer"
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
IHaveNoName
Posts: 20
Joined: Sun Apr 30, 2017 1:37 am

Re: My forms keep giving me errors

Post by IHaveNoName »

Aha. I thought that might be it, so I tried that myself; it didn't work, but I think I just didn't save it properly. I tried it just now and it worked. Horray. Again, thanks a lot.
Open Office 4.1.3 on Windows 7
IHaveNoName
Posts: 20
Joined: Sun Apr 30, 2017 1:37 am

Re: [Solved] My forms keep giving me errors

Post by IHaveNoName »

Argh. Okay, I'm stupid. I can see how you set up all the cells, but not the form itself. I need to add another field, so I had to redo the form. Fine... but how do you get the customer info in there? I can add the bids subform just fine, but I can't for the life of me figure this one out.
Open Office 4.1.3 on Windows 7
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: [Solved] My forms keep giving me errors

Post by UnklDonald418 »

It isn't clear which form you are referring to. Also, what data type is the field are you adding to which table?
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
IHaveNoName
Posts: 20
Joined: Sun Apr 30, 2017 1:37 am

Re: [Solved] My forms keep giving me errors

Post by IHaveNoName »

Oh, sorry. I need to add a new numeric field to the Job Info table - the one you helped me with before. How did you get the Customer info fields to show up?
Open Office 4.1.3 on Windows 7
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: [Solved] My forms keep giving me errors

Post by UnklDonald418 »

Perhaps a brief overview of Forms would be helpful for you.
One facet of Base that can be confusing is that the word Form can have two meanings.
There are Form documents such as Customer Info and Job Info. These are Writer documents that are connected to the underlying database tables. In fact they can be saved as stand-alone forms (if you are interested there is a Tutorial in the Base Tutorials section of this forum).
The confusion begins because each Form document contains a collection of data Forms. In your case the forms collection for Job Info contains MainForm, SubFormBids, SubFormCust and SubSubFormPhone. Data Forms are data aware, they have access to the database tables but they can't actually display the data on the screen.
That is where Controls enter the picture. Controls can display data on the screen, but they are not data aware, so they must get their data from an associated data Form.
The Form Navigator is a valuable tool because it allows you to visualize and edit those connections.
Select a data Form and open its Properties dialog. The the first entry under the Data tab is Content type, this is where data awareness begins. This can be connected directly to a Table, via a saved Query, or SQL query statement. Next is the Content line which allows the choice of which Table or saved Query from the list shown when the list box down-arrow is selected. An SQL query statement can be entered either directly or by clicking on the ellipsis (…) to open a query dialog.
To actually display any table data on the screen a Control must be associated with one of the data Forms. When a Control is selected its Properties dialog can be opened to the Data tab.
The Data field property contains a list of available fields for this control to display. If the field you want to display isn't on this list then either the control is associated with the wrong data Form, or the data Form isn't currently supplying that field.
In the first case, the Form Navigator can be used to drag the Control to the proper data Form.
In the second case, the Content of the data Form must be edited.

Note that a table Control is actually a collection of columns of Controls so it doesn't have a Data tab in its Properties dialog. However, each Column on that table does have a Data tab, and can be connected to a Data field.
Of course label Controls have no data associated with them, but they are often grouped with a Control that does display data.
List boxes and Combo boxes are special Controls that have additional properties on the Data tab used to determine the contents of the list.
How did you get the Customer info fields to show up?
The Customer info field data is found on SubFormCust. Using the Form Navigator select SubFormCust and open its Properties dialog (right click on SubFormCust and select Properties) and look at the Data tab. There are two lines Link Master fields and Link Slave fields which show which field (CustID in this case) on the Master Form (MainForm in this case) is linked to which field (CustID in this case) on the Slave field ( SubFormCust in this case). Clicking on the ellipsis next to Link Master fields opens a dialog that lists the fields available on both Forms (the ellipsis next to Link Slave fields shows the same dialog). The result is that the Slave form only selects data from a record where the Master and Slave fields match.
I need to add a new numeric field to the Job Info table
I am assuming you have added the numeric field to the Job Info table using the GUI ( according the HyperSQL User Guide, “NUMERIC and DECIMAL are types with user-defined decimal precision. They can be used with zero scale to store very large integers, or with a non-zero scale to store decimal fractions.”)
Since it will be associated with the Job Info table, use the Form Navigator to select the MainForm, then select the control type you want to use from the Form Controls tool bar (probably Formatted field, Numeric field, or Currency field) and place the Control where you want it to appear. Once it is in place right click on the new control and select Control to display the Properties dialog. Since MainForm is directly connected to the Form Info table, you should be able to select your new field from the list of Data fields. That should make your connection complete.
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
IHaveNoName
Posts: 20
Joined: Sun Apr 30, 2017 1:37 am

Re: [Solved] My forms keep giving me errors

Post by IHaveNoName »

Thanks for taking the time to hold my hand through this. I got most of the initial explanation.

I was trying to duplicate the form you built, so I could see for myself how it was done from the ground up. The problem is, Customers didn't show up as a subform - I didn't edit the tables or relationships at all, except to add a new line in the Job Info table for Job Number (more on that later). I thought CustInfo was a subform, so I was understandably confused when it didn't appear. Naturally, SubSubPhone didn't show up either.
I am assuming you have added the numeric field to the Job Info table using the GUI.
If you mean right-click on the JobInfo table, Edit, and add new cell, then yes. I want a field that will hold a number, with or without decimals. I figured out how to add a new field and changed it to a numeric, but when I tried to find the name field in the list, it wasn't there. Did I add that line to the table wrong?
Open Office 4.1.3 on Windows 7
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: [Solved] My forms keep giving me errors

Post by UnklDonald418 »

First make sure the new field actually exists in the table.
If you double click on the Job Info table you should get a display of the raw data in the table. Make sure there is a column for your new field.

Next check the Properties for the new Control you added to the Form, looking at the Data field list to verify that it contains fields from the Job Info table. If it does have Job Info table fields then your new field should be on the list, probably the last item.
If you are not seeing fields from the Job Info table then the Control is associated with the wrong Form. In that case using the Form Navigator click and drag the control onto the MainForm. If you are successful your new field should now appear on the Data field list.

To add SubForms to an existing Form use the Form Navigator. On the Job Info form I right-clicked on MainForm and selected New->Form. Then I right-clicked that new Form and opened the Properties dialog. On the General tab I changed the Name to SubFormCust, and on the Data tab I chose SQL command as the Content type and clicked on the ellipsis on the Content line and used the query dialog to chose the fields I wanted for to display on the form. I chose the CustID field, not because I wanted to display it, but because I knew I would need it for the next step.
I clicked on the ellipsis on the Link Master fields line and selected CustID from the lists for both Job Info and Sub Form.

Then back to the Form Navigator where I right-clicked on SubFormCust and selected New->Form to create the SubSubFormPhones using the same procedure that I used for the SubForm.
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
IHaveNoName
Posts: 20
Joined: Sun Apr 30, 2017 1:37 am

Re: [Solved] My forms keep giving me errors

Post by IHaveNoName »

UnklDonald418 wrote:First make sure the new field actually exists in the table.
If you double click on the Job Info table you should get a display of the raw data in the table. Make sure there is a column for your new field.
It is. So far so good.
Next check the Properties for the new Control you added to the Form, looking at the Data field list to verify that it contains fields from the Job Info table. If it does have Job Info table fields then your new field should be on the list, probably the last item.
If you are not seeing fields from the Job Info table then the Control is associated with the wrong Form. In that case using the Form Navigator click and drag the control onto the MainForm. If you are successful your new field should now appear on the Data field list.
And you lost me. I have it set to the Job Info table, but I can't drag it from the Form Navigator. I also tried this method:
* Open Form Navigator
* Click Text Box on the Form Controls bar, and placed the field.
* Open the controls for the new field, go to DataField, and select Job Number from the Job Info able.
* After that, I can' assign a label field, so I just have a blank field.
To add SubForms to an existing Form use the Form Navigator. On the Job Info form I right-clicked on MainForm and selected New->Form. Then I right-clicked that new Form and opened the Properties dialog. On the General tab I changed the Name to SubFormCust, and on the Data tab I chose SQL command as the Content type and clicked on the ellipsis on the Content line and used the query dialog to chose the fields I wanted for to display on the form. I chose the CustID field, not because I wanted to display it, but because I knew I would need it for the next step.
I clicked on the ellipsis on the Link Master fields line and selected CustID from the lists for both Job Info and Sub Form.

Then back to the Form Navigator where I right-clicked on SubFormCust and selected New->Form to create the SubSubFormPhones using the same procedure that I used for the SubForm.
Ahh, I get it now. Thanks.
Open Office 4.1.3 on Windows 7
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: [Solved] My forms keep giving me errors

Post by UnklDonald418 »

And you lost me. I have it set to the Job Info table, but I can't drag it from the Form Navigator.
If you have it set to the Job Info table then all is well. You only need to drag it if it is assigned to one of the SubForms.
Open Form Navigator
* Click Text Box on the Form Controls bar, and placed the field.
* Open the controls for the new field, go to DataField, and select Job Number from the Job Info able.
If Job Number is the new field you added to the Job Info table, then the connection should be complete.
However, it your new field is Numeric as you said earlier, then a Text Box control might not be your best choice.
After that, I can' assign a label field, so I just have a blank field.
In order assign a Label field it must first exist. The Label Field control is towards the bottom of the Form Control tool bar. It is marked ABC like the Text Box Control but it has no rectangle surrounding it. It works like the other controls, so add a new Label Field to your form. Open the Properties dialog for the new Label and you can edit the Name and Label as well as other formatting options.
Now reopen the Properties dialog for the new data Control and click on the ellipsis on the Label line to select the new label from the list of available labels.
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: [Solved] My forms keep giving me errors

Post by UnklDonald418 »

I forgot to mention this earlier, but in your Customer Info table you have Zip set to NUMERIC type. Even though they are composed on numerals, some Zip Codes can be very difficult to represent using a NUMERIC type.
They are best saved as VARCHAR type. I usually use VARCHAR(10)
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
IHaveNoName
Posts: 20
Joined: Sun Apr 30, 2017 1:37 am

Re: [Solved] My forms keep giving me errors

Post by IHaveNoName »

Aha, got it now. Thanks. I know what you mean about zip codes (some are hyphenated); we just use the standard 5 digits, but I'll change it anyway, just in case.
Open Office 4.1.3 on Windows 7
IHaveNoName
Posts: 20
Joined: Sun Apr 30, 2017 1:37 am

Re: [Solved] My forms keep giving me errors

Post by IHaveNoName »

I'm back. Rather than start a new thread, I'll just keep using this one. :P

Okay, so here's my question/problem: I'm using the form discussed above (see attachment). Now, this form is slightly different than the one we have at work - I added an extra radio button to that one, but it's otherwise unchanged as far as relations and such. The customer form is linked to the Jobs form, such that if you select a name on the Jobs form, their info will appear on the gray boxes on the right. Or, at least it's supposed to; it did initially, but I broke it somehow. I can add new customer entries, and the names will appear on the dropdown list in the Jobs form, but their information won't appear like it's supposed to.

The one at work has the same issue, except that despite adding multiple customer forms, the dropdown menu only has the first name added - none of the others appear at all.

Both systems are Win7; mine is 64-bit and the other is 32-bit.
Attachments
BOFR.odb
(30.65 KiB) Downloaded 358 times
Open Office 4.1.3 on Windows 7
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: [Solved] My forms keep giving me errors

Post by UnklDonald418 »

When I open your new Job Info form in the edit mode and select the Form Navigator I see that all the controls related to the customer information are now on the MainForm.
For them to work they must be on SubFormCust and the Table Control 1 related to the phone numbers should be on SubSubFormPhone.
You added Notes to the form. It also needs to be on SubFormCust with the other customer information. Plus for that to show data, the SQL statement for SubFormCust needs to be modified to include Notes.
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
IHaveNoName
Posts: 20
Joined: Sun Apr 30, 2017 1:37 am

Re: [Solved] My forms keep giving me errors

Post by IHaveNoName »

I'm not sure how those ended up there, but I figured out how to fix them - just drag and drop in Form Navigator until they appear under suFormCust (like the screenshot I attached, right?) - but the info still doesn't show up.

The Notes field is specific to the Jobs form, not carried over from Customer Info, but I could see how you'd think that, given where it is.
Attachments
FormNav.jpg
Open Office 4.1.3 on Windows 7
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: [Solved] My forms keep giving me errors

Post by UnklDonald418 »

I'm not sure how those ended up there, but I figured out how to fix them - just drag and drop in Form Navigator until they appear under suFormCust (like the screenshot I attached, right?) - but the info still doesn't show up.
Using the BOFR.obd you uploaded, when I drag the controls onto the proper SubForms it works for me. Did you make some changes after you uploaded it? Make sure the SQL statement is correct for SubFormCust, and make sure the Master and Slave links are correct.
BOFR.jpg
BOFR.jpg (33.94 KiB) Viewed 15386 times
The Notes field is specific to the Jobs form, not carried over from Customer Info, but I could see how you'd think that, given where it is.
That's where naming table fields becomes important. To avoid confusion you should rename them to something like JobNotes and CustNotes. That would be helpful for anyone trying to understand your database design.
Likewise, controls on your forms should be named to better reflect what their function is.
Odds are that at some future date you will need to make some changes to your database and will have forgotten some of those details.
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
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] My forms keep giving me errors

Post by Villeroy »

Check it systematically.

Image

A subform is filled by some table or query (does it exist and does it deliver any data?). Or the form is filled by its own SQL statement like the one in the screen shot. The little [...] button near the SQL query opens up a query editor so you can test if the query actually delivers some row set.

A subform is filtered by its parent form through master/slave fields. There is a little dialog when you hit the [...] button near "Link master/slave fields" which allows you to assign pairs of exisiting fields. Without the dialog you can type in the field names, multiple fields in the same box separated by Shift+Enter (new line). After finishing the entries, the multiple field values are displayed as "quoted" names separated by semicolons.

Each data aware form control has a linked field which has to be linked to an existing column of its hosting (sub-)form. The drop down of the "Linked field" property shows all availlable fields.
A grid control is not data aware. A grid control is a bundle of data aware controls so you have to check its columns for the right data binding.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
IHaveNoName
Posts: 20
Joined: Sun Apr 30, 2017 1:37 am

Re: [Solved] My forms keep giving me errors

Post by IHaveNoName »

UnklDonald418 wrote:Using the BOFR.obd you uploaded, when I drag the controls onto the proper SubForms it works for me.
Yeah, the existing records show the customer information. Did you try making a new one? If I do that and select an existing Customer from the dropdown, their info doesn't appear on the right.
Did you make some changes after you uploaded it? Make sure the SQL statement is correct for SubFormCust, and make sure the Master and Slave links are correct.
I added another radio button, but that shouldn't have done anything. Just to make sure, I went back to the original and tried again - no luck. The SQL statement looks right:

SELECT "CustID", "FirstName", "LastName", "Address", "City", "State", "Zip" FROM "Customer Info"

and the Master/Slave links are set to CustID, which is primary key in the Customer form and foreign in the Job Info form.
That's where naming table fields becomes important. To avoid confusion you should rename them to something like JobNotes and CustNotes. That would be helpful for anyone trying to understand your database design. Likewise, controls on your forms should be named to better reflect what their function is. Odds are that at some future date you will need to make some changes to your database and will have forgotten some of those details.
Good point. I'll do that.
Open Office 4.1.3 on Windows 7
IHaveNoName
Posts: 20
Joined: Sun Apr 30, 2017 1:37 am

Re: [Solved] My forms keep giving me errors

Post by IHaveNoName »

Villeroy wrote:Check it systematically.

Image

A subform is filled by some table or query (does it exist and does it deliver any data?). Or the form is filled by its own SQL statement like the one in the screen shot. The little [...] button near the SQL query opens up a query editor so you can test if the query actually delivers some row set.

A subform is filtered by its parent form through master/slave fields. There is a little dialog when you hit the [...] button near "Link master/slave fields" which allows you to assign pairs of exisiting fields. Without the dialog you can type in the field names, multiple fields in the same box separated by Shift+Enter (new line). After finishing the entries, the multiple field values are displayed as "quoted" names separated by semicolons.

Each data aware form control has a linked field which has to be linked to an existing column of its hosting (sub-)form. The drop down of the "Linked field" property shows all availlable fields.
A grid control is not data aware. A grid control is a bundle of data aware controls so you have to check its columns for the right data binding.
Checked all those, as I mentioned. They're good, which is what makes it so frustrating - it should work.
Open Office 4.1.3 on Windows 7
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: [Solved] My forms keep giving me errors

Post by UnklDonald418 »

Yeah, the existing records show the customer information. Did you try making a new one? If I do that and select an existing Customer from the dropdown, their info doesn't appear on the right.
Then everything is working properly.
When entering a new record all the data entered on the form remains in memory until the record is saved. As long as the JobID control shows <AutoField> the customer information will not be populated because SubFormCust is linked to a record that does not yet exist in the Job Info table.

If it is really important to see those fields populated immediately you could add a Button control to MainForm. Scroll down on the General tab of the Button Properties dialog and set the Action to Save record. Then after selecting a customer push the Button to have the customer information fields populated.
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
IHaveNoName
Posts: 20
Joined: Sun Apr 30, 2017 1:37 am

Re: [Solved] My forms keep giving me errors

Post by IHaveNoName »

Ahhh, I see. I thought it was supposed to pop up immediately, but it's not vitally important that it does. I see how it works now. Thanks again.
Open Office 4.1.3 on Windows 7
Post Reply