recordset is not up dateable

[Guest]

Hello,

Thanks to people who have been answering my posts. Before I try some of the
solutions that I have received, I need to address this problem first:

I have created a form that is based on a query. This query is updateable
(meaning, I can add and delete fields from the field list of the form by
going into the query's design view, or by clicking [...] of the form's
RecordSet). Also, in the form's properties I have allowed for edits,
additions, and deletions. The form is not "locked". However, I am still
unable to edit or add new records with my form. When I try to type in a
field I get "This Recordset is not updateable" at the very bottom of the
screen. Are there any suggestions for this problem?

Thank you,
Diana

 

[Guest]

Hi, Diana.

I have created a form that is based on a query. This query is updateable
(meaning, I can add and delete fields ....
When I try to type in a
field I get "This Recordset is not updateable" at the very bottom of the
screen. Are there any suggestions for this problem?

Updateable refers to the data, not the data structure. There are a number
of reasons the recordset isn't updateable, i.e., the lack of a primary key or
unique index on a table, or use of calculated fields, or distinct values or
rows, or the type of query (such as UNION, SQL Passthrough, crosstab,
Cartesian join, or action queries, or even select queries with aggregate
functions), a "read-only" table, a linked table to a database which uses an
ISAM driver that can't update data, et cetera.

One can usually tell whether a recordset is updateable just by reading the
SQL statement, but sometimes the outside influences (as I mentioned above)
affect the updateability of an otherwise updateable recordset. Please post
the SQL statement of the query your form is bound to and list any of the
circumstances mentioned above that apply to your situation, as well.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that questions
answered the quickest are often from those who have a history of rewarding
the contributors who have taken the time to answer questions correctly.

Hello,

Thanks to people who have been answering my posts. Before I try some of the
solutions that I have received, I need to address this problem first:

I have created a form that is based on a query. This query is updateable
(meaning, I can add and delete fields from the field list of the form by
going into the query's design view, or by clicking [...] of the form's
RecordSet). Also, in the form's properties I have allowed for edits,
additions, and deletions. The form is not "locked". However, I am still
unable to edit or add new records with my form. When I try to type in a
field I get "This Recordset is not updateable" at the very bottom of the
screen. Are there any suggestions for this problem?

Thank you,
Diana

 

[Guest]

Hello,

I am not quite sure if this is SQL code--I always thought of this as "visual
basic" -- I not sure where SQL stuff would be located.

Let me know if this is what you were asking for. Thank you!

Diana

Option Compare Database

Private Sub MVC_Marketing_CORE_Button_Click()
On Error GoTo Err_MVC_Marketing_CORE_Button_Click

Dim stDocName As String
Dim stLinkCriteria As String

DoCmd.OpenForm "MVC Marketing CORE", OpenArgs:=LeadNumber

Exit_MVC_Marketing_CORE_Button_Click:
Exit Sub

Err_MVC_Marketing_CORE_Button_Click:
MsgBox Err.Description
Resume Exit_MVC_Marketing_CORE_Button_Click

End Sub
Private Sub MVC_Schedule_and_COMM_Button_Click()
On Error GoTo Err_MVC_Schedule_and_COMM_Button_Click

Dim stDocName As String
Dim stLinkCriteria As String

DoCmd.OpenForm "MVC Schedule and Communications", OpenArgs:=TourNumber

Exit_MVC_Schedule_and_COMM_Button_Click:
Exit Sub

Err_MVC_Schedule_and_COMM_Button_Click:
MsgBox Err.Description
Resume Exit_MVC_Schedule_and_COMM_Button_Click

End Sub
---------------------------------------------------

Hi, Diana.

I have created a form that is based on a query. This query is updateable
(meaning, I can add and delete fields ....
When I try to type in a
field I get "This Recordset is not updateable" at the very bottom of the
screen. Are there any suggestions for this problem?

Updateable refers to the data, not the data structure. There are a number
of reasons the recordset isn't updateable, i.e., the lack of a primary key or
unique index on a table, or use of calculated fields, or distinct values or
rows, or the type of query (such as UNION, SQL Passthrough, crosstab,
Cartesian join, or action queries, or even select queries with aggregate
functions), a "read-only" table, a linked table to a database which uses an
ISAM driver that can't update data, et cetera.

One can usually tell whether a recordset is updateable just by reading the
SQL statement, but sometimes the outside influences (as I mentioned above)
affect the updateability of an otherwise updateable recordset. Please post
the SQL statement of the query your form is bound to and list any of the
circumstances mentioned above that apply to your situation, as well.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that questions
answered the quickest are often from those who have a history of rewarding
the contributors who have taken the time to answer questions correctly.

Hello,

Thanks to people who have been answering my posts. Before I try some of the
solutions that I have received, I need to address this problem first:

I have created a form that is based on a query. This query is updateable
(meaning, I can add and delete fields from the field list of the form by
going into the query's design view, or by clicking [...] of the form's
RecordSet). Also, in the form's properties I have allowed for edits,
additions, and deletions. The form is not "locked". However, I am still
unable to edit or add new records with my form. When I try to type in a
field I get "This Recordset is not updateable" at the very bottom of the
screen. Are there any suggestions for this problem?

Thank you,
Diana

 

[Guest]

1) Yes, I have a primary key
2) There is a calculated field, but the calculation is done in another
query--not the form itself.
3) Not sure what you mean by "distinct values or rows"
4) Not sure how to determine the "type of query"
5) No "read-only" tables are linked to my form
6) Don't know about the ISAM driver thing... not too familiar with
computers. I'm just starting to learn about this technical stuff.

