Edit table field via form textbox (1 Viewer)

Michiel Soede

Registered User.
Local time
Today, 16:08
Joined
Sep 16, 2012
Messages
12
All,

I have a very simple question. In the attachment I have a form populated by a query on a table. Would I be able to directly edit the 'explanation' field by populating the bounded text box on the form.
Now it simply does not allow me to write in it but I don't know why.

Thanks in advance,

Michiel
 

Attachments

  • Periodic_Analysis.accdb
    800 KB · Views: 44

isladogs

MVP / VIP
Local time
Today, 16:08
Joined
Jan 14, 2017
Messages
18,186
Your query is read only so none of the fields can be edited.
The reason is that neither table has a primary key field.
You should ALWAYS assign a PK field to ALL access tables

The other issue is that your VZ Trial Balance Periods table has a spreadsheet design
i.e. lots of fields & few records.
In Access, tables should have few fields with lots of records - each record effectively holding one 'bit of data'
 

Michiel Soede

Registered User.
Local time
Today, 16:08
Joined
Sep 16, 2012
Messages
12
Thanks let me try adding the PK's tomorrow.
Would the PK be required to locate the exact record or something?
I suppose the spreadsheet format will not be a real issue when updating the cell?

Many thanks

Michiel
 

isladogs

MVP / VIP
Local time
Today, 16:08
Joined
Jan 14, 2017
Messages
18,186
Would the PK be required to locate the exact record or something?
Yes - exactly that

I suppose the spreadsheet format will not be a real issue when updating the cell?
Depends on how the PK fields are done.
Spreadsheets have cells. Databases have records.

Attached is one way of getting it to work with your current structure but whether that's OK for your purposes is another matter.
All I've done is add PK fields - I'll leave the rest to you
 

Attachments

  • Periodic_Analysis -CR.zip
    32.4 KB · Views: 62

Michiel Soede

Registered User.
Local time
Today, 16:08
Joined
Sep 16, 2012
Messages
12
Works indeed, for which many thanks. I only noticed that you assigned the primary key of the account per manager table to the account field. How does the primary key of this table interact with the primary key of the VZ trial balance table? (that's my ignorance with working with primary keys)
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 03:08
Joined
Jan 20, 2009
Messages
12,849
You should ALWAYS assign a PK field to ALL access tables

Mostly yes but not quite always. If records are simply being displayed and are not related to other tables, then there is no real need for a key. A primary key field requires a unique index which is a substantial overhead when records are being added to a table, so there is an advantage to omitting the primary key if not required.

Note however, if the table is held in an ODBC connected database, a primary key is required even under the limited circumstances above.

There are few absolutes in database design. One of them is that the pros and cons of various circumstances should be considered in every design.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:08
Joined
May 7, 2009
Messages
19,169
There is no pk in the trial balance. Pk should be unique and no duplicate. If by chance you need to add manager to an account your pk on acct per manager will not work since there wil be an act for two person. To fix this you need to create yiur pk on two fields, value + manager..

You can also make you query updatable by creating a Unique Index on Value field without making it as your Primary key.
 

Michiel Soede

Registered User.
Local time
Today, 16:08
Joined
Sep 16, 2012
Messages
12
You can also make you query updatable by creating a Unique Index on Value field without making it as your Primary key.

With value field you mean the explanation field I want to edit? How do you add a unqiue index on the value field then?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:08
Joined
May 7, 2009
Messages
19,169
Ridders had already done that for you.
Open act per manager table in design view.
On the ribbon, click Indexes.
Youll notice that Value field is set as Primary and Unique.
If yiu change Primary to No and leave Unique as Yes, youll still have an updatable query.
 

isladogs

MVP / VIP
Local time
Today, 16:08
Joined
Jan 14, 2017
Messages
18,186
Michiel
I gave you simple advice because I didn't want to overcomplicate matters.
Galaxiom correctly described very limited circumstances when a PK field is unnecessary. This setup isn't one of those

Arnelgp is correct that you can just assign a unique index to the value field in the Accounts per manager table and still have an updateable query.
However, it is almost certain that for other purposes you will still need a PK field at some point.
Therefore I still say assign a PK field to each table.

You can use:
a) an autonumber ID field
b) an existing field where this is unique (as Value is at the moment)
c) a composite PK field of two (or more) fields such as Value & Manager if the combination is unique

It is also worth indexing fields that aren't part of a PK where these will be regularly used in queries as:
- link fields
- filter criteria (WHERE clause)
- for sorting (ORDER BY clause)
as this will make your queries significantly faster.

Note there is an indexing option which allows for duplicates
 
Last edited:

Michiel Soede

Registered User.
Local time
Today, 16:08
Joined
Sep 16, 2012
Messages
12
Grateful for your input guys and hopefully the discussion is valuable for the forum.
 

isladogs

MVP / VIP
Local time
Today, 16:08
Joined
Jan 14, 2017
Messages
18,186
You're welcome

You still need to look at the structure of your 'spreadsheet' table
For example, replace fields Jan_Dec with 2 fields - MonthNo and another field to contain the values
Similarly rationalise fields such as
JanNextYear-> MarNextYear
Jan17YTD-Dec17YTD
Jan18YTD-Mar18YTD
All fields which are identical except for final number 1,2,3 etc

There may be others!
In some cases you will need to split the data into two or more records. That's fine
It will allow Access to search, sort & filter more effectively

