[Solved] Convert a new record form to an edit record form?

Creating tables and queries
Post Reply
Maax555
Posts: 127
Joined: Tue Mar 28, 2017 11:56 am

[Solved] Convert a new record form to an edit record form?

Post by Maax555 »

So I have a basic form which is working ok for adding new records. The form only has basic information such as part number, description and customer.
Once this is entered into the database it will then be picked up by another employee who will then add other details such as cost, time and size Etc.
For the second form lets call it PartNumberAdd I have simply copied the initial form, deleted details not relevant and added new fields which will be updated and the record saved. SO for this I would like to change the existing PartNumber field to a listbox that will list the parts in the system but filtered against the customer. I managed to so far make the listbox show all harnesses (will add the customer filter once i get this bit working) and once i choose a harness the PartNumberID is correctly also chosen. However when i make a change and press save I am getting an error as its trying to add a duplicate PartNumberID.
So its trying to add a new record but i am wanting to modify. I have checked the form and it has allow modifications.
Last edited by Maax555 on Thu Sep 27, 2018 10:35 am, edited 1 time in total.
Regards Maax
LibreOffice 6.1.4.2 on Windows 7
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Convert a new record form to an edit record form?

Post by Villeroy »

Add data only = No
Image
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
Maax555
Posts: 127
Joined: Tue Mar 28, 2017 11:56 am

Re: Convert a new record form to an edit record form?

Post by Maax555 »

I will double check but pretty sure this is already set to NO.

thanks
Regards Maax
LibreOffice 6.1.4.2 on Windows 7
Maax555
Posts: 127
Joined: Tue Mar 28, 2017 11:56 am

Re: Convert a new record form to an edit record form?

Post by Maax555 »

checked and have the ADD ONLY set to off. Here is the message I am getting
error1.JPG
.
Attachments
error2.JPG
Regards Maax
LibreOffice 6.1.4.2 on Windows 7
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Convert a new record form to an edit record form?

Post by Villeroy »

The error message says it all. You are trying to change a PartNumberID so it becomes duplicate.
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
Maax555
Posts: 127
Joined: Tue Mar 28, 2017 11:56 am

Re: Convert a new record form to an edit record form?

Post by Maax555 »

Villeroy wrote:The error message says it all. You are trying to change a PartNumberID so it becomes duplicate.
ok, so my aim is to recall a record using the PartNumber Listbox. Choosing the PartNumber from the Listbox does choose the correct PartNumberID.
I then wanted to update the details below on the form and save the record. Am I going about this totally wrong?
Thanks
Attachments
edit1.JPG
Regards Maax
LibreOffice 6.1.4.2 on Windows 7
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Convert a new record form to an edit record form?

Post by Villeroy »

The error is not in the list box.
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
Maax555
Posts: 127
Joined: Tue Mar 28, 2017 11:56 am

Re: Convert a new record form to an edit record form?

Post by Maax555 »

Villeroy wrote:The error is not in the list box.
Hi, sorry, yes i think i realise the error is not in the listbox, however I was trying to show what I had in place.
I see that it is stopping me from adding a duplicate which is good, however I changed form properties to allow modifications etc and thought this would allow the record to be saved (modified) rather than attempting to save a duplicate.
Regards Maax
LibreOffice 6.1.4.2 on Windows 7
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Convert a new record form to an edit record form?

Post by Villeroy »

Yes, you should be able to modify (update) the record.
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
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Convert a new record form to an edit record form?

Post by Villeroy »

A subform is filtered by the corresponding values of its parent form.
The corresponding fields are set up as master and slave fields in the subform's data properties.
Slave fields can also be the names of parameters, so you substitute parameters with the corresponding fields of the parent form.

The subform is filtered by the given values, so it includes only those records where the slave fields match their corresponding master fields.
Any slave field in a new subform record inherits its value from its master field.