the lack of a primary key or
unique index on a table, or use of calculated fields, or distinct values or
rows, or the type of query (such as UNION, SQL Passthrough, crosstab,
Cartesian join, or action queries, or even select queries with aggregate
functions), a "read-only" table, a linked table to a database which uses an
ISAM driver that can't update data, et cetera.

Hi, Diana.

I have created a form that is based on a query. This query is updateable
(meaning, I can add and delete fields ....
When I try to type in a
field I get "This Recordset is not updateable" at the very bottom of the
screen. Are there any suggestions for this problem?

Updateable refers to the data, not the data structure. There are a number
of reasons the recordset isn't updateable, i.e., the lack of a primary key or
unique index on a table, or use of calculated fields, or distinct values or
rows, or the type of query (such as UNION, SQL Passthrough, crosstab,
Cartesian join, or action queries, or even select queries with aggregate
functions), a "read-only" table, a linked table to a database which uses an
ISAM driver that can't update data, et cetera.

One can usually tell whether a recordset is updateable just by reading the
SQL statement, but sometimes the outside influences (as I mentioned above)
affect the updateability of an otherwise updateable recordset. Please post
the SQL statement of the query your form is bound to and list any of the
circumstances mentioned above that apply to your situation, as well.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that questions
answered the quickest are often from those who have a history of rewarding
the contributors who have taken the time to answer questions correctly.

Hello,

Thanks to people who have been answering my posts. Before I try some of the
solutions that I have received, I need to address this problem first:

I have created a form that is based on a query. This query is updateable
(meaning, I can add and delete fields from the field list of the form by
going into the query's design view, or by clicking [...] of the form's
RecordSet). Also, in the form's properties I have allowed for edits,
additions, and deletions. The form is not "locked". However, I am still
unable to edit or add new records with my form. When I try to type in a
field I get "This Recordset is not updateable" at the very bottom of the
screen. Are there any suggestions for this problem?

Thank you,
Diana

 

[Guest]

Hi, Diana.

1) Yes, I have a primary key
Excellent!

2) There is a calculated field, but the calculation is done in another
query--not the form itself.

It doesn't matter where the calculation is made, whether it's a bound query
or the form's properties or VBA code, because you won't be able to change
(update) the value of a calculated field unless you remove the expression
calculating this value. But calculated values are usually put there for a
very good reason.

3) Not sure what you mean by "distinct values or rows"

In the "many" side table, there may be multiple entities (rows), but you may
not need to know that Joe Schmuckatelli has five orders of widgets and Hugh
Dunnit has three orders of widgets. You may just want to know the names of
your customers, so the use of the keyword "DISTINCT" is placed in the query
to differentiate the people who made these orders, not their total orders.

4) Not sure how to determine the "type of query"

If you save the query, then the Database Window shows an icon next to each
query indicating the type of query (1st clue). If you open any query, you'll
see the type of query next to the name of the query in the query's Title Bar
(2nd clue). Each type of query has a different job to do, and as you get
more familiar with databases, you'll learn which ones do what for you.

6) Don't know about the ISAM driver thing... not too familiar with
computers.

If your tables aren't external to the Access database, then an ISAM driver
isn't needed to handle the data transfer and manipulation, so you don't need
to worry about this for now.

I highly recommend that you either find someone knowledgeable in databases
who can sit down with you and explain the little mysteries to you, or take a
few database classes because knowing the secrets of the little mysteries goes
a long way to figuring out the big mysteries.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

