Can't update data in my form 1

[ajaeger]

I have a form (frmTime) in an Employee Time database that displays all the days of a particular month (the records) and a few fields (Columns) for them to track where they spent their time that day.

Upon opening frmTime, the following runs:

strSQL = "SELECT tblTimeLog.TimeID, tblEmployees.EmployeeID, tblEmployees.FirstName, tblEmployees.LastName, tblTimeLog.DateDay, tblTimeLog.[310Hours], tblTimeLog.[310Minutes], tblTimeLog.[320Hours], tblTimeLog.[320Minutes]
FROM (tblTimeLog INNER JOIN tblEmployees ON tblTimeLog.EmployeeID=tblEmployees.EmployeeID) INNER JOIN tblDates ON tblTimeLog.DateDay=tblDates.DateDay
WHERE ((tblEmployees.EmployeeID)=1) And (12=Month(tblTimeLog.DateDay)) And (2012=Year(tblTimeLog.DateDay))"

Forms!frmTime.RecordSource = strSQL

The form opens the correct records, however, I can't enter/update the values in the form - 310Hours, 310Minutes, etc...

On the Property Sheet for the form, Allow Additions, Allows Edits are both set to Yes. What else should I be looking for? When I use the above code directly in a query, I can't update that query either.

Anna Jaeger
iMIS Database Support

http://www.axcedesolutions.com

 

[MacroScope]

Why don't you create a query that returns what you want to see, and then copy the SQL from it if you don't want to use it directly?

 

[dhookom]

Are these primary key fields:
tblEmployees.EmployeeID
tblDates.DateDay

Duane
Hook'D on Access
MS Access MVP

 

[ajaeger]

The query works if I set it up directly as a query. However, I still can't update the data in the query, just as I can't update the data in the form when the same SQL is the record source.

Primary Key fields are:
tblDates.DateID
tblTimeLog.TimeID
tblEmployees.EmployeeID


Anna Jaeger
iMIS Database Support

http://www.axcedesolutions.com

 

[dhookom]

Can tblDates.DateDay by a primary or unique index?
Why is tblDates even in the query?

Duane
Hook'D on Access
MS Access MVP

 

[ajaeger]

I had abbreviated the fields that are in the query when I posted. There was one field from tblDates that I need for formatting the form later. For now I've deleted it and the table. The query is now updateable. So, if i want to update the query, must I only link on primary or unique indexes?

Anna Jaeger
iMIS Database Support

http://www.axcedesolutions.com

 

[dhookom]

It has been my experience that Access queries enforce the primary/foreign keys in queries in order to be able to edit the data.

If tblDates.DateDay isn't unique then records from tblTimeLog could be joined to multiple records in tblDates.

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

Have a look here:

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

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