| View previous topic :: View next topic |
| Author |
Message |
alicemoon
General User
Joined: 23 Jul 2006
Posts: 37
Location: UK
|
 Posted: Sat Feb 14, 2009 4:07 am Post subject: error updating record |
 |
|
| I have a database with 6 tables all joined and use various forms and subforms to insert data. Everything works well but I recently tried to add a checkbox on my main form just to say that a job was complete. However when I tried to tick the checkbox I got the return "error updating the current record no values were modified" - I wondered if it was a problem with the checkbox so changed it to a date box but got the same return. This happens if I try to insert a value into the date box either in the form or straight into the table - I can update everything else - I cannot understand why this is happening - can anyone help - I am sure it must be something simple but I cannot see what it is - I run openoffice 3.01 on Ubuntu Intrepid. |
|
| Back to top |
|
 |
RPG
Super User
Joined: 24 Apr 2008
Posts: 2696
Location: Apeldoorn, Netherland
|
| Posted: Sun Feb 15, 2009 11:18 am Post subject: |
|
|
Hello
It is not clear to me what the check box must do for you. But I think it does an unconditional update of the form with this instruction
Name_of_your_form.updaterow.
I think it must not unconditional but only if it is needed.
I think this code can be a solution for you
| Code: | WITH Name_of_your_form
if .isnew then
.insertrow
elseif .ismodified then
.updaterow
end if
end with |
Explanation
with .... end with
This is a construction so you have to type only once the name of the form
The if ... then ... elseif ... end if
In the if then the program make a decision about the update.
with a new record it is insertrow.
with a modified record it is updaterow.
And if both not there is no action
I feel the need to tell this about the word form.
First the word form is be used for two different thing.
1) the write document what is a form
2) What you see in the form navigator. In the form navigator you can have more then one form.
If we have about forms I mean a form what you see in the form navigator.
Every form does have a result set. The resultset can be part of a real table or a query.
I have told about updaterow or insertrow and about forms.
Now you look it which form you get the error.
I hope it is clear and helps you.
Romke |
|
| Back to top |
|
|
alicemoon
General User
Joined: 23 Jul 2006
Posts: 37
Location: UK
|
| Posted: Sun Feb 15, 2009 12:37 pm Post subject: |
|
|
thanks for your reply romke
perhaps I was not clear enough - I'll try again
I have a database which uses 6 joined tables.
Everything works fine and everything has been updating correctly whether I enter data through my various forms or directly into the tables.
But - I added an extra field to my main table - initially this was a boolean field which I then inserted into a form as a checkbox. However I could not add any data into this field - in the rest of the forms fields I could add data but not in this one field. Thinking it was a problem with the form I tried adding data to it in the table but still got the error report. So I deleted the field and added a date field instead - I got the same error in both the table and the form when I tried to add data into the date field.
My database is till working fine - I can add new records in both my forms/linked sub-forms and tables BUT I cannot add any data to the new date field if I try to I just get an error.
Will your update macro solve this and can you explain why is it happening - I would like to try to understand as if in the future I need to amend the database and add new fields I could have a big problem! |
|
| Back to top |
|
|
RPG
Super User
Joined: 24 Apr 2008
Posts: 2696
Location: Apeldoorn, Netherland
|
| Posted: Sun Feb 15, 2009 2:26 pm Post subject: |
|
|
Hello
Seeking an error is always difficult. Special if you only see something. My first post is only based on this quote out of your first post.; | Quote: | | "error updating the current record no values were modified" |
That says you try to update a record and nothing is changed so why updating.
The code is for testing. I use the code on several places.
I did expect that you maybe use a macro or a button who did say that the record must be updated. Only when you say with a button or a macro, update this record, then maybe the code can help. The code cannot help for other problems.
Now the second post
First you say;You add columns to your table and my first thought was how is this handled. OOo-base could add columns to the HSQLDB table only with the command line. But I did a test and I could add columns without the commandline. I don't know when this is added to OOo-base. I believe it is added in version OOo 3.0
I did search on this forum and Sliderule give on 7 Nov 2008 an example how to do it with the command line, it was a special case. But I still believe that you could not insert columns in an existing table before version OOo 3.
And now: I can insert boolean and date fields in a existing table and add data in to those fields: boolean and date.
I have
OOo 3.0.1
More precise
OOo300m15(build9379)
Build 3.0.1.2
My OOo3.012 is a Novell
I work with openSUSE 10.3
And now is the question
1) Do you things wrong?
2) Is there an error in OOo3.01 and not in my version who is maybe a little higher.
A possible solution is doing it in the old way and use the command line
I give a link
http://www.oooforum.org/forum/viewtopic.phtml?t=76923&highlight=alter+table+add+column
What to do
If your database is important and you have the time wait to version 3.1.
If you need a solution try the commandline.
If you want learn SQL code try the command line
In the case that you want have a solution before version 3.1 tell in the next post which dbase engine you use. If you don't know then it is possible HSQLDB, look left down the window.
This is not a solution Maybe an other person can tell more.
But I hope this helps you.
Romke |
|
| Back to top |
|
|
alicemoon
General User
Joined: 23 Jul 2006
Posts: 37
Location: UK
|
| Posted: Tue Mar 03, 2009 8:20 am Post subject: |
|
|
I have been trying to solve this problem - I tried the suggested code but it threw up more errors -
It is very frustrating as my database works fine as it stands - I can update records, create queries using tables/views already created but if I add new fields to a table I cannot put data into the the new fields either through the table or through a form.
This means I cannot improve on my database in any way it is effectively frozen - and there are various fields I really need to add |
|
| Back to top |
|
|
RPG
Super User
Joined: 24 Apr 2008
Posts: 2696
Location: Apeldoorn, Netherland
|
| Posted: Tue Mar 03, 2009 9:49 am Post subject: |
|
|
hello
I will try to pinpoint your problem.
You have a good working database
you can insert new fields into the database
after inserting sucessfull a new field you cannot insert data into that field but you can insert data into the other field on the same row.
The problem is in the forms and in the real tables as you see in the table view.
From you second post
| Quote: | Thinking it was a problem with the form I tried adding data to it in the table but still got the error report. So I deleted the field and added a date field instead - I got the same error in both the table and the form when I tried to add data into the date field.[\quote]
You mean realy a table and not a grid control looking like a table in a form? |
The quote say to me you can not update a table. If you cannot update the table that is an other kind of problem then if you cannot update an grid control. A grid control can be based on a table but also on a query.
The answers on both questions can maybe other people help to give an answer.
I don't have an answer. But maybe helps this questions you. I do trust you, that you understand the different between a real table and a table control. A grid control and a table control are the same and are part of a form. A real table is part of a database, but not a view. You can see the difference between a view and a table in the table window
a table have one square.
a view have two squares.
Romke |
|
| Back to top |
|
|
alicemoon
General User
Joined: 23 Jul 2006
Posts: 37
Location: UK
|
| Posted: Tue Mar 03, 2009 10:20 am Post subject: |
|
|
| thankyou romke - yes that is it exactly - I do understand the difference between fields, controls, tables and forms (apologies if the language I have used is confusing) and I did initially think it was a problem with my form but then realised it was a problem with the underlying table- I hope someone can help me as it is very frustrating |
|
| Back to top |
|
|
RPG
Super User
Joined: 24 Apr 2008
Posts: 2696
Location: Apeldoorn, Netherland
|
| Posted: Sat Mar 07, 2009 5:26 am Post subject: |
|
|
Hello
I did think sometimes to this problem.
Do you have found a solution?
If not maybe you can send it up if the database is not great and the data is not secret.
Romke |
|
| Back to top |
|
|
alicemoon
General User
Joined: 23 Jul 2006
Posts: 37
Location: UK
|
| Posted: Sat Mar 07, 2009 7:52 am Post subject: |
|
|
Romke
thanks for thinking about the problem.
I have had some help from a forum member - I sent him my database and after looking at it he pointed out that the problem lay in the way I had named two fields in one of the tables. I had a similar name for both but one was capitalised.This caused a problem with the GUI as he could add records using the problem fields via SQL but not via the GUI.
Once I changed the field names the problem was resolved - I can now create new fields and update them in both table and form format.
So the problem lay with my table construction and the solution was to be more consistent and careful in naming fields |
|
| Back to top |
|
|
RPG
Super User
Joined: 24 Apr 2008
Posts: 2696
Location: Apeldoorn, Netherland
|
| Posted: Sat Mar 07, 2009 8:11 am Post subject: |
|
|
Hello
It is nice there is a solution. I did not think that can be a problem. So I can maybe think on that the next time. And for other people who read can it also be important
Romke |
|
| Back to top |
|
|
|