Good luck with your project
 
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:08
Joined
Feb 19, 2002
Messages
42,970
Mostly yes but not quite always. If records are simply being displayed and are not related to other tables, then there is no real need for a key.
If you ever expect to update a table, it should have a primary key (autonumber/identity if no candidate key is available) or a unique index. However, if the table is some RDBMS other than Jet/ACE, the table MUST have a PK or unique index or you will not be able to update the table from Access.

According to relational design, best practice is to always use either a natural key or a surrogate key for every table.

Think about it this way. You will never have a problem if the table has a PK but you will in certain cases have a problem if the table does not have a PK. In all the years I have been developing databases (going back to the late 60's), the only tables without primary keys that I have built have been import/export holding tables that are never updated. They are always replaced in total.
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 03:08
Joined
Jan 20, 2009
Messages
12,849
If you ever expect to update a table, it should have a primary key (autonumber/identity if no candidate key is available) or a unique index. However, if the table is some RDBMS other than Jet/ACE, the table MUST have a PK or unique index or you will not be able to update the table from Access.

We agree on these points which I included in my previous post.

According to relational design, best practice is to always use either a natural key or a surrogate key for every table.
Unsubstantiated claims not without a logical reason don't really contribute to a debate.

Think about it this way. You will never have a problem if the table has a PK but you will in certain cases have a problem if the table does not have a PK.

Adding a PK contributes substantial overheads when inserting records and slow the process considerably. That in itself can be a problem.

In all the years I have been developing databases (going back to the late 60's), the only tables without primary keys that I have built have been import/export holding tables that are never updated. They are always replaced in total.
Thankyou for acknowledging that you don't have any experience inserting records into existing tables without Primary keys. I have done it both ways using the same data and found that it was much faster without the Primary Key. The task involved inserting about 30,000 rows per batch and while on Access, the table reached a total of over nine million records. There were never any problems retrieving and displaying the records in Access despite having no Primary Key.

I added a PK when I moved the data to MSSQL Server. The insert into the Access table was done on my busy little PC reading and writing over a Gigabit LAN. The insert into the SQL table uses a custom .NET application running on the database server. The Access insert over the LAN without the PK vastly outperformed the program running on the server with the PK.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:08
Joined
Feb 19, 2002
Messages
42,970
Last edited:

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 03:08
Joined
Jan 20, 2009
Messages
12,849
I don't think we do agree or I wouldn't have said anything.

These two points seemed quite similar. The need for a key if a record is to be updated is implicit in my point.

Pat Hartman said:
If you ever expect to update a table, it should have a primary key (autonumber/identity if no candidate key is available) or a unique index. However, if the table is some RDBMS other than Jet/ACE, the table MUST have a PK or unique index or you will not be able to update the table from Access.

Galaxiom said:
If records are simply being displayed and are not related to other tables, then there is no real need for a key.

Note however, if the table is held in an ODBC connected database, a primary key is required even under the limited circumstances above.

In fact my constraint goes further than yours. While you say that a PK is necessary if a record is to be updated, in fact, it will require a PK to be linked to Access.

Was it necessary to be snotty?
Backing an argument by pulling rank instead of objective reasoning results in an edifice that topples easily. People who are easily offended should avoid building such edifices.


From the same scholarly article:

Phil Factor said:
It is wrong to say that every table requires a primary key, but any relational table must, by definition have at least one candidate key.

You may, however, deliberately choose to use a SQL Server table in a non-relational way, and it is perfectly legitimate, though odd, to have a table without any key in SQL Server.
 
Last edited:

isladogs

MVP / VIP
Local time
Today, 16:08
Joined
Jan 14, 2017
Messages
18,186
At the risk of further escalating any disagreements, this thread long ago stopped being of any use to the OP.
I doubt it is of benefit to anyone else either at this point.

As a deliberately simplistic comment from me about always using a PK field was the trigger for a lot of this, please read my follow up comments in post #10
 
Last edited:

Michiel Soede

Registered User.
Local time
Today, 16:08
Joined
Sep 16, 2012
Messages
12
You still need to look at the structure of your 'spreadsheet' table
For example, replace fields Jan_Dec with 2 fields - MonthNo and another field to contain the values

I am definitely not a database expert but to my knowledge putting month data in columns/fields has the advantage that you use less memory in the table where you share the other fields of the specific values. Also driven by the fact that I am not sure how you would efficiently deduct two months from each other if they would be in the rows. I would then simply create two queries in Access (one for january and one for february for example) and deduct those two with a join on the other fields? Nothing to do with the original topic but this is some rationalization of why people put months next to each other I suppose.
 

isladogs

MVP / VIP
Local time
Today, 16:08
Joined
Jan 14, 2017
Messages
18,186
I am definitely not a database expert but to my knowledge putting month data in columns/fields has the advantage that you use less memory in the table where you share the other fields of the specific values. Also driven by the fact that I am not sure how you would efficiently deduct two months from each other if they would be in the rows. I would then simply create two queries in Access (one for january and one for february for example) and deduct those two with a join on the other fields? Nothing to do with the original topic but this is some rationalization of why people put months next to each other I suppose.

I'm not fully understanding what you are saying above

If you mean storing all data in one huge table uses less disk space then that MAY be true.
However the database definitely will not run as well as it could

For maximum efficiency in use, database table data should be normalised. What you have now is definitely nowhere near being normalised.
Suggest you do a Google or forum search for this topic - there are plenty of good explanations around

It isn't difficult to setup queries to get the difference between 2 records.
Suggest you have a look at Allen Browne's website or the Access videos by Steve Bishop available on You Tube.
 

Users who are viewing this thread

Top Bottom