1) Yes, I have a primary key
2) There is a calculated field, but the calculation is done in another
query--not the form itself.
3) Not sure what you mean by "distinct values or rows"
4) Not sure how to determine the "type of query"
5) No "read-only" tables are linked to my form
6) Don't know about the ISAM driver thing... not too familiar with
computers. I'm just starting to learn about this technical stuff.

the lack of a primary key or
unique index on a table, or use of calculated fields, or distinct values or
rows, or the type of query (such as UNION, SQL Passthrough, crosstab,
Cartesian join, or action queries, or even select queries with aggregate
functions), a "read-only" table, a linked table to a database which uses an
ISAM driver that can't update data, et cetera.

Hi, Diana.

I have created a form that is based on a query. This query is updateable
(meaning, I can add and delete fields ....
When I try to type in a
field I get "This Recordset is not updateable" at the very bottom of the
screen. Are there any suggestions for this problem?

Updateable refers to the data, not the data structure. There are a number
of reasons the recordset isn't updateable, i.e., the lack of a primary key or
unique index on a table, or use of calculated fields, or distinct values or
rows, or the type of query (such as UNION, SQL Passthrough, crosstab,
Cartesian join, or action queries, or even select queries with aggregate
functions), a "read-only" table, a linked table to a database which uses an
ISAM driver that can't update data, et cetera.

One can usually tell whether a recordset is updateable just by reading the
SQL statement, but sometimes the outside influences (as I mentioned above)
affect the updateability of an otherwise updateable recordset. Please post
the SQL statement of the query your form is bound to and list any of the
circumstances mentioned above that apply to your situation, as well.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that questions
answered the quickest are often from those who have a history of rewarding
the contributors who have taken the time to answer questions correctly.

Hello,

Thanks to people who have been answering my posts. Before I try some of the
solutions that I have received, I need to address this problem first:

I have created a form that is based on a query. This query is updateable
(meaning, I can add and delete fields from the field list of the form by
going into the query's design view, or by clicking [...] of the form's
RecordSet). Also, in the form's properties I have allowed for edits,
additions, and deletions. The form is not "locked". However, I am still
unable to edit or add new records with my form. When I try to type in a
field I get "This Recordset is not updateable" at the very bottom of the
screen. Are there any suggestions for this problem?

Thank you,
Diana

 

[Guest]

I think this is the SQL statement, sorry about the mixup:

SELECT [MVC Schedule].TourNumber, [MVC Schedule].LeadNumber, [MVC
Schedule].TourStatus, [MVC Schedule].[Tour Date], [MVC Schedule].[Tour Time],
[MVC Schedule].MrtlStatus, [MVC Core].[First Name1], [MVC Core].[Last Name1],
[MVC Core].[First Name2], [MVC Core].[Last Name2], [MVC Schedule].Income,
[MVC Schedule].Occupation, [MVC Schedule].Qualified, [MVC
Schedule].DisQualReason, [MVC Schedule].[DisQual Cmnts], [MVC
Schedule].ArrvlTime, [MVC Schedule].StartTime, [MVC Schedule].EndTime, [MVC
Schedule].Gift1, [MVC Schedule].Gift1No, [MVC Schedule].VOID1, [MVC
Schedule].Gift2, [MVC Schedule].Gift2No, [MVC Schedule].VOID2, [MVC
Schedule].Gift3, [MVC Schedule].Gift3No, [MVC Schedule].VOID3, [MVC
Schedule].Gift4, [MVC Schedule].Gift4No, [MVC Schedule].VOID4, [MVC
Schedule].Gift5, [MVC Schedule].Gift5No, [MVC Schedule].VOID5,
QueryTourLength.TourLength
FROM ([MVC Core] INNER JOIN QueryTourLength ON [MVC Core].LeadNumber =
QueryTourLength.LeadNumber) INNER JOIN [MVC Schedule] ON [MVC
Core].LeadNumber = [MVC Schedule].LeadNumber;

Hi, Diana.

1) Yes, I have a primary key
Excellent!

2) There is a calculated field, but the calculation is done in another
query--not the form itself.

It doesn't matter where the calculation is made, whether it's a bound query
or the form's properties or VBA code, because you won't be able to change
(update) the value of a calculated field unless you remove the expression
calculating this value. But calculated values are usually put there for a
very good reason.

3) Not sure what you mean by "distinct values or rows"

In the "many" side table, there may be multiple entities (rows), but you may
not need to know that Joe Schmuckatelli has five orders of widgets and Hugh
Dunnit has three orders of widgets. You may just want to know the names of
your customers, so the use of the keyword "DISTINCT" is placed in the query
to differentiate the people who made these orders, not their total orders.

4) Not sure how to determine the "type of query"

