[Solved] Error Inserting New Record When Refreshing Subform
-
- Posts: 4
- Joined: Sun Jan 21, 2018 1:10 am
[Solved] Error Inserting New Record When Refreshing Subform
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:
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:
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
Current Status:
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:
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
-
- Volunteer
- Posts: 1549
- Joined: Wed Jun 24, 2015 12:56 am
- Location: Colorado, USA
Re: "Error Inserting New Record" When Refreshing Subform
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.
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
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
-
- Posts: 4
- Joined: Sun Jan 21, 2018 1:10 am
Re: "Error Inserting New Record" When Refreshing Subform
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
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
Re: [Solved] "Error Inserting New Record" When Refreshing ub
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)
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
-
- Posts: 4
- Joined: Sun Jan 21, 2018 1:10 am
Re: [Reopen] "Error Inserting New Record" When Refreshing ub
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
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 153 times
OpenOffice 4.1.5 on Windows 10
Re: [Reopen] "Error Inserting New Record" When Refreshing ub
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.
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: [Reopen] "Error Inserting New Record" When Refreshing ub
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.
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.
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
-
- Posts: 4
- Joined: Sun Jan 21, 2018 1:10 am
Re: [Reopen] "Error Inserting New Record" When Refreshing ub
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.
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.)
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
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.
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.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?
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.)
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.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'
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
Re: "Error Inserting New Record" When Refreshing Subform
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".
........................................................
eg: menu:tools>SQL
after execution: from the base main window hit Tables icon then menu:View>Refresh Tables.
if you have any further questions not related to Cascading List Boxes then please ask them in a new thread.
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".
........................................................
the answer is no but as it's one field & fight related it should be added to the table 'tbl_fights'.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?
eg: menu:tools>SQL
Code: Select all
alter table "tbl_fights" add "winner_id" integer before "wieght_class_id";
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'. |
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