Unable to update fields in a form

[mslennb]

Greetings,
I am trying to create a form to update two fields (VRB-Status, and VRB-Notes) but the fields are not updatable. The form is based on a query (qry_VacantPositionsDetails) that is based on two other queries. The VacantPositionsDetails query is designed to only display those position numbers that are currently vacant. Due to turnover, a position number can become vacant multiple time. I only need to update the status of the most recent date the position is vacant hence the maxdate (current vacancies). The VRB-Status fields are in the tbl_EmpDetails table. Below are the codes for the VacantPositionsDetails query. The tables are: tbl_Position (unique position #s); tbl_Personnel (Emp. personnel ID, Name, etc); tbl_EmpHistory (main form); and tbl_EmpDetails (subform).
I am hoping someone can guide me in the right direction to being able to update the form.

Thanks.

SELECT qry_VacantPositions.PosNumID, qry_VacantPositions.PosStatusID, qry_VacantPositions.MaxOfEffectiveDate, [FirstName] & " " & [LastName] AS Name, qry_EmpHistory.PersonnelID, qry_EmpHistory.IndexTitle, qry_EmpHistory.JobClassTitle, qry_EmpHistory.WY, qry_EmpHistory.[F/P], qry_EmpHistory.Grade, qry_EmpHistory.IndexCodeID, qry_EmpHistory.VRBStatusID, qry_EmpHistory.VRBStatus, qry_EmpHistory.VRBNotes
FROM qry_VacantPositions INNER JOIN qry_EmpHistory ON (qry_VacantPositions.MaxOfEffectiveDate=qry_EmpHistory.EffectiveDate) AND (qry_VacantPositions.PosNumID=qry_EmpHistory.PosNumID);

 

[PHV]

Have a look here:

http://msdn.microsoft.com/archive/en-us/dnaraccess/html/msdn_harness.asp

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TheAceMan1]

How are ya mslennb . . .

To tell if the returned recordset of a query or SQL is [blue]uneditable[/blue], have a look at the [blue]AddNew[/blue] navigation button

, if it has that [blue]disabled look[/blue]

then the recordset is uneditable!

If you cannot edit the data in a query, this list (courtesy by [blue]Allen Browne[/blue]) may help you identify why it is not updatable:
[ol][li]It has a [blue]GROUP BY[/blue] clause. A Totals query is always read-only.[/li]
[li]It has a [blue]TRANSFORM[/blue] clause. A Crosstab query is always read-only.[/li]
[li]It uses [blue]First(), Sum(), Max(), Count(), etc[/blue]. in the SELECT clause. Queries that aggregate records are read-only.[/li]
[li]It contains a [blue]DISTINCT[/blue] predicate. Set Unique Values to No in the query's Properties.[/li]
[li]It involves a [blue]UNION[/blue]. Union queries are always read-only.[/li]
[li]It has a [blue]subquery[/blue] in the SELECT clause. Uncheck the Show box under your subquery, or use a domain aggregation function instead.[/li]
[li]It uses [blue]JOINs of different directions on multiple tables[/blue] in the FROM clause. Remove some tables.[/li]
[li]The [blue]fields in a JOIN are not indexed correctly[/blue]: there is [blue]no primary key or unique index[/blue] on the JOINed fields.[/li]
[li]The query's [blue]Recordset Type[/blue] property is [blue]Snapshot[/blue]. Set Recordset Type to "Dynaset" in the query's Properties.[/li]
[li]The query is [blue]based on another query that is read-only[/blue] (stacked query.)[/li]
[li]Your [blue]permissions are read-only[/blue] (Access security.)[/li]
[li]The database is [blue]opened read-only[/blue], or the file attributes are read-only, or the database is on read-only media (e.g. CD-ROM, network drive without write privileges.)[/li][/ol]

See Ya! . . . . . .

Be sure to see thread181-473997

 

[mslennb]

Thanks for the information. I had read the article, but the problem I am having is how to fix it and to make it work.

 

[GingerR]

Identify which of the issues (1-12) is affecting you from the list above, then correct it.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[mslennb]

I reviewed each one the issues and the problem seems to be #3, Max Date.
To fix the problem, I created two queries.
1. Qry_MaxDate using
EmpHistory Table and selecting
EmpHistoryID and
EffectiveDate (Max)

The second query from EmpDetails Table
EmpHistoryID
PosNumID
VRBStatus
VRBHistory
(The relationship between the Tables EmpHistory and EmpDetails is the EmpHistoryID - one to many)

I need to update the VRBStatus and VRBHistory fields for a position number for the most recent Date that a position number was vacant.

The fields are still not updatable.
Any help would be appreciated. Thanks.