[Solved] Error Inserting New Record When Refreshing Subform

Discuss the database features
Post Reply
ZombieDuckWalk
Posts: 4
Joined: Sun Jan 21, 2018 1:10 am

[Solved] Error Inserting New Record When Refreshing Subform

Post by ZombieDuckWalk »

I am attempting to create a form of cascading list boxes. After many days of searching and scouring these forums I have had some success, but due to user error I have lost progress.

Current Status:

FightPicksFormNav.jpg
I have a main form with two list boxes controlled by queries, one text field that displays the Primary Key for the filter table, and a button to save the corresponding keys into the filter table.

The sub-form contains another list box, also controlled by a query. This field will successfully display filtered results that correspond to the selection in the 'Event' list box. This happens once the 'Refresh' button, also in the sub-form is pressed.

The Problem:
ErrorCode.jpg
When there is no information in the table 'tbl_junct_picker_event' the form reacts exactly as I would like. Although, once a record has been written, and I attempt to refresh the sub-form, I am presented with this error message.

My current design revolves around reusing the Primary Key '0' in the 'tbl_junct_picker_event', but for some reason I am not able to get the form to update that specific record. Instead it tries to insert a new record with the Primary Key '0', which is unsuccessful for obvious reasons.

Any suggestions or questions will be greatly appreciated.

-Justin
Last edited by ZombieDuckWalk on Tue Jan 30, 2018 1:48 am, edited 5 times in total.
OpenOffice 4.1.5 on Windows 10
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: "Error Inserting New Record" When Refreshing Subform

Post by UnklDonald418 »

Without a sample database to see first hand what is happening I doubt anyone here would be able to provide much specific help.

Using an integer as the primary key in a filter table can result it problems. I have have had good success using a Boolean as the Primary Key for a filter 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
ZombieDuckWalk
Posts: 4
Joined: Sun Jan 21, 2018 1:10 am

Re: "Error Inserting New Record" When Refreshing Subform

Post by ZombieDuckWalk »

Thank you for your response.

I saved my file and shut down base with the intent of uploading my database to be reviewed. After reopening my database everything seems to be working correctly.

I am currently attempting to add another filter table into the same form, if any more problems arise I will continue this thread. For now I am marking as solved.

- Justin
OpenOffice 4.1.5 on Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] "Error Inserting New Record" When Refreshing ub

Post by Villeroy »

Filter Form Receipe
Source: SELECT * FROM "FilterTable" WHERE "PrimaryKey" = 0 (select one distinct record only)
Allow modification: YES (we want to edit the distinct record)
Allow insertion: NO (otherwise the user can tab into a new record below the distinct record which might have happened to you)
Allow deletion: NO (we want to keep the distinct record)
Navigation toolbar: NO (we only have one distinct 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
ZombieDuckWalk
Posts: 4
Joined: Sun Jan 21, 2018 1:10 am

Re: [Reopen] "Error Inserting New Record" When Refreshing ub

Post by ZombieDuckWalk »

After closing and reopening OpenOffice I was able to get the second list box to populate and save correctly. Shortly after I was successful in obtaining that same result for a third form and list box. After stepping away from it for a couple of days I opened base again and the issue is now present in the third form. I don't recall changing anything, and believe that a working version was saved.

I proceeded to inspect all relevant tables, forms, and queries to find anything that was out of place. Even with a bit of tweaking and testing I was not able to figure out where my error is.

Attached is a copy of my database as I left it when I believed everything to be working properly.

The form in question is named 'frm_fight_fighter_picks'

There are still quite a few extra tables and forms hanging around after much trial and error.

I don't know what information I can provide you that the file itself cannot. If you have any questions please ask.

- Justin
Attachments
PicksDatabase3_PickingWinners.odb
(110.24 KiB) Downloaded 146 times
OpenOffice 4.1.5 on Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Reopen] "Error Inserting New Record" When Refreshing ub

Post by Villeroy »

Open frm_fight_fighter_picks
Focus the first control and hit the tab key 2 times. You are in a new record now.
Hit the update button.
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
chrisb
Posts: 298
Joined: Mon Jun 07, 2010 4:16 pm

Re: [Reopen] "Error Inserting New Record" When Refreshing ub

Post by chrisb »

ZombieDuckWalk,
considering that this thread is your first on the forum you have done well. it takes a good deal of effort & determination to get where you are.
i spent a couple of hours looking at your database trying to figure out what it is that you are trying to achieve.
it would have been a big help if you had clearly indicated the purpose of your objective.
it seems you have a boxing/wrestling database, organised events, combatants one on one & a small number of what you call pickers.
one or more of the pickers pick one combatant from one or more of the bouts.
your question appears to be: how do i limit the choice of fighters from which the pickers have to choose to the two combatants who participate in each bout?

it's my opinion that cascading list boxes are not your saviour, they are not user friendly nor a practical solution here. of course opinions do differ.
i can offer two propositions.
the attachment contains two forms which are almost identical.
each form has a filter(mainform) which filters the data by event.
each form has a sub form where bouts(fights) are made.
each form has a sub sub form where the pickers & their selections are chosen. these sub sub forms differ because selections are not made in the same way.
i made the forms with open office because that is what ZombieDuckWalk uses.
all tables with the prefix 'tbl' are the users originals. most of the queries are also originals although i may have slightly doctored one or two.
all tables/queries contained in the attachment are required.

fFights1:
a simple form without macros.
here the user will choose either 'Fighter 1' or 'Fighter 2' from a list box.
the query 'qPickers_&_Picks1_SQL' will show all & only the picks which were made when using this form.
the tables 'tPickList1' & 'tPicks1' are used exclusively by this form.

