Go Premium for a chance to win a PS4. Enter to Win


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

Posted on 2011-02-18
Medium Priority
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 49

Accepted Solution

Dale Fye earned 1000 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 (http://www.allenbrowne.com/ser-61.html) 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 1000 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?
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.


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

Industry Leaders: 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!

Question has a verified solution.

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

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Suggested Courses

782 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