If you save the query, then the Database Window shows an icon next to each
query indicating the type of query (1st clue). If you open any query, you'll
see the type of query next to the name of the query in the query's Title Bar
(2nd clue). Each type of query has a different job to do, and as you get
more familiar with databases, you'll learn which ones do what for you.

6) Don't know about the ISAM driver thing... not too familiar with
computers.

If your tables aren't external to the Access database, then an ISAM driver
isn't needed to handle the data transfer and manipulation, so you don't need
to worry about this for now.

I highly recommend that you either find someone knowledgeable in databases
who can sit down with you and explain the little mysteries to you, or take a
few database classes because knowing the secrets of the little mysteries goes
a long way to figuring out the big mysteries.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

1) Yes, I have a primary key
2) There is a calculated field, but the calculation is done in another
query--not the form itself.
3) Not sure what you mean by "distinct values or rows"
4) Not sure how to determine the "type of query"
5) No "read-only" tables are linked to my form
6) Don't know about the ISAM driver thing... not too familiar with
computers. I'm just starting to learn about this technical stuff.

the lack of a primary key or
unique index on a table, or use of calculated fields, or distinct values or
rows, or the type of query (such as UNION, SQL Passthrough, crosstab,
Cartesian join, or action queries, or even select queries with aggregate
functions), a "read-only" table, a linked table to a database which uses an
ISAM driver that can't update data, et cetera.

Hi, Diana.

I have created a form that is based on a query. This query is updateable
(meaning, I can add and delete fields ....
When I try to type in a
field I get "This Recordset is not updateable" at the very bottom of the
screen. Are there any suggestions for this problem?

Updateable refers to the data, not the data structure. There are a number
of reasons the recordset isn't updateable, i.e., the lack of a primary key or
unique index on a table, or use of calculated fields, or distinct values or
rows, or the type of query (such as UNION, SQL Passthrough, crosstab,
Cartesian join, or action queries, or even select queries with aggregate
functions), a "read-only" table, a linked table to a database which uses an
ISAM driver that can't update data, et cetera.

One can usually tell whether a recordset is updateable just by reading the
SQL statement, but sometimes the outside influences (as I mentioned above)
affect the updateability of an otherwise updateable recordset. Please post
the SQL statement of the query your form is bound to and list any of the
circumstances mentioned above that apply to your situation, as well.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that questions
answered the quickest are often from those who have a history of rewarding
the contributors who have taken the time to answer questions correctly.


:

Hello,

Thanks to people who have been answering my posts. Before I try some of the
solutions that I have received, I need to address this problem first:

I have created a form that is based on a query. This query is updateable
(meaning, I can add and delete fields from the field list of the form by
going into the query's design view, or by clicking [...] of the form's
RecordSet). Also, in the form's properties I have allowed for edits,
additions, and deletions. The form is not "locked". However, I am still
unable to edit or add new records with my form. When I try to type in a
field I get "This Recordset is not updateable" at the very bottom of the
screen. Are there any suggestions for this problem?

Thank you,
Diana

 

[Guest]

Hi, Diana.

I am not quite sure if this is SQL code--I always thought of this as "visual
basic"

You're right. That's VB code, which is very flexible for automating steps
and reacting to events in the program. The SQL code I mentioned is a
different programming language designed to deal with data sets, not
procedural logic like VBA is designed for.

I not sure where SQL stuff would be located.

If it's a saved query, then you may select the name of the object in the
Database Window, then open it in Datasheet View or Design View. After the
query is open, the View -> SQL View menu can be used to view the SQL pane.

If it's a SQL statement stored in the form's Record Source Property, then
open the form in Design View and open the Properties dialog window. Select
the "Data" tab and place the cursor in the "Record Source" Property. Press
<SHIFT><F2> to open the Zoom dialog window.

If it's a SQL statement in a code module, then you'll need to do a search
for it, but generally this would be found in the code module behind the form
itself.

Once you find the SQL code, please copy and paste it into the next message
so that we can take a look at it. We'll probably have a few more questions
for you in order to clarify information regarding your database structure.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

Hello,

I am not quite sure if this is SQL code--I always thought of this as "visual
basic" -- I not sure where SQL stuff would be located.

Let me know if this is what you were asking for. Thank you!

Diana

Option Compare Database

Private Sub MVC_Marketing_CORE_Button_Click()
On Error GoTo Err_MVC_Marketing_CORE_Button_Click

Dim stDocName As String
Dim stLinkCriteria As String

DoCmd.OpenForm "MVC Marketing CORE", OpenArgs:=LeadNumber

Exit_MVC_Marketing_CORE_Button_Click:
Exit Sub

Err_MVC_Marketing_CORE_Button_Click:
MsgBox Err.Description
Resume Exit_MVC_Marketing_CORE_Button_Click

End Sub
Private Sub MVC_Schedule_and_COMM_Button_Click()
On Error GoTo Err_MVC_Schedule_and_COMM_Button_Click

Dim stDocName As String
Dim stLinkCriteria As String

DoCmd.OpenForm "MVC Schedule and Communications", OpenArgs:=TourNumber

Exit_MVC_Schedule_and_COMM_Button_Click:
Exit Sub

Err_MVC_Schedule_and_COMM_Button_Click:
MsgBox Err.Description
Resume Exit_MVC_Schedule_and_COMM_Button_Click

End Sub
---------------------------------------------------

Hi, Diana.

I have created a form that is based on a query. This query is updateable
(meaning, I can add and delete fields ....
When I try to type in a
field I get "This Recordset is not updateable" at the very bottom of the
screen. Are there any suggestions for this problem?

Updateable refers to the data, not the data structure. There are a number
of reasons the recordset isn't updateable, i.e., the lack of a primary key or
unique index on a table, or use of calculated fields, or distinct values or
rows, or the type of query (such as UNION, SQL Passthrough, crosstab,
Cartesian join, or action queries, or even select queries with aggregate
functions), a "read-only" table, a linked table to a database which uses an
ISAM driver that can't update data, et cetera.

One can usually tell whether a recordset is updateable just by reading the
SQL statement, but sometimes the outside influences (as I mentioned above)
affect the updateability of an otherwise updateable recordset. Please post
the SQL statement of the query your form is bound to and list any of the
circumstances mentioned above that apply to your situation, as well.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that questions
answered the quickest are often from those who have a history of rewarding
the contributors who have taken the time to answer questions correctly.

Hello,

Thanks to people who have been answering my posts. Before I try some of the
solutions that I have received, I need to address this problem first:

I have created a form that is based on a query. This query is updateable
(meaning, I can add and delete fields from the field list of the form by
going into the query's design view, or by clicking [...] of the form's
RecordSet). Also, in the form's properties I have allowed for edits,
additions, and deletions. The form is not "locked". However, I am still
unable to edit or add new records with my form. When I try to type in a
field I get "This Recordset is not updateable" at the very bottom of the
screen. Are there any suggestions for this problem?

Thank you,
Diana

 

[Guest]

Hi, Diana.

I see a few potential problems, particularly if the "one" side and "many"
sides aren't set up properly for this query.

1.) LeadNumber is the join field in each of the tables. Are you trying to
add new records and either the MVC Core table or a table in QueryTourLength
are on the "many" sides? Possibly the QueryTourLength query doesn't have
join lines drawn between the tables in the QBE Design Grid. If so, make sure
the lines are drawn to connect the join fields of the tables in this query.
(I'm assuming this is a query of one or more tables, and MVC Core and MVC
Schedule are tables -- please correct me if I'm wrong or paste the other
queries in the message next time so we can evaluate those, too.)

2.) Is MVC Schedule on the "one" side? If so, you'll need to enable
cascading updates.

