From based on a query that opens to a new blank record

Posted on 2011-02-18
Last Modified: 2013-11-27
Access 2007
I have a form that is based on a query.  It is the first time I've used this method.  But it does not allow for creating new records.

When I create a form based on a table, in the On Load property I use this
Private Sub Form_Load()
Me.DataEntry = True
End Sub

to open with a new blank record.

Is there something different required when a form is based on a query?

Question by:gbnorton
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 48

Accepted Solution

Dale Fye (Access MVP) earned 250 total points
ID: 34928969
It may be that the query is not updateable.  Open the query by itself and attempt to change or add a record.  If you get at "this query is not updateable" message, then consult Allen Browne's website ( for a discussion of some of the causes of this.
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 250 total points
ID: 34928970
You first need to be sure that the query itself is Updatable.  If it's based on more than one table, then it may not be.  Run the query by itself and see if you can update and/or add a record.

LVL 21
ID: 34929037
Normally I only have a single table in the query for the form's record source.

 For example: If you havea  look up table then you can use a combo box to display the related data without the need to add the look up table to the form's query.

If you have a one-to-many relationship then  I would not add the table to the form's query. I would use a sub form for the many side's table

This might also help:
Why is my query read-only?
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 34929086
I ran the query by itself and could not add or edit a record.

In the query I have two expressions.  One for calculating a total and one for an average.

From Allen Browne's article that seems the reason.

It is a single table.

LVL 75
ID: 34929119
"In the query I have two expressions."
Is this a Group By query then? If so, that is ... the reason. No can do.

LVL 21
ID: 34929617
gbnorton, as mx stated you can not use a Totaling query and it be updatable. It may be possible to get eh total use want by using a sub query and the query still be updatable.

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question