Access Solutions Archive

In this article, we have addressed some common reasons that at times, prevent you from editing Data in an Access Query

A query is a Microsoft database component which allows users to retrieve and revise their data anytime. The sole purpose of Queries is to generate outputs and make the necessary updates in the database. Queries can help in locating data from a record, table, or form, in a database. But it is necessary to use these queries in the right order and structure; else they might not retrieve any data at all. Well, in this case, we are going to talk about situations in which users are unable to edit their data while using queries.Reasons Why You Can Not Edit Data In An Access Query

Every now and then, we all face a problem where we are unable to edit our data using the Access Queries. Here are some of the reasons which cause the following situation. This list might also help you in identifying the root cause of the problem, which prevents you from updating your database.

  1. Query Consists Of A GROUP BY ClauseYour query consists of a GROUP BY Clause. Note that a Totals query is set by default for read-only.
  2. Your query consists of a TRANSFORM clause. Note that a Crosstab query is also set for read-only by default.
  3. Your query is using SELECT clause with either of the following First(), Max(), Sum(), Count(), etc. Queries which can aggregate records are also always read-only.
  4. Your query might be containing a DISTINCT predicate. Users can solve this issue by setting the Unique Values in the query’s Properties to No.
  5. Your Query involves or is operating through a UNION. Note that Union queries are designed for read-only by default.
  6. Your query’s SELECT clause contains a subquery. For updating your data users can go to their subquery and Uncheck its Show box or they can also try using domain aggregation functions.
  7. Your Query might be using JOINs from different directions from several tables while running on a FROM clause. Just remove some tables and you will be able to update your data easily.
  8. You have not indexed the JOIN fields correctly: These fields do not consist of any unique index or primary key.
  9. Your queries are set as Snapshot in its Recordset Type property. In order to solve this change it to “Dynaset” from Set Recordset Type in your query’s Properties.
  10. Your query might be based on a different query that is set at read-only by default. (Stacked query)
  11. You have set your queries permissions to read-only. (This can be changed by changing the setting on the Access security.)
  12. User has opened the Database with read-only, or its file attributes are set at read-only, or maybe the database’s media type is on read-only. (E.g. CD-ROM or any network drive which doesn’t offer writing privileges.)
  13. The query requests for a VBA function. However, the user’s database is not located in a trusted storage location which disables the code from running.
  14. Maybe the fields which the query is supposed to generate as outputs are calculated fields, which prevent the data from being touched in MS Access.
  15. In rare cases you may be looking at an incident of corrupted Access file cause the issue.

Author Introduction:

Vivian Stevens is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including mdf recovery and excel recovery software products. For more information visit www.datanumen.com

Be the first to comment

How to Mark a Record as Printed in MS Access

Posted January 22, 2018 By AuthorVS2

In this article, users will learn how to mark a record as printed in MS Access.

Handling tons of data can be a big challenge when using MS Access. Similarly to remember which records were printed and which were not printed can often get confusing. In order to keep a track of above-mentioned issue, Microsoft has created a sign which allows users in identifying the printed records from the un-printed ones. But this is not the only issue. It is also difficult to tell previewing from printing. As while a user commands to print a file it is previewed on the screen and then it is printed. But printer being externally operated machine can face paper jams, run out of toner or ink or sometimes it can also be turned off by anyone before the job is completed.Learn To Mark A Record As Printed In MS Access

So it is better for the user if he/she marks the record as ‘print run’ before he/she makes the print command. In this way, the user will be able to resend the batch in case of any interruption in the printing. If the user has a record time and date of when these files were printed, then they can reprint them according to their requirement.

So, users should use Number fields instead of using yes/no fields for indicating whether a record has been printed or not. These Number Fields can be stored according to their batch number. This number will remain blank until the record is not printed. Then it will contain the numeric value of the batch which was printed. In case of interruption, the user can send the print command again.

Assign number first, and then run print Command for each batch

