How do you continue a SQL DoCmd to the next line 1

[williekay]

How do you put breaks in an UPDATE SQL to continue it on the next line (it's to long for the window)?

SQL$ = "UPDATE MoldList SET Rework = IIF(Me.Scrapped=-1,DATE(),"") ; Notes = IIF(Me.Scrapped=-1,Me.Reason,"") ; [LastRepair] = '" & Me.[LastRepair] & "' ;
[DL] = '" & Me.[DL] & "' ;[DLn] = " & Me.[DLn] & " ;[DO] = '" & Me.[DO] & "' ;[DOn] = " & Me.[DOn] & " ;

---------------------------------------- break -----------------------------------------

[CL] = '" & Me.[CL] & "' ;[CLn] = " & Me.[CLn] & " ;[CO] = '" & Me.[CO] & "' ;[COn] = " & Me.[COn] & " ;

---------------------------------------- break -----------------------------------------


[BL] = '" & Me.[BL] & "' ;[BLn] = " & Me.[BLn] & " ;[BO] = '" & Me.[BO] & "' ;[BOn] = " & Me.[BOn] & " ;

---------------------------------------- break -----------------------------------------


[AL] = '" & Me.[AL] & "' ;[ALn] = " & Me.[ALn] & " ;[AO] = '" & Me.[AO] & "' ;[AOn] = " & Me.[AOn] & " ;

---------------------------------------- break -----------------------------------------


[Length] = '" & Me.[Length] & "' ;[Lengthn] = " & Me.[Lengthn] & " ;[Mold] = " & Me.[Mold] & " ;

---------------------------------------- break -----------------------------------------


[Plate] = '" & Me.[Plate] & "' ;[PlateSize] = " & Me.[PlateSize] & " ;[Type] = '" & Me.[Type] & "' ;[Location] = '" & Me.[Location] & "' ;

---------------------------------------- break -----------------------------------------


[0] = '" & Me.[0] & "' ;[00] = '" & Me.[00] & "' ;[1] = '" & Me.[1] & "' ;[2] = '" & Me.[2] & "' ;[3] = '" & Me.[3] & "' ;[4] = '" & Me.[4] & "' ;

---------------------------------------- break -----------------------------------------


[5] = '" & Me.[5] & "' ;[6] = '" & Me.[6] & "' ;[7] = '" & Me.[7] & "' ;[8] = '" & Me.[8] & "' ;[9] = '" & Me.[9] & "' ;[10] = '" & Me.[10] & "' ;[11] = '" & Me.[11] & "' ;[12] = '" & Me.[12] & "' ;

---------------------------------------- break -----------------------------------------


WHERE [Mold#] = " & [Mold#] & " "




Willie

 

[Remou]

You can use the Line Continuation Character, which is an underscore. The ampersand (&) is a good place to break:

SQL$ = "UPDATE MoldList SET Rework = IIF(Me.Scrapped=-1,DATE(),"") ; Notes = IIF(Me.Scrapped=-1,Me.Reason,"") ; [LastRepair] = '" & Me.[LastRepair] & "' ;
[DL] = '" & Me.[DL] & "' ;[DLn] = " & Me.[DLn] & " ;[DO] = '" & Me.[DO] & "' ;[DOn] = " & Me.[DOn] [red] _
& " [/red];

You will also see & _, rather than _ &
Make sure that you leave a space before the underscore.

Do not break in the middle of a piece of text. To break up text you need to end it with a quotation mark (") and restart with an ampersand and another quotation mark.

 

[williekay]

In the following code I get an error message that reads Compile Error Wrong number of arguments or invalid property assignment, do you know why? It is set to a button click in a sub form to update a linked mainform record.

DoCmd.RunSQL "UPDATE MoldList SET Rework = IIf(Forms![MoldList]![MoldHistory1].Form![SCRAPPED] = 0,NULL, Date()) , Notes = IIf(Forms![MoldList]![MoldHistory1].Form![SCRAPPED] = 0,NULL, Forms![MoldList]![MoldHistory1].Form![Reason]) ,[LastRepair] = Forms![MoldList]![MoldHistory1].Form![LastRepair] , [DL] = Forms![MoldList]![MoldHistory1].Form![DL] , [DLn] = Forms![MoldList]![MoldHistory1].Form![DLn] , [DO] = Forms![MoldList]![MoldHistory1].Form![DO] , [DOn] = Forms![MoldList]![MoldHistory1].Form![DOn] , [CL] = Forms![MoldList]![MoldHistory1].Form![CL] , [CLn] = Forms![MoldList]![MoldHistory1].Form![CLn] , [CO] = Forms![MoldList]![MoldHistory1].Form![CO] , [COn] = """ _
& Forms![MoldList]![MoldHistory1].Form![COn], [Aln] = Forms![MoldList]![MoldHistory1].Form![Aln], [AO] = Forms![MoldList]![MoldHistory1].Form![AO], [AOn] = Forms![MoldList]![MoldHistory1].Form![AOn], [Length] = Forms![MoldList]![MoldHistory1].Form![Length], [Lengthn] = "" _
& Forms![MoldList]![MoldHistory1].Form![Lengthn], [Mold] = Forms![MoldList]![MoldHistory1].Form![Mold], [Plate] = Forms![MoldList]![MoldHistory1].Form![Plate], [PlateSize] = Forms![MoldList]![MoldHistory1].Form![PlateSize], [Type] = "" _
& Forms![MoldList]![MoldHistory1].Form![Type], [Location] = Forms![MoldList]![MoldHistory1].Form![Location], [0] = Forms![MoldList]![MoldHistory1].Form![0], [00] = Forms![MoldList]![MoldHistory1].Form![00], [1] = Forms![MoldList]![MoldHistory1].Form![1], [2] = "" _
& Forms![MoldList]![MoldHistory1].Form![2], [3] = Forms![MoldList]![MoldHistory1].Form![3], [4] = Forms![MoldList]![MoldHistory1].Form![4], [5] = Forms![MoldList]![MoldHistory1].Form![5], [6] = Forms![MoldList]![MoldHistory1].Form![6], [7] = Forms![MoldList]![MoldHistory1].Form![7], [8] = "" _
& Forms![MoldList]![MoldHistory1].Form![8], [9] = Forms![MoldList]![MoldHistory1].Form![9], [10] = Forms![MoldList]![MoldHistory1].Form![10], [11] = Forms![MoldList]![MoldHistory1].Form![11], [12] = "" _
& Forms![MoldList]![MoldHistory1].Form![12]
WHERE [Mold#] = " Forms![MoldList]![MoldHistory1].Form![Mold#] & """

Willie

 

[Remou]

You need something more like this:

Dim objSubForm, strSQL
objSubForm = Forms![MoldList]![MoldHistory1].Form

'What if Scrapped is Null?
strSQL = "UPDATE MoldList SET "
If objSubForm![SCRAPPED] = 0 Then
    strSQL = strSQL & "Rework = Null, Notes = Null, "
Else
    strSQL = strSQL & "Rework = #" & Date & "# , Notes = " & objSubForm![Reason]
End If
strSQL = strSQL & ", [LastRepair] = " _
& objSubForm![LastRepair] & ", [DL] = " _
& objSubForm![DL] & ", [DLn] = " _
& objSubForm![DLn] & ", [DO] = " _
& objSubForm![DO] & ", [DOn] = " _
& objSubForm![DOn] & ", [CL] = " _
& objSubForm![CL] & ", [CLn] = " _
& objSubForm![CLn] & ", [CO] = "

strSQL = strSQL & objSubForm![CO] & ", [COn] = " _
& objSubForm![COn] & ", [Aln] = " _
& objSubForm![Aln] & ", [AO] = " _
& objSubForm![AO] & ", [AOn] = " _
& objSubForm![AOn] & ", [Length] = " _
& objSubForm![Length] & ", [Lengthn] = " _
& objSubForm![Lengthn] & ", [Mold] = " _
& objSubForm![Mold] & ", [Plate] = " _
& objSubForm![Plate] & "[PlateSize] = " _
& objSubForm![PlateSize] & ", [Type] = "

strSQL = strSQL & objSubForm![Type] & ", [Location] = " _
& objSubForm![Location] & ", [0] = " _
& objSubForm![0] & ", [00] = " _
& objSubForm![00] & ", [1] = " _
& objSubForm![1] & ", [2] = " _
& objSubForm![2] & ", [3] = " _
& objSubForm![3] & ", [4] = " _
& objSubForm![4] & ", [5] = " _
& objSubForm![5] & ", [6] = " _
& objSubForm![6] & ", [7] = "

strSQL = strSQL & objSubForm![7] & ", [8] = " _
& objSubForm![8] & ", [9] = " _
& objSubForm![9] & ", [10] = " _
& objSubForm![10] & ", [11] = " _
& objSubForm![11] & ", [12] = " _
& objSubForm![12] _
& " WHERE [Mold#] = " & objSubForm![Mold#]

Debug.Print strSQL

You will need to go through this carefully putting in hash marks (#) on either side for date fields and single quotes (')for text fields. For example, if [10] is a date field:

[tt]& objSubForm![9] & ", [10] = #" _
& objSubForm![10] & "#, [11] = " _
& objSubForm![11] & ", [12] = " _[/tt]

If [10] is a text field:

[tt]& objSubForm![9] & ", [10] = '" _
& objSubForm![10] & "', [11] = " _
& objSubForm![11] & ", [12] = " _[/tt]

Use the Debug.Print at the end to cust and paste the SQL into SQL View of the query design window. This will allow you to see the mistakes more clearly. Comment it out when all is working as you wish.

I suspect your database design needs to be looked at, have you seen:

http://www.r937.com/relational

It is required reading.