access DAO

[news.microsoft.com]

Hi,

Anyone here help me out with a bit of access? It's probably something
stilly but it's been a while since I wrote any vba.
I get the Error:

"cannot update. Database or object is read-only"

When I try and run the following code. It falls over on the rst.Edit.

Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset

Set dbs = CurrentDb

Set qdf = dbs.QueryDefs("SelectAbsenceEdit")

qdf.Parameters("ABSENCEID") = Me.AbsenceID.Value

Set rst = qdf.OpenRecordset()

If Not rst.EOF Then
MsgBox rst.EditMode
rst.Edit
rst!EstimatedReturnDate = Me.EstReturnDate
rst.Update
End If

Set rst = Nothing
Set qdf = Nothing
Set dbs = Nothing


Thanks,

Rick

 

[Wayne Morgan]

The first thing I would try would be going to the database window and double
clicking on the query to open it. Provide it with the parameter value when
prompted, then see if you can manually make a change to the
EstimatedReturnDate field.

 

[Immanuel Sibero]

Hi Rick,

Your recordset is based on a query. Is the query updateable?

Immanuel Sibero

 

[news.microsoft.com]

Arghh!! no it isn't. Just tried Wanye's suggestion above and I can't update
it.

What do I need to do to change this?

Thanks,

Rick

 

[Wayne Morgan]

Post the SQL of your query and we'll have a look.

 

[news.microsoft.com]

Below is the SQL.

Thanks

SELECT a.id, a.hri_number, a.date, a.time, a.estimatedReturnDate,
a.operatorID, a.managerID, a.workRelated, a.longTermDisability,
a.upperLimbDisorder, a.neckOrBackPain, a.stressAnxietyDepression, i.surname,
i.known_as, c.companyName, a.ReasonForAbsense
FROM absence AS a, individual AS i, company AS c
WHERE a.hri_number=i.hri_number
And i.idcompany=c.id
And a.id=ABSENCEID;

 

[Wayne Morgan]

The first thing I see is that there aren't any joins (links) between the
tables. To have associated records in the other tables, they need to be
linked on a common field so that Access knows which records in one table
goes with which record in the other table(s).

For example, your Indiviual table should have a unique id field for each
individual. Your Absence table should have an IndividualID field that
corelates to the ID field in the Individual table so that you can determine
which individual was absent.

--
Wayne Morgan
MS Access MVP

Below is the SQL.

Thanks

SELECT a.id, a.hri_number, a.date, a.time, a.estimatedReturnDate,
a.operatorID, a.managerID, a.workRelated, a.longTermDisability,
a.upperLimbDisorder, a.neckOrBackPain, a.stressAnxietyDepression, i.surname,
i.known_as, c.companyName, a.ReasonForAbsense
FROM absence AS a, individual AS i, company AS c
WHERE a.hri_number=i.hri_number
And i.idcompany=c.id
And a.id=ABSENCEID;

 

[news.microsoft.com]

Hi,

The table joins are there

WHERE a.hri_number=i.hri_number
And i.idcompany=c.id

just not using the INNER OUTER join method Access employs. I tend to write
the sql myself as I find it easier to understand when looking back on it.
In all other databases the joining like this is fine, maybe not so in Access
though?

Rick

 

[Wayne Morgan]

Ok, I built your tables using the fields in the query then created a query
using your SQL (copied and pasted). The query was not updateable. I then
changed it to this (below) and it was updateable.

SELECT a.ID, a.Hri_Number, a.Date, a.Time, a.EstimatedReturnDate,
a.OperatorID, a.ManagerID, a.WorkRelated, a.LongTermDisability,
a.upperLimbDisorder, a.NeckOrBackPain, a.StressAnxietyDepression, i.Surname,
i.Known_As AS Expr1, c.CompanyName, a.ReasonForAbsense
FROM (absence AS a INNER JOIN individual AS i ON a.Hri_Number =
i.Hri_Number) INNER JOIN company AS c ON i.IdCompany = c.ID
WHERE (((a.ID)=[ABSENCEID]) AND ((a.Hri_Number)=.[hri_number]) AND
((i.IdCompany)=[c].[id]));


I noticed a possible spelling problem (Absense and Absence). Also, is
ABSENCEID in the WHERE clause a parameter, I took it to be. If so, is it
coming from a form or do you just type it in when prompted?

--
Wayne Morgan
MS Access MVP

Below is the SQL.

Thanks

SELECT a.id, a.hri_number, a.date, a.time, a.estimatedReturnDate,
a.operatorID, a.managerID, a.workRelated, a.longTermDisability,
a.upperLimbDisorder, a.neckOrBackPain, a.stressAnxietyDepression, i.surname,
i.known_as, c.companyName, a.ReasonForAbsense
FROM absence AS a, individual AS i, company AS c
WHERE a.hri_number=i.hri_number
And i.idcompany=c.id
And a.id=ABSENCEID;

 

[Douglas J. Steele]

Access is very fussy about when queries are updatable and when they're not.

From the Help file (under "When can I update data from a query?")

Type of query:
Query based on tables with a one-to-many relationship

Updatable?
Usually: see below

Type of field
Join field from the "one" side
Solution
Enable cascading updates between the two tables.

Type of field
New records, if the "many" side join field doesn't appear in the datasheet
Solution
Add the join field from the "many" side to your query to allow adding new
records.

Type of field
Join field from the "many" side, after you've updated data on the "one" side
Solution
Save the record; then you'll be able to make changes to the "many" side join
field
..
Type of field
Blank field from the table on the "one" side of a one-to-many relationship
where an outer join exists
Solution
Enter values in fields from the table on the "many" side, but only if the
joined field from the "one" side contains a value for that record.

Type of query:
Query based on three or more tables in which there is a many-to-one-to-many
relationship.

Updatable?
No

Solution:
Though you can't update the data in the query directly, you can update the
data in a form based on the query if the form's RecordsetType property is
set to Dynaset (Inconsistent Updates).

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

Hi,

The table joins are there

WHERE a.hri_number=i.hri_number
And i.idcompany=c.id

just not using the INNER OUTER join method Access employs. I tend to write
the sql myself as I find it easier to understand when looking back on it.
In all other databases the joining like this is fine, maybe not so in Access
though?

Rick