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
LVL 47

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 - Access MVP)
DatabaseMX (Joe Anderson - Access MVP) 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?
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.


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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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…

831 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