Update table script

[Bre-x]

Hi,

Why this sql script doesnt work?

'UPDATE COLUMNS
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE print_mainlines SET C1= (SELECT pt_l1 FROM
print_lines WHERE L1=1) WHERE fid=1"
DoCmd.SetWarnings True

Thank you all,

Bre-x

 

[Jerry Whittle]

"doesn't work" Precisely how doesn't it work?

 

[Bre-x]

Hi,

When I run the vba code, I get a msg: "Operation must use and updateable
query"

 

[John Spencer]

Since, in theory the subquery could return more than one record (even if you
know it will return only one record - the database engine does not), Access
will not allow this construct.

Try using one of the aggregate functions DMax or DLookup should both work.

DoCmd.RunSQL
"UPDATE print_mainlines" & _
" SET C1= DMax(""pt_l1"",""print_lines"",""L1=1"")" & _
" WHERE fid=1"

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

 

[Bre-x]

Thank you

DoCmd.RunSQL "UPDATE print_mainlines SET C1= '" & DLookup("[pt_l1]",
"print_lines", "[L1]=1") & "' WHERE fid=1"

 

[Bre-x]

Thank you for answering my post
I have another question,

I need to run the script 19 times, example

'UPDATE COLUMNS
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE print_mainlines SET C1= '" & DLookup("[pt_l1]",
"print_lines", "[L1]=1") & "' WHERE fid=1"
DoCmd.RunSQL "UPDATE print_mainlines SET C1= '" & DLookup("[pt_l2]",
"print_lines", "[L1]=1") & "' WHERE fid=2"
DoCmd.SetWarnings True


how do I change teh pt_11 to pt_l2 and the fid=1 to fid=2?

Thank you!!!

 

[John Spencer]

STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the way you
expect.

Dim strSQL as String

For iCount = 1 to 19
strSQL = "UPDATE Print_Mainlines SET C1="
& DLookup("pt_l" & iCount,"Print_Lines","L1=1")
& "WHERE Fid=" & iCount
DoCmd.RunSQL strSQL
Next ICount

Easy way to check that the correct strings are being generated is to replace
DoCmd.RunSQL strSQL
with
Debug.Print strSQL

Then you should see valid SQL strings in the immediate window. If it all
looks good then remove the Debug and use the DoCmd.RunSQL line.

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