|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
September 3rd, 2009, 04:07 PM
posted to microsoft.public.access.gettingstarted
|
|||
|
|||
Update table script
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 
|
|
#2
September 3rd, 2009, 04:58 PM
posted to microsoft.public.access.gettingstarted
|
|||
|
|||
|
Update table script
"doesn't work" Precisely how doesn't it work?
-- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Bre-x" wrote: 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
|
|
#3
September 3rd, 2009, 05:13 PM
posted to microsoft.public.access.gettingstarted
|
|||
|
|||
|
Update table script
Hi,
When I run the vba code, I get a msg: "Operation must use and updateable query" "Jerry Whittle" wrote in message ... "doesn't work" Precisely how doesn't it work? -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
|
|
#4
September 3rd, 2009, 06:00 PM
posted to microsoft.public.access.gettingstarted
|
|||
|
|||
|
Update table script
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 wrote: 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
|
|
#5
September 3rd, 2009, 06:20 PM
posted to microsoft.public.access.gettingstarted
|
|||
|
|||
|
Update table script
Thank you
DoCmd.RunSQL "UPDATE print_mainlines SET C1= '" & DLookup("[pt_l1]", "print_lines", "[L1]=1") & "' WHERE fid=1" "John Spencer" wrote in message ... 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 wrote: 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
|
|
#6
September 3rd, 2009, 06:39 PM
posted to microsoft.public.access.gettingstarted
|
|||
|
|||
|
Update table script
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!!!
|
|
#7
September 3rd, 2009, 10:05 PM
posted to microsoft.public.access.gettingstarted
|
|||
|
|||
|
Update table script
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 Bre-x wrote: 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!!!
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