If you filter a subform by primary keys, the subform is always populated by one record since the primary key is unique by definition. Same with any other unique index.
But then you should restrict the form to now allow new records because the slave field will inherit its value from its master field, so you are going to create a duplicate record.
A subform for new records only makes no sense when its given master values pass over existing primary key values. Every new record will be invalid.

HOWEVER, the error message describes an UPDATE statement whereas the insertion of a new record would be an INSERTion. From screenshot it is very hard to tell what is happening.
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
Maax555
Posts: 127
Joined: Tue Mar 28, 2017 11:56 am

Re: Convert a new record form to an edit record form?

Post by Maax555 »

Villeroy, the above is an excellent explanation and is helping me greatly to understand better the relationship between main and subforms.
I am going to create a simple test database with the sole purpose of gaining a better understanding of subforms. Specifically for recalling a record from a Listbox and editing the record. I will hopefully then apply this to my main project, return here and mark another problem as solved.

many thanks.
Regards Maax
LibreOffice 6.1.4.2 on Windows 7
Maax555
Posts: 127
Joined: Tue Mar 28, 2017 11:56 am

Re: Convert a new record form to an edit record form?

Post by Maax555 »

So I have tried for several hours now on this problem. I really dont know what I am getting wrong or misunderstanding.
So created the MainNew form which works as expected. Copied to Main Edit which allows modifications using the record navigator.
Then tried to convert the PartNumber to a Listbox to choose the correct PartID, which it does. But as soon as I come out of the listbox to edit another field I get the duplicate PartID message. How do I get this form to update/modify the record without trying to save a new PartID?
Its nearly end of day for me which is pleasing as I dont understand how I can not get this simple (feels like its so very simple) procedure to work.?
:-(
Attachments
SubFormTest.odb
(29.2 KiB) Downloaded 189 times
Regards Maax
LibreOffice 6.1.4.2 on Windows 7
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Convert a new record form to an edit record form?

Post by Villeroy »

I added a unique index on the PartNumber, so it is enforced to be unique. You could also use that field as a "natural" primary key but the auto-ID does its job.
I removed the ResultAB field from table Main. You must not store any calculated values in tables. Calculated values are calculated in queries according to stored values but you never store calculation results.
I added a filter table.
I removed all auto-ID from the input forms. They are just meaningless auto-IDs. No reason to expose them to the user.
I added a query which is used as data source of the first and second form.
------------
The first form can look up a record by using the built-in form filter. With property "Filter Proposal" the text box turns into a list box when you turn on filter mode. Then you can pick (or type) a value and finish filter mode by clicking the first button on the temporary filter mode toolbar.
--------------
The second form makes use of the "power filtering" technique which requires an additional filter table. See countless forum topics on "power filtering" and the form properties.
It offers auto-complete through a combo box. If you enter a not existing PartNumber, the subform remains blank and you can enter a new record.
-------------
The third form takes a new record. I added a tall listbox with no field binding. In this listbox you can test if a PartNo already exists. This is ugly, I know
Attachments
SubFormTest3.odb
(31.85 KiB) Downloaded 188 times
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
Maax555
Posts: 127
Joined: Tue Mar 28, 2017 11:56 am

Re: Convert a new record form to an edit record form?

Post by Maax555 »

Hi, yes, on my actual project database the partnumber field is unique, I did not make it unique for this test.
The ResultAB filed, yes I was aware that storing calculated numbers should not be done as the numbers could change but the calculation would not update. I added this field for experimental results.
AutoID only there for test so i can see if they are changing in accordance with the partnumber listbox, will be removing all of these from finished database.
Filter tables I have used before so understand basics (i think) without necessarily knowing when its best to make use of them over other methods.
I removed my query to concentrate on the other issue. It was basically the same as the one you have included.

The first form using form filter, I have had little experience in using the built in form filter. If i used this way of filtering is it possible to have a button on the form to work as a filter and also for the filter navigator not to show? I would add a button "Search" and perhaps another "select" to close the filter?

I much prefer the second option as this is how i imagined this form to work but could not get it right. From past experience and help I believe i could replace the ok button with a refresh macro.

The third option i can still see a use for a modified version. If I added a customer listbox, the form could then list all partnumbers for that customer and then click on the part from the list to bring up record details. This could be used for displaying/editing existing partnumbers.

As usual I am very grateful for your help. I do make an effort to search for existing help and try apply to my situation, often spending several hours on what seems to be a very simple requirement. I am only working on this database in spare time where I do not have more important work commitments so sometime I may be stuck with an issue for many days. Its great to log in next day and see the excellent help offered. Thanks for all your patience.
Regards Maax
LibreOffice 6.1.4.2 on Windows 7
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Convert a new record form to an edit record form?

Post by Villeroy »

It does not matter if it is a button on a form, a toolbar button or something else. In the first place it has to work. And when you set the filter proposal property of a text control, then you get a listbox "for free" when you switch to form filter mode. In this mode the same form takes filter criteria. I use this form filter mode when my predefined "power filter" is not flexible enough. Form filter mode is a little bit difficult to understand with its extra tool window and toolbar but once you got the hang of it, you can define rather complex filters.

The OK button on my form is a refresh button and it belongs to the subform. See form navigator and "Action" property.
In this case I use a combo box bound to Filter.TXT. This way you can enter a filter criterion or a value for a new PartNumber. A combo box is just a text box where you can enter any text you want with an auto-complete functionality for already existing values. This way you filter the subform either by an existing value or by an unknown PartNumber. In the latter case the subform goes to the new record and inherits the unknown value as a new PartNumber.

Abstain from all macro programming. It never really works unless you are a very experienced programmer. But even then, you should get your forms working before you add macros.
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
Maax555
Posts: 127
Joined: Tue Mar 28, 2017 11:56 am

Re: Convert a new record form to an edit record form?

Post by Maax555 »

I mostly had working what I wanted but instead of saving at that point and creating a new release I kept going, attempted to add the next requirement and somehow managed to destroy what I had. Started again and have got close to the point where i was but struggling to work out how to have the category listbox display the category rather than a blank. I think I had a CatID linked somehow and this was also in the main table. But struggled to work this out.
When i have had this part working other parts have then not allowed me to edit the wires field and save.
How do i have the Category retain the chosen value from the list instead of going blank?
Attachments
SubFormTest3.1.odb
(15.27 KiB) Downloaded 186 times
Regards Maax
LibreOffice 6.1.4.2 on Windows 7
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Convert a new record form to an edit record form?

Post by UnklDonald418 »

The boundfield in a list box needs to be an integer, but you are using Multiplier which is Numeric type.
You can either modify the List content query to

Code: Select all

SELECT "Category", CAST( "Multiplier" AS INTEGER) FROM "Categories"
or change the data type of Multiplier to Integer.
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
Maax555
Posts: 127
Joined: Tue Mar 28, 2017 11:56 am

Re: Convert a new record form to an edit record form?

Post by Maax555 »

Ah yes, I previously had Multiplier as an integer. I then managed to screw stuff up and ended up deleting and re-creating the database.
Many thanks, A little disappointed I never worked that out for myself.
Regards Maax
LibreOffice 6.1.4.2 on Windows 7
Maax555
Posts: 127
Joined: Tue Mar 28, 2017 11:56 am

Re: Convert a new record form to an edit record form?

Post by Maax555 »

So this is working very well with the exception of the category listbox. If mt category table has two fields with identical values, then the listbox falls back to the first instance of this in the listbox dropdown. So if I have one category " Spiral Tape" with value of 2.2 and then another entry further down the records "PVC Sleeving" with the same 2.2 value, when i choose PVC Sleeving and update, the listbox shows Spiral Tape instead of PVC Sleeving. There must be some filtering or sorting going on somehow. I have checked one database with this issue in it against the working demo and can not see any differences. I will look deeper.
Attachments
category#.JPG
category#.JPG (26.37 KiB) Viewed 7062 times
Regards Maax
LibreOffice 6.1.4.2 on Windows 7
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Convert a new record form to an edit record form?

Post by UnklDonald418 »

If more than one Category has the same Multiplier value, then Multiplier should not be used as the boundfield. As you are finding, the boundfield value needs to be unique for the list box control to work properly.
Looking at your data it appears that the Multiplier can be determined from the Category, so maybe you should use CatID instead of Multiplier. as the boundfield. Of course that is what would be saved in your Main table. Your problem results from the normalization violation of storing the Multiplier value in both Main and Categories.
Then your query qCalculate_AxB would need to be modified to get the Multiplier value from Categories table, but simple join should accomplish that.
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
Maax555
Posts: 127
Joined: Tue Mar 28, 2017 11:56 am

Re: Convert a new record form to an edit record form?

Post by Maax555 »

UnklDonald418 wrote:If more than one Category has the same Multiplier value, then Multiplier should not be used as the boundfield. As you are finding, the boundfield value needs to be unique for the list box control to work properly.
. I did not know that, I am surprised I had not come across this issue previously.

The multiplier does not need to be in my main table as I have the CatID in there already. Its only there as i found that the easiest way for me to achieve the form. On the form the multiplier is only used to provide an estimate on the form. The estimate is not saved and the user will either choose to use the suggested estimate or add their own value in the Assigned labour field.

The mention of using join in your post is not familiar to me. I will check it out and also look to remove the multiplier from the main table.

thanks
Regards Maax
LibreOffice 6.1.4.2 on Windows 7
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Convert a new record form to an edit record form?

Post by Villeroy »

Create a table of multipliers

Code: Select all

DESCR RATE ID
minimal .1 0
low   .15 1
medium .24 2
high  .32  3
max .36  4
Now you are free to use duplicate rates, show the description or the rate or both in a list box while always using the ID value as a referrer.

HOWEVER: if the rates change over time any you still need to calculate with correct rates of previous time periods, then you need a time tracking table where you keep track of which rate was valid when. Otherwise you get wrong calculations for the past periouds whenever you update the multipliers. At this point you leave behind beginners level. This is development work. It is not about clicking together some "database document":
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
Maax555
Posts: 127
Joined: Tue Mar 28, 2017 11:56 am

Re: Convert a new record form to an edit record form?

Post by Maax555 »

Hi thanks for above. For this instance I am happy to have the estimate calculated from wires x multiplier (value determined from Category listbox).
Th estimate being calculated only in the form and not saved in the main form. So still looking at using similar query as original suggestion but removing the multiplier from the main table.
Checking for a way to calculate in a query two fields from different tables. As in Select ("Wires" FROM "PartNumber2") * ("Multiplier" FROM "Categories) AS "Estimate" FROM "PartNumber2"
I know the above is totally incorrect in the way its written but thats what I have in my head, now to check how to achieve that :-)
Regards Maax
LibreOffice 6.1.4.2 on Windows 7
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Convert a new record form to an edit record form?

Post by Villeroy »

SELECT "A"."anything", "B"."something", "A"."x" * "B"."y" AS "Result"
FROM "A" JOIN "B" ON "A"."BID" = "B"."ID"
This joins 2 tables A and B by their common fields A.BID and B.ID. From this join you can select any fields you need and do simple row-by-row calculations.

Different but equivalent syntax:
SELECT "A"."anything", "B"."something", "A"."x" * "B"."y" AS "Result"
FROM "A", "B"
WHERE "A"."BID" = "B"."ID"
You can get the second result in the Base query designer.
Just add the 2 tables, draw a line between A.BID and B.ID
Double-click on the fields you want to select.
Then I would switch to SQL view and add the multiplication between 2 fields because the edit box in the query designer is so small.
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
Maax555
Posts: 127
Joined: Tue Mar 28, 2017 11:56 am

Re: Convert a new record form to an edit record form?

Post by Maax555 »

My head is done in. This was working yesterday with the exception of the category listbox returning to the first instance of a duplicate value in Multiplier.
I forgot to save it as a new version 1.24 and have tried to fix the issue only yo have gone backwards and now have the listbox returning cabletie (Multiplier value 2) when i press the update button. I fixed this bu using the suggested method above, using CAST or changing to an integer for the bound Multiplier field. I pretty sure i tried both ways and only one of them would work for me. Now I am trying both and none are working. I am opening the working subformtest database and comparing each and every field etc. This is quite difficult as windows keep shutting (i often take a screen shot to compare). I will keep checking as I know it can only be something very simple as had it working yesterday :-). I will then try looking at your latest suggestion. I did formulate something quite like your second suggestion but obviously not close enough as did not work fully.
thanks again.
Regards Maax
LibreOffice 6.1.4.2 on Windows 7
Maax555
Posts: 127
Joined: Tue Mar 28, 2017 11:56 am

