Recordset not updatable, why ?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a query that populates a form and refers to 2 tables, TablePerson and
TableLocations but because the two are linked in 1 query, on the form it says
'recordset not updatable' in the status bar when trying to edit a persons
details... why ???

EG,
Name, telephone extention number, location etc are all stored on TablePerson
The direct dial prefix number for all the various locations are stored in
TableLocations.

Persons Ext Nº = 1234
Direct Dial prefix for their location is 01234 56

So direct dial for THIS person = 01234 561234 (location direct dial prefix &
ext number, stored in a locked text box)

Anyone able to help me get my form so i CAN update the records ??
 
Widemonk,

Search the forum and Google "non-updateable queries" for a full discussion.
There are many reasons that cause this, but a common reason is including the
primary key from the one side of a one-to-many relationship in the recordset.


If that's not your problem, please post the SQL.

Sprinks
 
Ive tried with primary keys, without primary keys. Googled the phrase but
nothing has helped me.

As requested, this is the SQL of the query but to be honest, I never use
SQL. I build all my queries in Design mode.

Refering back to my initial post, location is actually 'site'.

Thanks very much

SELECT TablePeople.Surname, TablePeople.Forename, TablePeople.Site,
TablePeople.Department, TablePeople.SubDept, TablePeople.JobDesc,
TablePeople.TelExt, TablePeople.Mobile, TablePeople.Add1, TablePeople.Add2,
TablePeople.Add3, TablePeople.Town, TablePeople.County, TablePeople.PostCode,
TableSites.SiteDirectDial, TablePeople.ID, TableSites.SiteAdd1,
TableSites.SiteAdd2, TableSites.SiteTown, TableSites.SiteCounty,
TableSites.SitePostCode, TableSites.SiteTel, TableSites.SiteFax
FROM (TablePeople INNER JOIN TableSites ON TablePeople.Site =
TableSites.Site) LEFT JOIN TableSubDepts ON TablePeople.SubDept =
TableSubDepts.[Sub-Dept]
ORDER BY TablePeople.Surname, TablePeople.Forename;
 
I'm not sure why you have the join to table TableSubDepts, since no fields
are selected from it, so I would try removing it, although I duplicated a
subset of your tables here, and the query was updateable with or without the
Left Join. Do you have any other relationships defined between any of the
fields in the recordset?

Good luck.

Sprinks

Widemonk said:
Ive tried with primary keys, without primary keys. Googled the phrase but
nothing has helped me.

As requested, this is the SQL of the query but to be honest, I never use
SQL. I build all my queries in Design mode.

Refering back to my initial post, location is actually 'site'.

Thanks very much

SELECT TablePeople.Surname, TablePeople.Forename, TablePeople.Site,
TablePeople.Department, TablePeople.SubDept, TablePeople.JobDesc,
TablePeople.TelExt, TablePeople.Mobile, TablePeople.Add1, TablePeople.Add2,
TablePeople.Add3, TablePeople.Town, TablePeople.County, TablePeople.PostCode,
TableSites.SiteDirectDial, TablePeople.ID, TableSites.SiteAdd1,
TableSites.SiteAdd2, TableSites.SiteTown, TableSites.SiteCounty,
TableSites.SitePostCode, TableSites.SiteTel, TableSites.SiteFax
FROM (TablePeople INNER JOIN TableSites ON TablePeople.Site =
TableSites.Site) LEFT JOIN TableSubDepts ON TablePeople.SubDept =
TableSubDepts.[Sub-Dept]
ORDER BY TablePeople.Surname, TablePeople.Forename;

Sprinks said:
Widemonk,

Search the forum and Google "non-updateable queries" for a full discussion.
There are many reasons that cause this, but a common reason is including the
primary key from the one side of a one-to-many relationship in the recordset.


If that's not your problem, please post the SQL.

Sprinks
Click to expand...
Click to expand...
 
Certain people, depending on their job description shouldnt really be
telephoned directly. The telephone system is set up with a ring-round so on
the form for their details, while it shows their extention number, the direct
dial should be the ringround (stored on TableSubDepts)

Lets try that again...

SELECT TablePeople.Surname, TablePeople.Forename, TablePeople.Site,
TablePeople.Department, TablePeople.SubDept, TablePeople.JobDesc,
TablePeople.TelExt, TablePeople.Mobile, TablePeople.Add1, TablePeople.Add2,
TablePeople.Add3, TablePeople.Town, TablePeople.County, TablePeople.PostCode,
TableSites.SiteDirectDial, TablePeople.ID, TableSites.SiteAdd1,
TableSites.SiteAdd2, TableSites.SiteTown, TableSites.SiteCounty,
TableSites.SitePostCode, TableSites.SiteTel, TableSites.SiteFax,
TableSubDepts.RingRound
FROM (TablePeople INNER JOIN TableSites ON TablePeople.Site =
TableSites.Site) LEFT JOIN TableSubDepts ON TablePeople.SubDept =
TableSubDepts.[Sub-Dept]
ORDER BY TablePeople.Surname, TablePeople.Forename;
 
Widemonk,

I can't specifically determine why this query is non-updateable, however,
the more complex the query, the more likely it will be non-updateable.

Presumably, this form is primarily meant for editing information for the
people, and that each person's site data is being presented for informational
purposes. A workaround in that case would be to remove the TableSites table
from your query, and include these fields in the RowSource of a combo box
bound to TablePeople.Site.

You could then display the site fields in Unbound textboxes using the combo
box' Column property (column index starting with 0):

' Setting the ControlSource to the following displays the second column
= YourComboBox.Column(1)

Should you wish to be able to edit the site data, you could always provide a
command button that opens another form to the proper record.

Hope that helps.

Sprinks
 
Back
Top