3.) Does the QueryTourLength query contain an outer join? Or an inner join
with NULL values in the join fields? If so, make sure that the "Required"
Property is set for the join fields in all of these tables. (Remember that a
unique index that allows NULL's allows non-unique values to be placed in the
index because of the way that NULL's are evaluated. Don't allow this in your
database designs. Ever.)

4.) Are the relationships set up as "many" to "one" to "many," with the
form's Recordset Type assigned as a Dynaset? In this case, MVC Core would be
"many," QueryTourLength would be "one," and MVC Schedule would be "many."
This would need to be changed to a Recordset Type of Dynaset (Inconsistent
Updates).

5.) Is the QueryTourLength query one of those special types of queries I
mentioned earlier?

6.) Do all of the tables have primary keys, including any tables mentioned
within the QueryTourLength query?

7.) Which fields are calculated fields? Those can't be assigned values
from your form.

8.) The field names Gift1, Gift2, Gift3, ... Gift1No, Gift2No, Gift13No,
.... VOID4, VOID5, ... First Name1, et cetera, are big red flags that there's
a database design problem, where the same attributes are assigned separate
columns, instead of separate rows in the table under a single column. This
type of database design problem will create insertion and deletion anomalies,
provide inconsistent data reports and updates, and waste disk space and
development time. The rule of thumb is "Fields (columns) are expensive,
records (rows) are cheap." I won't go into a lesson here about database
design, because it is a complex subject that should probably be best taught
in a classroom or in a one-on-one environment from a knowledgeable relational
database expert.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

