Form based on a query but not allowing any edits 2

[Powerhouse123]

Hi,

Merry Christmas!

Scenario:

Users Table: Training 1 Table Training 2 Table

ID ID ID
FirstName FirstName First Name
Surname Surname Surname
Address Attended Attended
Tel Date Attending Date Attending
Training1
Training1Date
Training2
Training2Date

I have created a query to bring through the Date Attending from Training 1 and 2 Tables to the appropriate date columns in the Users Table if the First Name and Surname match correctly.

This part is working perfectly. What I want to be able to do is edit any of the detail. When I change the Recordset Type field property to Dynaset (Inconsistency) I am able to edit everything in the resulting datasheet view of the query.

The problem comes when I recordsource the query to a form...The data comes through fine but the I am unable to edit anything. It says at the bottom that "this recordset is not updateable"

Do you have any ideas?

Many thanks and have a good festive period.

Kind Regards.

 

[Remou]

Have you got a unique index on the appropriate tables? Have you seen:
msdn.microsoft.com/archive/en-us/dnaraccess/html/msdn_harness.asp

 

[MajP]

My first question is why do you have two tables (training tables 1 and 2) with the exact same information? My assumption is that these tables represent two different types of training. If that is the case then to normalize your database combine the two tables and add one field "trainingType". The second question is what are your primary keys? I assume that it is a combined key of first and last name. If this is the case it is not a very good idea unless your data set is so small that you will absolutely never have two people with the same first and last name. If names change (ie married female) you also need to ensure that you have cascade updates set.

If you fix these problems it will simplify creating an updateable query. I do not have the link, but someone after me will likely post a link to an article on "Harnessing the Power of Updateable Queries". Depending on how you build your query you can make a nonupdateable query. Witout seeing your sql it is hard to tell the reason, but my guess is that First and last name are not a composite primary key in your person table. This will allow you to create a link but not update the query.

 

[MajP]

I was typing while Remou was posting, but that is the link.