[Solved] Unable to enter Data

[Itsmemike]

[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

[mgroenescheij]

Have you assigned one field as Primary Key in your tables?
If that doesn't solve the issue, can you upload the .odb file?

[Itsmemike]

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.

[mgroenescheij]

the .odb file is a base document. .odb is the file extension.

[Itsmemike]

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.

[mgroenescheij]

I asked if you can upload the .odb file

[Villeroy]

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.

[Itsmemike]

Sorry i,m new to this I don,t know what you mean by "upload the .odb file

[Villeroy]

New to the internet? Well, we have a tutorial for that: viewtopic.php?f=74&t=8289

And this is where you can download plenty of odb files which had been uploaded by others: viewforum.php?f=100

[Itsmemike]

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.

[Villeroy]

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.

[Itsmemike]

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.

[Villeroy]

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).

[Itsmemike]

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

[F3K Total]

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

[Itsmemike]

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.

[F3K Total]

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.

[Itsmemike]

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

[Itsmemike]

Please ignore the previous post I have solved the problem

[Itsmemike]

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.

[F3K Total]

Hello,

There's more than one way to skin a cat.

The main problem was:

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

[LilZebra]

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