Update Query Error

[Amy]

I have a program that provides a database behind the scenes to generate more
complex queries and reports that the program itself won't allow. I have a
field that is not allowed to be used in a merge which I need to do. I want to
copy the data that is stored in one field to another field that I can use to
merge. Since I can't simply copy and paste the data I am assuming update
query is the only way? Here is the SQL for the update query that is giving me
the must be an updateable error. There is not data in every record. Any
suggestions?

UPDATE tblClients INNER JOIN qryCVPasswords ON tblClients.ID =
qryCVPasswords.ID SET tblClients.AccountingCode = [qryCVPasswords].[PRPwd];

I also tried simply:

UPDATE tblClients
SET tblClients.AccountingCode = PRPwd

And also:

UPDATE tblClients
SET "AccountingCode" = "PRPwd"

I get errors each time.

 

[John Spencer (MVP)]

Perhaps you can use

Update tblClients
SET AccountingCode = DLookup("PRPWd","qryCvPassWords","ID='" & [ID] & "'")

That assumes that ID is a text field. If Id is a number field remove the
apostrophes.

Update tblClients
SET AccountingCode = DLookup("PRPWd","qryCvPassWords","ID=" & [ID])

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Jerry Whittle]

Is AccountingCode a text or number field?

Would AccountingCode happen to have one of those evil lookups to another
table?

 

[Amy]

They are both text fields. The only difference I can see is the the field
being copied has a limit of 10 whereas the copy to field has 255. No lookups,
the accounting code is just a field the programmer gave to allow us to keep
data. The prpwd field is used in another program...

Is AccountingCode a text or number field?

Would AccountingCode happen to have one of those evil lookups to another
table?
--
Jerry Whittle - MS Access MVP 2007 - 2009
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder

I have a program that provides a database behind the scenes to generate more
complex queries and reports that the program itself won't allow. I have a
field that is not allowed to be used in a merge which I need to do. I want to
copy the data that is stored in one field to another field that I can use to
merge. Since I can't simply copy and paste the data I am assuming update
query is the only way? Here is the SQL for the update query that is giving me
the must be an updateable error. There is not data in every record. Any
suggestions?

UPDATE tblClients INNER JOIN qryCVPasswords ON tblClients.ID =
qryCVPasswords.ID SET tblClients.AccountingCode = [qryCVPasswords].[PRPwd];

I also tried simply:

UPDATE tblClients
SET tblClients.AccountingCode = PRPwd

And also:

UPDATE tblClients
SET "AccountingCode" = "PRPwd"

I get errors each time.

 

[Amy]

Thanks, but no luck. Still that annoying error message. I only created the
second qry because I was grasping. I didn't think this should be so
difficult!

You know, it is not a look up but it is linked, has the little arrow and
world... Maybe that's the problem.

Perhaps you can use

Update tblClients
SET AccountingCode = DLookup("PRPWd","qryCvPassWords","ID='" & [ID] & "'")

That assumes that ID is a text field. If Id is a number field remove the
apostrophes.

Update tblClients
SET AccountingCode = DLookup("PRPWd","qryCvPassWords","ID=" & [ID])

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I have a program that provides a database behind the scenes to generate more
complex queries and reports that the program itself won't allow. I have a
field that is not allowed to be used in a merge which I need to do. I want to
copy the data that is stored in one field to another field that I can use to
merge. Since I can't simply copy and paste the data I am assuming update
query is the only way? Here is the SQL for the update query that is giving me
the must be an updateable error. There is not data in every record. Any
suggestions?

UPDATE tblClients INNER JOIN qryCVPasswords ON tblClients.ID =
qryCVPasswords.ID SET tblClients.AccountingCode = [qryCVPasswords].[PRPwd];

I also tried simply:

UPDATE tblClients
SET tblClients.AccountingCode = PRPwd

And also:

UPDATE tblClients
SET "AccountingCode" = "PRPwd"

I get errors each time.

 

[Amy]

From the company that wrote the program,

You could create, or hire a programmer to create, a custom update query in
either MS Access linked to the [program name removed] SQL database or in SQL
server Management Studio on the server that will do the update for you all at
once.

Why would we need to link it to the SQL database when they give us access to
the tables and allow queries etc in a "custom database". I can create an
append query with no problem and can copy the data in question manually
through the 'custom database'.