I have a local table: tbl_Emp_Rate
Fields:
Emp_No
Emp_Rate

I have a Pass-Through query: qry_Emp_Info
Fields:
Emp_No
Emp_Name

I would like to build a query -> subform -> form that will allow a user to find the employees name or number and update the rate. Sounds simple but...

The pass-through query is not updatable soooooo the entire query is not updatable, even the local table.


SELECT
tbl_Emp_Rates.Emp_No
, qry_Emp_Info.Emp_Name
, tbl_Emp_Rates.Emp_Rate

FROM
tbl_Emp_Rates
INNER JOIN
qry_Emp_Info
ON (tbl_Emp_Rates.Emp_No = qry_Emp_Info.EMP_NO);


I am only updating [tbl_Emp_Rates.Emp_Rate], yet because the PT query is not updatable and it is used in a query then nothing in that query is updatable.

My only solution is to copy the entire qry_Emp_Info to a local able but! that duplicates records which could be in the thousands.

My question is: Is there a way given the table and query above to let a user select an employee name and enter a rate? I have looked around and found that using a PT query makes the query unble to update (hmm) now I am looking for a solution or alternative.

Thank you any help would be appreciated.

a strange way to do this - heres some alternatives

a) have a continuous form showing all the employees, then you can change the rate directly

b) have a single form - find the employee using the binoculars. and change the rate directly

c) have a single form - use an unbound combo box to select the employee - jump to the correct employee, and change the rate directly

d) have an unbound form - use the combo box to select the employee
enter the rate required
run an update query directly

currentdb.execute "update employees set payrate = " & newrate & " where employeeid = " & selectedemployee

The idea is to have a spreadsheet (like) view so the user can go down the list and enter the numbers. I like(d) the idea about a continuous form, this is closest to a datasheet view which is what I really want.

However (and there's almost always one of those) I can't get it to work. If I have a Text Box (txt_Emp_Rate) and set the control to unbound then whatever I type in the box appears in ALL of the rows. If I bind the contol to [Emp_Rate] then I can't change it.

Getting closer

its because you are basing the form on your non updateable query

can you post your table structure for the employees and employeerates tables, then we can suggest the correct query

You could use the above with the Emp_No being the primary key in both tables. Note that the qry_Emp_Info is actually a PT query.

I really appreciate it.

Here is an idea, is there a way to, using datasheet view to bind the contol (txt_Emp_Rate) to [Emp_Rate] then I can't change it which I realize.

But somehow capture the data entered?????

And preform an update query?

I would really, really like to stay away from a subform that has a single field on it, it is a matter of usability.

Any ideas on how to enter this information?