I think this is the SQL statement, sorry about the mixup:

SELECT [MVC Schedule].TourNumber, [MVC Schedule].LeadNumber, [MVC
Schedule].TourStatus, [MVC Schedule].[Tour Date], [MVC Schedule].[Tour Time],
[MVC Schedule].MrtlStatus, [MVC Core].[First Name1], [MVC Core].[Last Name1],
[MVC Core].[First Name2], [MVC Core].[Last Name2], [MVC Schedule].Income,
[MVC Schedule].Occupation, [MVC Schedule].Qualified, [MVC
Schedule].DisQualReason, [MVC Schedule].[DisQual Cmnts], [MVC
Schedule].ArrvlTime, [MVC Schedule].StartTime, [MVC Schedule].EndTime, [MVC
Schedule].Gift1, [MVC Schedule].Gift1No, [MVC Schedule].VOID1, [MVC
Schedule].Gift2, [MVC Schedule].Gift2No, [MVC Schedule].VOID2, [MVC
Schedule].Gift3, [MVC Schedule].Gift3No, [MVC Schedule].VOID3, [MVC
Schedule].Gift4, [MVC Schedule].Gift4No, [MVC Schedule].VOID4, [MVC
Schedule].Gift5, [MVC Schedule].Gift5No, [MVC Schedule].VOID5,
QueryTourLength.TourLength
FROM ([MVC Core] INNER JOIN QueryTourLength ON [MVC Core].LeadNumber =
QueryTourLength.LeadNumber) INNER JOIN [MVC Schedule] ON [MVC
Core].LeadNumber = [MVC Schedule].LeadNumber;

Hi, Diana.

1) Yes, I have a primary key
Excellent!

2) There is a calculated field, but the calculation is done in another
query--not the form itself.

It doesn't matter where the calculation is made, whether it's a bound query
or the form's properties or VBA code, because you won't be able to change
(update) the value of a calculated field unless you remove the expression
calculating this value. But calculated values are usually put there for a
very good reason.

3) Not sure what you mean by "distinct values or rows"

In the "many" side table, there may be multiple entities (rows), but you may
not need to know that Joe Schmuckatelli has five orders of widgets and Hugh
Dunnit has three orders of widgets. You may just want to know the names of
your customers, so the use of the keyword "DISTINCT" is placed in the query
to differentiate the people who made these orders, not their total orders.

4) Not sure how to determine the "type of query"

If you save the query, then the Database Window shows an icon next to each
query indicating the type of query (1st clue). If you open any query, you'll
see the type of query next to the name of the query in the query's Title Bar
(2nd clue). Each type of query has a different job to do, and as you get
more familiar with databases, you'll learn which ones do what for you.

6) Don't know about the ISAM driver thing... not too familiar with
computers.

If your tables aren't external to the Access database, then an ISAM driver
isn't needed to handle the data transfer and manipulation, so you don't need
to worry about this for now.

I highly recommend that you either find someone knowledgeable in databases
who can sit down with you and explain the little mysteries to you, or take a
few database classes because knowing the secrets of the little mysteries goes
a long way to figuring out the big mysteries.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

1) Yes, I have a primary key
2) There is a calculated field, but the calculation is done in another
query--not the form itself.
3) Not sure what you mean by "distinct values or rows"
4) Not sure how to determine the "type of query"
5) No "read-only" tables are linked to my form
6) Don't know about the ISAM driver thing... not too familiar with
computers. I'm just starting to learn about this technical stuff.

the lack of a primary key or
unique index on a table, or use of calculated fields, or distinct values or
rows, or the type of query (such as UNION, SQL Passthrough, crosstab,
Cartesian join, or action queries, or even select queries with aggregate
functions), a "read-only" table, a linked table to a database which uses an
ISAM driver that can't update data, et cetera.


:

Hi, Diana.