Re: Convert a new record form to an edit record form?

Post by Maax555 »

ok so i think i have this back to the previous point where i had the issue of the category returning back to cable tie in the EditPartNumberLabour form. I added the CAST function to the Category listbox but still its not working. Would someone please advise in which area i am going wrong. Is it in The form, listbox or the PartNumber2 table?
https://www.dropbox.com/s/c1pn0tjvcb6p9 ... 4.odb?dl=0
Regards Maax
LibreOffice 6.1.4.2 on Windows 7
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Convert a new record form to an edit record form?

Post by UnklDonald418 »

I am uploading something for you to try
Project Tracking Test V1.2401.odb
(12.63 KiB) Downloaded 188 times
It only contains a query "qCalculate_AxB01" and a form "EditPartNumberLabour01". Copy them into your existing database Project Tracking Test V1.24.odb
See if that does what you want.
Since "Multiplier" is determined by the Category selection, the "Multiplier" column/field in table "PartNumber2" is a normalization violation and will lead to problems.
Let me know if you have questions or issues.
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
Maax555
Posts: 127
Joined: Tue Mar 28, 2017 11:56 am

Re: Convert a new record form to an edit record form?

Post by Maax555 »

UnklDonald418 wrote:I am uploading something for you to try
Project Tracking Test V1.2401.odb
It only contains a query "qCalculate_AxB01" and a form "EditPartNumberLabour01". Copy them into your existing database Project Tracking Test V1.24.odb
See if that does what you want.
Since "Multiplier" is determined by the Category selection, the "Multiplier" column/field in table "PartNumber2" is a normalization violation and will lead to problems.
Let me know if you have questions or issues.
Yes this is working, its a little different to what was working previously. I will try remove the double pipes part from the listbox so i still only see the category name.
I will remove the multiplier field from PartNumber2 main table as i know this is not required.
When i use the Update details this updates and saves the record so think i can remove the save category button?
Regards Maax
LibreOffice 6.1.4.2 on Windows 7
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Convert a new record form to an edit record form?

Post by UnklDonald418 »

If you select a new Category in the list box, the Save Category button is enabled as is the Save icon on the Navigation Bar. One of the two would need to be selected to save the new selection.
I added the concatenate (pipes) to the display field of the list box as a debugging aid, so I wasn't constantly toggling back and forth to the "Catetgories" 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
Maax555
Posts: 127
Joined: Tue Mar 28, 2017 11:56 am

Re: Convert a new record form to an edit record form?

Post by Maax555 »

Many thanks for all the help on this one. I have removed both the save button and nav bar as the update button saves any changes i make.
Regards Maax
LibreOffice 6.1.4.2 on Windows 7
Post Reply