[Solved] Unable to enter Data

Creating and using forms

[Solved] Unable to enter Data

Postby Itsmemike » Fri Nov 06, 2015 6:08 am

[SOLVED]

Hi everyone, I am new to Oo after using Access for many years. I have had many problems but the one I can,t solve is this;

I have created my table and form and everything seemed OK, however I now find a few fields will not allow me to enter data. I have checked the table and form settings and can see nothing wrong like read only. I have tried different settings but makes no difference (one field is Text, another is Number like 44.65).

I have run out of ideas can anyone give me some suggestions.

Regards
Mike
Last edited by Itsmemike on Sat Nov 07, 2015 4:24 am, edited 3 times in total.
OpenOffice 4.1.2
Itsmemike
 
Posts: 16
Joined: Fri Nov 06, 2015 5:42 am

Re: Unable to enter Data

Postby mgroenescheij » Fri Nov 06, 2015 8:13 am

Have you assigned one field as Primary Key in your tables?
If that doesn't solve the issue, can you upload the .odb file?
AOO 4.1.5 on MS Windows 10 Professional & MacOS High Sierra 10.13.5
Please add '[Solved]' at beginning of your first post title (edit button) if your issue has been fixed.
mgroenescheij
Volunteer
 
Posts: 300
Joined: Thu Apr 23, 2009 10:19 pm
Location: Sydney Australia

Re: Unable to enter Data

Postby Itsmemike » Fri Nov 06, 2015 9:12 am

Yes i have set a PK, not sure what you mean by .odb file?
Since posting my first question I have closed and re-opened the Db. Now I can not change any data in any of the fields and can only move from one record to another, can not open a new record.
OpenOffice 4.1.2
Itsmemike
 
Posts: 16
Joined: Fri Nov 06, 2015 5:42 am

Re: Unable to enter Data

Postby mgroenescheij » Fri Nov 06, 2015 9:45 am

the .odb file is a base document. .odb is the file extension.
AOO 4.1.5 on MS Windows 10 Professional & MacOS High Sierra 10.13.5
Please add '[Solved]' at beginning of your first post title (edit button) if your issue has been fixed.
mgroenescheij
Volunteer
 
Posts: 300
Joined: Thu Apr 23, 2009 10:19 pm
Location: Sydney Australia

Re: Unable to enter Data

Postby Itsmemike » Fri Nov 06, 2015 11:17 am

Yes I have closed and re-opened the Db now all fields are locked. Can not enter or change any data or open a new record.
OpenOffice 4.1.2
Itsmemike
 
Posts: 16
Joined: Fri Nov 06, 2015 5:42 am

Re: Unable to enter Data

Postby mgroenescheij » Fri Nov 06, 2015 12:40 pm

I asked if you can upload the .odb file
AOO 4.1.5 on MS Windows 10 Professional & MacOS High Sierra 10.13.5
Please add '[Solved]' at beginning of your first post title (edit button) if your issue has been fixed.
mgroenescheij
Volunteer
 
Posts: 300
Joined: Thu Apr 23, 2009 10:19 pm
Location: Sydney Australia

Re: Unable to enter Data

Postby Villeroy » Fri Nov 06, 2015 12:57 pm

Hi everyone, I am new to Oo after using Access for many years.

Stick with any old version of MS Access. If you want to use this office suite in combination with your MS Access database, connect a Base document to your Access database and pull in some meaningful queries for mail merge, label printing etc. The clou of a database is that you do not need to convert things over and over again as long as you have a working database driver to use the same database with a wide combination of third party products.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28559
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Unable to enter Data

Postby Itsmemike » Fri Nov 06, 2015 1:00 pm

Sorry i,m new to this I don,t know what you mean by "upload the .odb file
OpenOffice 4.1.2
Itsmemike
 
Posts: 16
Joined: Fri Nov 06, 2015 5:42 am

Re: Unable to enter Data

Postby Villeroy » Fri Nov 06, 2015 1:13 pm

New to the internet? Well, we have a tutorial for that: https://forum.openoffice.org/en/forum/v ... =74&t=8289

And this is where you can download plenty of odb files which had been uploaded by others: https://forum.openoffice.org/en/forum/v ... .php?f=100
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28559
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Unable to enter Data