I have created a form that is based on a query. This query is updateable
(meaning, I can add and delete fields ....
When I try to type in a
field I get "This Recordset is not updateable" at the very bottom of the
screen. Are there any suggestions for this problem?

Updateable refers to the data, not the data structure. There are a number
of reasons the recordset isn't updateable, i.e., the lack of a primary key or
unique index on a table, or use of calculated fields, or distinct values or
rows, or the type of query (such as UNION, SQL Passthrough, crosstab,
Cartesian join, or action queries, or even select queries with aggregate
functions), a "read-only" table, a linked table to a database which uses an
ISAM driver that can't update data, et cetera.

One can usually tell whether a recordset is updateable just by reading the
SQL statement, but sometimes the outside influences (as I mentioned above)
affect the updateability of an otherwise updateable recordset. Please post
the SQL statement of the query your form is bound to and list any of the
circumstances mentioned above that apply to your situation, as well.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that questions
answered the quickest are often from those who have a history of rewarding
the contributors who have taken the time to answer questions correctly.


:

Hello,

Thanks to people who have been answering my posts. Before I try some of the
solutions that I have received, I need to address this problem first:

I have created a form that is based on a query. This query is updateable
(meaning, I can add and delete fields from the field list of the form by
going into the query's design view, or by clicking [...] of the form's
RecordSet). Also, in the form's properties I have allowed for edits,
additions, and deletions. The form is not "locked". However, I am still
unable to edit or add new records with my form. When I try to type in a
field I get "This Recordset is not updateable" at the very bottom of the
screen. Are there any suggestions for this problem?

Thank you,
Diana

 

[Guest]

Hello,

I started checking the things you mentioned, and realized that the problem
was too complex given the amount of time I have to fix it. I came up with a
quick solution that seems to be working well, and is actually better than my
original design. I had previously taken the contents of one table and split
it into two forms, the second of which was giving me problems. I decided to
delete this form, and add the necessary fields to the first form, on a
different tab. Now I can edit these fields without a hitch. Like you said,
it was probably some relationship problem between the different tables and
form. I'll definitely take up your suggestion and get some Access
training... I had thought building a database would be quite simple. I
realize now that there are a lot of things I do not yet understand.

Thanks for the help--I learned a few things (like SQL and Visual Basic are
DIFFERENT codes, you can change the properties of relationships, etc...).

Diana

Hi, Diana.

I see a few potential problems, particularly if the "one" side and "many"
sides aren't set up properly for this query.

1.) LeadNumber is the join field in each of the tables. Are you trying to
add new records and either the MVC Core table or a table in QueryTourLength
are on the "many" sides? Possibly the QueryTourLength query doesn't have
join lines drawn between the tables in the QBE Design Grid. If so, make sure
the lines are drawn to connect the join fields of the tables in this query.
(I'm assuming this is a query of one or more tables, and MVC Core and MVC
Schedule are tables -- please correct me if I'm wrong or paste the other
queries in the message next time so we can evaluate those, too.)

2.) Is MVC Schedule on the "one" side? If so, you'll need to enable
cascading updates.

3.) Does the QueryTourLength query contain an outer join? Or an inner join
with NULL values in the join fields? If so, make sure that the "Required"
Property is set for the join fields in all of these tables. (Remember that a
unique index that allows NULL's allows non-unique values to be placed in the
index because of the way that NULL's are evaluated. Don't allow this in your
database designs. Ever.)

4.) Are the relationships set up as "many" to "one" to "many," with the
form's Recordset Type assigned as a Dynaset? In this case, MVC Core would be
"many," QueryTourLength would be "one," and MVC Schedule would be "many."
This would need to be changed to a Recordset Type of Dynaset (Inconsistent
Updates).

5.) Is the QueryTourLength query one of those special types of queries I
mentioned earlier?

6.) Do all of the tables have primary keys, including any tables mentioned
within the QueryTourLength query?

7.) Which fields are calculated fields? Those can't be assigned values
from your form.

8.) The field names Gift1, Gift2, Gift3, ... Gift1No, Gift2No, Gift13No,
... VOID4, VOID5, ... First Name1, et cetera, are big red flags that there's
a database design problem, where the same attributes are assigned separate
columns, instead of separate rows in the table under a single column. This
type of database design problem will create insertion and deletion anomalies,
provide inconsistent data reports and updates, and waste disk space and
development time. The rule of thumb is "Fields (columns) are expensive,
records (rows) are cheap." I won't go into a lesson here about database
design, because it is a complex subject that should probably be best taught
in a classroom or in a one-on-one environment from a knowledgeable relational
database expert.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

I think this is the SQL statement, sorry about the mixup:

SELECT [MVC Schedule].TourNumber, [MVC Schedule].LeadNumber, [MVC
Schedule].TourStatus, [MVC Schedule].[Tour Date], [MVC Schedule].[Tour Time],
[MVC Schedule].MrtlStatus, [MVC Core].[First Name1], [MVC Core].[Last Name1],
[MVC Core].[First Name2], [MVC Core].[Last Name2], [MVC Schedule].Income,
[MVC Schedule].Occupation, [MVC Schedule].Qualified, [MVC
Schedule].DisQualReason, [MVC Schedule].[DisQual Cmnts], [MVC
Schedule].ArrvlTime, [MVC Schedule].StartTime, [MVC Schedule].EndTime, [MVC
Schedule].Gift1, [MVC Schedule].Gift1No, [MVC Schedule].VOID1, [MVC
Schedule].Gift2, [MVC Schedule].Gift2No, [MVC Schedule].VOID2, [MVC
Schedule].Gift3, [MVC Schedule].Gift3No, [MVC Schedule].VOID3, [MVC
Schedule].Gift4, [MVC Schedule].Gift4No, [MVC Schedule].VOID4, [MVC
Schedule].Gift5, [MVC Schedule].Gift5No, [MVC Schedule].VOID5,
QueryTourLength.TourLength
FROM ([MVC Core] INNER JOIN QueryTourLength ON [MVC Core].LeadNumber =
QueryTourLength.LeadNumber) INNER JOIN [MVC Schedule] ON [MVC
Core].LeadNumber = [MVC Schedule].LeadNumber;

Hi, Diana.

1) Yes, I have a primary key

Excellent!

2) There is a calculated field, but the calculation is done in another
query--not the form itself.

It doesn't matter where the calculation is made, whether it's a bound query
or the form's properties or VBA code, because you won't be able to change
(update) the value of a calculated field unless you remove the expression
calculating this value. But calculated values are usually put there for a
very good reason.

3) Not sure what you mean by "distinct values or rows"

In the "many" side table, there may be multiple entities (rows), but you may
not need to know that Joe Schmuckatelli has five orders of widgets and Hugh
Dunnit has three orders of widgets. You may just want to know the names of
your customers, so the use of the keyword "DISTINCT" is placed in the query
to differentiate the people who made these orders, not their total orders.

4) Not sure how to determine the "type of query"

If you save the query, then the Database Window shows an icon next to each
query indicating the type of query (1st clue). If you open any query, you'll
see the type of query next to the name of the query in the query's Title Bar
(2nd clue). Each type of query has a different job to do, and as you get
more familiar with databases, you'll learn which ones do what for you.

6) Don't know about the ISAM driver thing... not too familiar with
computers.

If your tables aren't external to the Access database, then an ISAM driver
isn't needed to handle the data transfer and manipulation, so you don't need
to worry about this for now.

I highly recommend that you either find someone knowledgeable in databases
who can sit down with you and explain the little mysteries to you, or take a
few database classes because knowing the secrets of the little mysteries goes
a long way to figuring out the big mysteries.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)


:

1) Yes, I have a primary key
2) There is a calculated field, but the calculation is done in another
query--not the form itself.
3) Not sure what you mean by "distinct values or rows"
4) Not sure how to determine the "type of query"
5) No "read-only" tables are linked to my form
6) Don't know about the ISAM driver thing... not too familiar with
computers. I'm just starting to learn about this technical stuff.

the lack of a primary key or
unique index on a table, or use of calculated fields, or distinct values or
rows, or the type of query (such as UNION, SQL Passthrough, crosstab,
Cartesian join, or action queries, or even select queries with aggregate
functions), a "read-only" table, a linked table to a database which uses an
ISAM driver that can't update data, et cetera.


:

Hi, Diana.

I have created a form that is based on a query. This query is updateable
(meaning, I can add and delete fields ....
When I try to type in a
field I get "This Recordset is not updateable" at the very bottom of the
screen. Are there any suggestions for this problem?

Updateable refers to the data, not the data structure. There are a number
of reasons the recordset isn't updateable, i.e., the lack of a primary key or
unique index on a table, or use of calculated fields, or distinct values or
rows, or the type of query (such as UNION, SQL Passthrough, crosstab,
Cartesian join, or action queries, or even select queries with aggregate
functions), a "read-only" table, a linked table to a database which uses an
ISAM driver that can't update data, et cetera.

One can usually tell whether a recordset is updateable just by reading the
SQL statement, but sometimes the outside influences (as I mentioned above)
affect the updateability of an otherwise updateable recordset. Please post
the SQL statement of the query your form is bound to and list any of the
circumstances mentioned above that apply to your situation, as well.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that questions
answered the quickest are often from those who have a history of rewarding
the contributors who have taken the time to answer questions correctly.


:

Hello,

Thanks to people who have been answering my posts. Before I try some of the
solutions that I have received, I need to address this problem first:

I have created a form that is based on a query. This query is updateable
(meaning, I can add and delete fields from the field list of the form by
going into the query's design view, or by clicking [...] of the form's
RecordSet). Also, in the form's properties I have allowed for edits,
additions, and deletions. The form is not "locked". However, I am still
unable to edit or add new records with my form. When I try to type in a
field I get "This Recordset is not updateable" at the very bottom of the
screen. Are there any suggestions for this problem?

Thank you,
Diana