MS AccessThe database comprises a table where the user enters new members (tblMember), plus another table which is responsible for storing and tracking all the print runs (tblBatch.) When a user enters a new member in the table, he/she leaves the BatchID blank.

When the user is ready for printing the records of the new members, he/she can open them from frmBatch. Then they need to select button at the top called ‘Create New Batch’. It will automatically create a new entry in the tblBatch. Then it will assign the batch number of this new entry with all the members which are yet to be printed (by entering the code, BatchID is null). Now the user needs to click on the Print Selected Batch for printing these records. Now the system will print the batch, and also filter your record.

Using this method user will be able to know if and when their records were printed. In case of an interruption in the procedure, the user will be able to resend the batch without any hassle. Using this method user can even undo and edit their batches if necessary.

For Tracking the Time when each record was printed

Since a user can print one record multiple times, he/she will need another table for storing that information.

  • BatchID – comprises of tblBatch.BatchID data
  • MemberID – comprises of tblMember.MemberID data

So if batch 3 contains 11 members, the user can add 11 records to the table then he/she can print the matching records using a query which can filter only one batch.

This will help the user in storing the entire history of every time a record is or was printed.

While using MS Access always make it a point to immediately initiate a database recovery the moment you notice any Access damage. The first signs of these come through data errors or an exceedingly slow performing database. Turn in to a professional tool like DataNumen Access Repair to handle such scenarios with aplomb.

Author Introduction:

Vivian Stevens is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including mdf repair and excel recovery software products. For more information visit www.datanumen.com

Be the first to comment

How to Work with Label Margins in MS Access

Posted January 20, 2018 By AuthorVS2

In this article, we have addressed how user can use label margins in MS Access

A good designed and structured Report or Form is more likely to catch the eyes of the onlookers than a plain bland data record. All organizations, businesses, and programmers design them.  But the problem with assigning different people to design the same Report or Form is that they are going to design them according to their preferences and taste unless they are all using the same default wizards.Working With Label Margins In MS Access

Some users might focus on the information stored in a report, and their structure and order, but it is also important to make it presentable in a given time. If you put the right amount of effort in the report, it is likely to pay you back. As the office reports travel from place to place via Emails or faxes. And if compared to other reports your masterpiece is able to impress your boss. Fortunately, MS Access offers multiple designing tools which can make your forms and reports strikingly beautiful.

Now let’s talk about some of these properties offered by label margins in MS Access

  • WLabel Marginizards come with a default formatting, which maintains (Font, style, text, size) ratio of the content, saving a good amount of time. Users can also modify them according to their requirements. Users can also insert tables, graphs etc in their reports to make them more comprehensible.
  • For changing the values in the Sheet users need to select View and then open its properties.
  • Users can also align the text in their reports by selecting Format > Align > Left/Right/Center. The heading or the name of the company should be placed in the center, whereas the subject of the report should be aligned from the left side.
  • Users can also insert Borders or Headers in the report. They can change the color, structure of the type of line, by going in the properties of the report.
  • Users can also insert and edit the thickness of the margin in their reports. Margins promote better division structure, separating headings from detail section. In order to change the Property value of their Header Label, users can go to the properties of Top Margin and change its value to their desired size.

Note: Users can make any changes in their forms or reports using label margins. However, these changes should be able to present the essence of the content present in the database. Like any other report, it is important, that it is structured with a futuristic and professional outlook, as it is likely to be used in the future for inserting new data or entries in it. It is also important that size of the headings and the subheading is differentiated and highlighted so that it becomes easier for the readers to locate different sections of the reports. A good report is one which contains all the necessary research work, in an organized way which can be understood by even an amateur. A well-designed report conveys the meaning of the content 10 times more efficiently to the users compared to plain report without any categorizations or division of paragraphs.

Every business user of MS Access needs to keep in place contingency plans to deal with a corrupted Access database. So if you too are using MS Access for business, do ensure you do regular backups and have powerful recovery tool DataNumen Access Repair handy.

Author Introduction:

Vivian Stevens is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including sql fix and excel recovery software products. For more information visit www.datanumen.com

Be the first to comment