Postby Itsmemike » Fri Nov 06, 2015 1:30 pm

Please remember I have only been using OpenOffice for a few days so you might see many mistakes. Most of the problems I had I was able to resolve them, now everything has locked up so can not proceed. One other problem I have unsolved is in the Supplier table I have SupplierID which is the PK, I forgot to set the AutoValue to yes but It will not allow me to change it.

This is only a practice Db which I,m doing to learn Oo so please be kind to me.
Attachments
Pratice DB_2.odb
(52.11 KiB) Downloaded 141 times
OpenOffice 4.1.2
Itsmemike
 
Posts: 16
Joined: Fri Nov 06, 2015 5:42 am

Re: Unable to enter Data

Postby Villeroy » Fri Nov 06, 2015 2:39 pm

I can edit your tables and forms without problem. If you are running a Mac (which you should have told us) then you better go back to AOO 4.1.1 or http://libreoffice.org
AOO 4.1.2 for the Mac does not work with databases. Due to a lack of Mac testers nobody noticed this problem.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28559
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Unable to enter Data

Postby Itsmemike » Fri Nov 06, 2015 2:46 pm

No i am not using a Mac.
Would appreciate any info on any edits you make and comments on what I did wrong. Very confused, everything was going well until I started to use Combo boxes.

Thanks for your help.
OpenOffice 4.1.2
Itsmemike
 
Posts: 16
Joined: Fri Nov 06, 2015 5:42 am

Re: Unable to enter Data

Postby Villeroy » Fri Nov 06, 2015 3:58 pm

Since most of your fields are optional (nullable), I can enter anything anywhere except for POrder.SupplierID that does not exist in the suppliers table (and except for wrong data types of couse).
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28559
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Unable to enter Data

Postby Itsmemike » Fri Nov 06, 2015 4:12 pm

Thanks for your reply, so if you can enter data any suggestion why i can,t. Have a feeling I,m going to have to do it all over again. I know I have a problem with SupplierID but can not find a solution. When i try to change the setting to AutoValue I get the following:

The column "SupplierID" could not be changed. Should the column instead be deleted and the new format appended?

SQL Status: S1000

Error while saving the table design

SQL Status: 23000
Error code: -197

Column is referenced in constraint or view: SYS_REF_SYS_FK_62_66 in statement [ALTER TABLE "SupplierDetails" DROP "SupplierID"]

I deleted the row and re-entered setting it to AutuValue but it made no difference. Any comments or suggestions would be appreciated.
Would you upload it so I can see if it works for me
OpenOffice 4.1.2
Itsmemike
 
Posts: 16
Joined: Fri Nov 06, 2015 5:42 am

Re: Unable to enter Data

Postby F3K Total » Fri Nov 06, 2015 5:52 pm

Hello,
if we are talking about the form SupplierPOF, then:
  • Forms datasource is query SupplierPOQ, which is not writeable, because of the used subqueries without primary key.
  • That's also the reason for not beeing able to modify the datas in the form
Solution:
  • Remove the subqueries from SupplierPOQ
  • add a subform, datasource XRateTotalQ, and link it via PoID
  • Drag the controls
    • SubTotal2
    • XRateTotal
    onto the subform
See attached your modified file
R
Attachments
Pratice DB_3.odb
(52.07 KiB) Downloaded 137 times
    MMove 1.0.6
  • Extension for easy, exact positioning of shapes, pictures, controls, frames ...
    my current system
  • Windows 10 AOO, LOLinux Mint AOO, LO
F3K Total
Volunteer
 
Posts: 977
Joined: Fri Dec 16, 2011 8:20 pm

Re: Unable to enter Data

Postby Itsmemike » Fri Nov 06, 2015 6:16 pm

Many thanks you have saved me a lot of work. I had worked out that it was something to do with the Query but did not know the solution. I still have 2 problems if you can help me.
1/ I still can not change the setting in the SupplierDetail Table, SupplierID to AutoValue.
2/ after creating a new record and closing the form it does not save the new record.

Your help again would be appreciated.
OpenOffice 4.1.2
Itsmemike
 
Posts: 16
Joined: Fri Nov 06, 2015 5:42 am

Re: Unable to enter Data

