Cannot edit records on a form populated by a record set

[codequirks]

Hi there

I have a relatively simple form which is populated by a record source. The record source is an SQL statement defined in code in the Open event (see code below). The form loads all the records nicely but I cannot edit any of the fields!!

All the fields are enabled and not locked, the form allows edits, data-entry, has recordset type of dynaset and no record locks. Does anyone know why I cannot edit any of these fields???

Ideally I would like the user to be able to edit the fields and then click a save button to send the updates. I can handle the save bit but it is completely useless if the user cannot edit the data!!!!

Thanks for your help


Open Event..

'Define Variables
Dim StrSQL As String

'Set String
StrSQL = "SELECT Customer.ID, Customer.Name, Customer.Problem FROM ActiveCustomers;"

'Set the record source to be the SQL query
Me.RecordSource = StrSQL

 

[Remou]

There are a few things to check:

1. Can you edit any tables?
2. Can you edit a query built using the same sql?
3. Are the relevant controls locked/disabled?
4. Does the form allow edit?

 

[codequirks]

Remou - as requested

1. Yes I can edit the tables
2. No it is not possible to edit a query using the same SQL as this is a select query
3. The controls are not locked and not disabled
4. The form allows edits but not additions or deletions

I agree that the problem might be that the form is using a SELECT statement to populate the record set, I just want to get the data on the page though and then handle any changes later.

 

[Remou]

2. No it is not possible to edit a query using the same SQL as this is a select query

I wonder if we are talking about the same thing? When you paste your Select statement into SQL view of the query design window and open the query, can you edit the fields?

 

[fneily]

On another problem, you have a table that look like this:

ActiveCustomers
CustomerID
Name
Problem

This is not normalized. See:
Fundamentals of Relational Database Design

http://r937.com/relational.html

Name is NOT a good fieldname. Access uses the word name. Also, it's not atomized, it should be FirstName, LastName. Violates first normal form.
It seems you have a many-to-many relationship. One customer can have many problems, and a certain problem can belong to many customers.

So you should have:
tblCustomers
CustomerID Primary Key
FirstName
LastName
other customer info

tblProblem
ProblemID Primary Key
Description

tblCustProb
CPID Primary Key
CustomerID Foreign Key
ProblemID Foreign Key
othe COMMON fields such as
DateOfProblem

Since a "user" is using this database, make sure it's constructed correctly.

 

[JoeAtWork]

It doesn't matter that it's a SELECT statement - that's the only kind that you can put in a form's recordsource. Access figures out how to do the update. However, sometimes it's read-only for various reasons. If it's a complex query (comprises several tables, or based on other queries, etc.).

What is "ActiveCustomers" - that sounds like a query or View (if SQL Server) rather than a table? If this is a linked table, you may need to include the primary key in the query to be able to update.

Joe Schwarz
Custom Software Developer

http://www.plandev.net

 

[codequirks]

Ok, think we might be getting somewhere

Remou - That is what i did (paste the SQL into the query design view and run it, I could not edit the fields)

Fneily - those are not the actual field names, I have simplified the code for brevity and I understand your comments about relational design but that is not appropriate in this case

JoeAtWork - the SQL select statement runs directly off an access table. It is however much more complicated than that shown (i.e. many more fields and two left joins). So are we saying that if MS Access populates a form with a complicated SQL Query it will prevent the user from editing the form!!!? I just want the user to be allowed to edit the form - I will deal with the update myself when they click the save button!

 

[Remou]

It is possible to use a complex query, but more care must be taken in building it. For example, some or all of the tables must have unique indexes, key fields must be included and so on. For the most part, you will often find that you have included tables in your query that it is not necessary to include for data displayed on a form, because a combo will display the data better.

 

[fneily]

It is however much more complicated than that shown (i.e. many more fields and two left joins)." If we can't see the actual stuff, we can't help.

Sounds like a non-updatable query.
Using Updatable Queries White Paper Available in Download Center

http://support.microsoft.com/kb/148581

When can I update data from a query?

http://msdn.microsoft.com/en-us/library/aa198446.aspx