updateable query

[Bill]

Hi All

I'm having a time with a form that I need to be able to add new records to.
Here's the sinario.

Form name: frmMyEmployeeMilestones
Record Source: A query named qryMyEmpMilestones
Default View: Single Form
Allow Form View: Yes
Allow Datasheet: Yes
Allow PivotTable: Yes
Allow PivotChart: Yes
Allow Edits: Yes
Allow Deletions: Yes
Allow Additions: Yes
Allow Entry: Yes
Recordset Type: Dynaset
Record Locks: No Locks
Scroll Bars: Neither
Record Selector: Yes
Navigation Buttons: Yes

The query (qryMyEmpMilestones) is as follows
SELECT EmpMilestones.MilestoneID, EmpMilestones.EmployeeAlias,
EmpMilestones.Year, EmpMilestones.Milestones, Employee.Department,
Department.DepartmentManager
FROM (EmpMilestones INNER JOIN Employee ON EmpMilestones.EmployeeAlias =
Employee.Alias) LEFT JOIN Department ON Employee.Department =
Department.Dept
ORDER BY EmpMilestones.EmployeeAlias;

When I run this form I get the nav menu at the bottom that allows me to
navigate through the resultset which in this case contains 16 records. But
it doesn't let me add a new record. That button is greyed out. So I added a
button and on the click event I created a sub to add a new record which does
allow me to insert a new one but it seem very much over kill. At one point I
was getting an error saying that I needed to use an Updateable Query.

So, I'm not what I'm missing here to make this work. Any help or pointers
from you all would be very much appreciated.

Bill

 

[boblarson]

See if this helps at all:
http://allenbrowne.com/ser-61.html

--
Bob Larson
Access MVP
Access World Forums Administrator
Utter Access VIP

Tutorials at http://www.btabdevelopment.com

__________________________________

 

[Golfinray]

Make sure that the table or query that is the source of the form is editable
(has the arrow and asterisk at the bottom.) If they are not editable, your
form won't be either.

 

[Bill]

I'm not sure I see the arrow or asterisk that you speak of. Were exactly
should I see this??

 

[Golfinray]

You should see it at the bottom of your table, query, and form. If you don't,
your form will not be editable. Some types of queries are not editable, such
as union queries. Allen Browne I think has an article about that at
www.allenbrowne.com

 

[boblarson]

Allen Browne I think has an article about that at
www.allenbrowne.com

That's the link I gave (direct to the
article):http://allenbrowne.com/ser-61.html

--
Bob Larson
Access MVP
Access World Forums Administrator
Utter Access VIP

Tutorials at http://www.btabdevelopment.com

__________________________________

 

[Bill - ESAI]

Thank you both. It seems this is likely my issue. I'll probably have many
more questions before I get this sorted so I hope you two hang out here
regularily.

Bill

 

[Bill - ESAI]

I know what I don't see but I don't know what should see. Any chance you
could attach or send me a screen shot of this arrow or asterisk your talking
about??

My email used in this group actually works.

Bill

 

[Bill - ESAI]

OK, I'm giving up and asking for more help

Here my table layouts. I didn't design it and it used heavily else where to
redesigning is out of the question at this point.

Table: Employee
EmployeeID - Autonumber
FirstName - text
LastName - text
Alias - text
Department - text
etc....

Table Department
DepartmentID - Autonumber
EmployeeID - Number
Dept - text

Table: EmpMilestones
MilestoneID - Autonumber
EmployeeID - Number
DepartmentID - Number
EmployeeAlias - text
Milestone - Memo

And the query that is currently not editable is:
SELECT EM.EmployeeAlias, EM.Year, EM.Milestones, E.Department,
D.DepartmentManager
FROM (Employee AS E INNER JOIN EmpMilestones AS EM ON E.Alias =
EM.EmployeeAlias) INNER JOIN Department AS D ON E.Department = D.Dept
ORDER BY EM.EmployeeAlias;

So, my question is, Have you any ideas as to how to re-write this query so
that it's editable?

Bill