Results 1 to 2 of 2
Thread: Recordset not updateable (2000)
2004-06-10, 15:52 #1
- Join Date
- Sep 2002
- London, England
- Thanked 0 Times in 0 Posts
Recordset not updateable (2000)
Any idea why the following query is not updateable?
SELECT tbl_LHR_SalesHistory.*, qry_TillOperators.OpName
FROM tbl_LHR_SalesHistory LEFT JOIN qry_TillOperators ON tbl_LHR_SalesHistory.sales_person = qry_TillOperators.SPNumber;
tbl_LHR_SalesHistory is a table. I am displaying all the fields in a form, hence the *.
qry_TillOperators is a query that enables me to link the table tbl_TillOperators to the table tbl_LHR_SalesHistory.
The linking fields between the tables are both text, but the field in tbl_LHR_SalesHistory has leading zeros and the field in tbl_TillOperators does not, so I have used qry_TillOperators, which adds the leading zeros to the linking field, rather than tbl_TillOperators in the problem query.
qry_TillOperators is blow:
SELECT [Last Name] & ", " & [First name] AS OpName, tbl_TillOperators.[Employee no], tbl_TillOperators.[Cashier number], Right("00000000000" & [Cashier number],11) AS SPNumber
If it is the Right("00000000000" & [Cashier number],11) expression on the linked field that is causing the trouble, is there another way to connect the tables and still have an updateable query?
2004-06-10, 19:55 #2
- Join Date
- Mar 2002
- Thanked 29 Times in 29 Posts
Re: Recordset not updateable (2000)
You must link directly on the Cashier number field, and there must be a unique index on this field. To make direct linking possible:
Option 1: change the sales_person field in tbl_LHR_SalesHistory and the Cashier number field in tbl_TillOperators table to number fields.
- or -
Option 2: update the Cashier number field in the tbl_TillOperators table to include leading zeros.
This is probably not what you wanted to hear, sorry.