fFights2:
the table 'tPicks2' is exclusive to 'fFights2' & is used to store the picks.
SQL SELECT statements select data from stored or literal values & do not allow for the use of variables.
this form uses a small macro to obtain & assign to variables the ID values of the currently selected Fighter 1 & Fighter 2 in the Fighters form.
these variables are inserted into a select statement which is then used as the data source for the listbox 'Fighter' contained in the picker form.
the macro is fired by the 'After record change' event in the sub form 'sFights'

often when forms which contain more than one subform are first loaded into openoffice/libreoffice base those columns where a list box is used may show the underlying table values.
immediately after loading the form hit the refresh button after which things should function as expected.

with libreoffice & 'fFights2' when the user changes event & hits refresh the 'Picks/Fighter' list box fails to update even though the macro fires twice.
with libreoffice it's one step forward then one step back. i do not have the time or patience to investigate right now.
Pick 27-Jan-2018.odb
(28.5 KiB) Downloaded 117 times
open office 4.1.14 & LibreOffice 6.4.4.2 x64 using HSQL 1.8.0.10 (Embedded) and HSQL 2.6.0 (Split) on Windows 10
ZombieDuckWalk
Posts: 4
Joined: Sun Jan 21, 2018 1:10 am

Re: [Reopen] "Error Inserting New Record" When Refreshing ub

Post by ZombieDuckWalk »

Chris,

Thank you very much. This looks to be insanely helpful, although it will take me some time to understand well enough to incorporate some further ideas.
chrisb wrote:ZombieDuckWalk,
considering that this thread is your first on the forum you have done well. it takes a good deal of effort & determination to get where you are.
i spent a couple of hours looking at your database trying to figure out what it is that you are trying to achieve.
it would have been a big help if you had clearly indicated the purpose of your objective.
it seems you have a boxing/wrestling database, organised events, combatants one on one & a small number of what you call pickers.
one or more of the pickers pick one combatant from one or more of the bouts.
your question appears to be: how do i limit the choice of fighters from which the pickers have to choose to the two combatants who participate in each bout?
Thank you, I have spent many hours reading, watching, searching, and trying to get this far. I appreciate the time that you put in to help me with this. Following is my objective as it currently stands.

GOAL
: Document MMA picks from a select source of knowledgeable people; as well as the actual results from the fight. This will allow me to statistically analyze the accuracy of the 'Pickers' based on criteria of my choosing (weight class, sex, location, etc.)

chrisb wrote: fFights2:
the table 'tPicks2' is exclusive to 'fFights2' & is used to store the picks.
SQL SELECT statements select data from stored or literal values & do not allow for the use of variables.
this form uses a small macro to obtain & assign to variables the ID values of the currently selected Fighter 1 & Fighter 2 in the Fighters form.
these variables are inserted into a select statement which is then used as the data source for the listbox 'Fighter' contained in the picker form.
the macro is fired by the 'After record change' event in the sub form 'sFights'
Again, thank you a ton for all of your help. I am going to further investigate Fights2 to see if I can deconstruct how everything is working. This method of entering picks will be much quicker and easier than my original plans of cascading list boxes.

I am going to attempt to re-engineer this to input actual fight winners into a separate table. Do you see any inherent flaws in this working?

- Justin
OpenOffice 4.1.5 on Windows 10
chrisb
Posts: 298
Joined: Mon Jun 07, 2010 4:16 pm

Re: "Error Inserting New Record" When Refreshing Subform

Post by chrisb »

ZombieDuckWalk,
this thread is aimed at an issue you encountered when using cascading list boxes.
below i have tried to answer that question:-

your efforts were always doomed to failure due to form design.

your form contains three data forms two of which have single row filter tables as their data source, the third has a multi row table as its data source.
the cause of conflict are the fields which link the single row filter tables.

first single row filter table:
mainform: data source = tbl_junct_picker_event

second single row filter table:
subform: data source = 'tbl_junct_picker_event_fight'
linked fields "event_id" & "picker_id".

1. in the mainform we input 0 into "event_id" & 0 into "picker_id".
we refresh the subform.
the values that were input are saved to the mainforms data source.
2. we now input 0 (fight_id) into the subform.
we refresh the sub-subform.
the value of both linked fields & the value of 'fight_id'(0) are saved to the subforms data source.

we can now repeat step 2 choosing any 'fight_id' any number of times & the data is saved as expected.

3. if we now in step 1 change "event_id" or "picker_id" or both & refresh the subform it greys out & freezes up.

Why:
because the linked fields filter the data & the subform is restricted to one row(Allow additions = No).
once that subform single row contains data we have effectively limited any future input to the subform to those values already stored in the subforms linked fields of "event_id" & "picker_id".
........................................................
ZombieDuckWalk said:
I am going to attempt to re-engineer this to input actual fight winners into a separate table. Do you see any inherent flaws in this working?
the answer is no but as it's one field & fight related it should be added to the table 'tbl_fights'.
eg: menu:tools>SQL

Code: Select all

alter table "tbl_fights" add "winner_id" integer before "wieght_class_id";
after execution: from the base main window hit Tables icon then menu:View>Refresh Tables.
 Edit: 30 Jan 2018 12:30
to be clear
the answer is no. you can input actual fight winners into a separate table using the method contained in the form 'fFights2'.
BUT as it's one field with one entry per record & fight related it's best to add a field to the table 'tbl_fights' & select the winner by using the method contained in the form 'fFights1'. 
if you have any further questions not related to Cascading List Boxes then please ask them in a new thread.
open office 4.1.14 & LibreOffice 6.4.4.2 x64 using HSQL 1.8.0.10 (Embedded) and HSQL 2.6.0 (Split) on Windows 10
Post Reply