Postby F3K Total » Fri Nov 06, 2015 6:48 pm

Hi
  • delete the relation (Tools/Relationships), modify to autovalue, create the relation
  • I've modified everything, to make it simple: Datasource is the query only based on one table PurchaseOrder, including the calculations.
Attachments
Pratice DB_4.odb
(49.78 KiB) Downloaded 137 times
    MMove 1.0.6
  • Extension for easy, exact positioning of shapes, pictures, controls, frames ...
    my current system
  • Windows 10 AOO, LOLinux Mint AOO, LO
F3K Total
Volunteer
 
Posts: 977
Joined: Fri Dec 16, 2011 8:20 pm

Re: Unable to enter Data

Postby Itsmemike » Fri Nov 06, 2015 7:52 pm

Thanks again. I can understand how you have set up the query which is the way I was thinking it should be. I was following a tutorial I downloaded but of course it different when you do your own. Just one more thing (well for now anyway) can you check the calculation for SubTotal2. When I enter a shipping cost it gives the wrong total. Its only adding the shipping cost instead of multiplying it by the order qty.

Again I have looked but can not see the problem
OpenOffice 4.1.2
Itsmemike
 
Posts: 16
Joined: Fri Nov 06, 2015 5:42 am

Re: Unable to enter Data

Postby Itsmemike » Fri Nov 06, 2015 8:16 pm

Please ignore the previous post I have solved the problem
OpenOffice 4.1.2
Itsmemike
 
Posts: 16
Joined: Fri Nov 06, 2015 5:42 am

Re: Unable to enter Data

Postby Itsmemike » Sat Nov 07, 2015 4:19 am

F3K Total Thanks for your help everything working OK now. Just one question, was there a reason you used a Subform or was the way I first had my form wrong.

Thanks also to the others who replied and helped me, much appreciated. Now to move on to the next stage, I,m sure i will have many more problems and questions.
OpenOffice 4.1.2
Itsmemike
 
Posts: 16
Joined: Fri Nov 06, 2015 5:42 am

Re: [SOLVED]Unable to enter Data

Postby F3K Total » Sat Nov 07, 2015 1:35 pm

Hello,
There's more than one way to skin a cat.
:D
The main problem was:
F3K Total wrote:Forms datasource is query SupplierPOQ, which is not writeable, because of the used subqueries without primary key.

If you mix different tables to one writable query, you need exactly to know, what happens there. E.g. if you have a mix of Suppliers and Orders in one query, and you mistakenly modify the SupplierID witch belongs to the supplier table, although you wanted to enter a SupplierID into the Order table, you'll get a mess.
The risk of mistakes raises.
So i separateded by using a structural subform, the Supplier-Form from the Order-Form, the only link is the SupplierID. It is served by the Mainform (Supplier-Form), so if you create a new row in the Order form, the SupplierID of the currently selected supplier will be written into the column SupplierID from table PurchaseOrder
R
    MMove 1.0.6
  • Extension for easy, exact positioning of shapes, pictures, controls, frames ...
    my current system
  • Windows 10 AOO, LOLinux Mint AOO, LO
F3K Total
Volunteer
 
Posts: 977
Joined: Fri Dec 16, 2011 8:20 pm

Re: [Solved] Unable to enter Data

Postby LilZebra » Wed Aug 24, 2016 1:18 am

Itsmemike, I found that if you use the import wizzard to import an existing file as a Spreadsheet then you cannot edit that data afterward once its an .odb (Base) file.

What I had to do and that I learned from the excellent tutorials done by 'The Frugal Computer Guy' on YouTube is to use drag n drop _from_ your Excel file onto the Base Table window.

LibreOffice/OpenOffice Base (parts 1 to 94)
https://www.youtube.com/watch?v=jWU0WdM ... 4CYHMr4wmV
LibreOffice 6.1.3.2 (Linux Fedora 29 on x86_64) with 'Notebookbar'
OpenOffice.org since 2002-12. LibreOffice user since 2013-current.
HSQLDB 2.5.0
User avatar
LilZebra
 
Posts: 43
Joined: Sun Mar 06, 2016 4:27 pm
Location: Canada


Return to Forms

Who is online

Users browsing this forum: No registered users and 2 guests