break a long sql vba statement into multiple lines

Question

I am totally new to a VBA atmosphere. I tried to break this line into mulitple lines but I failed. Can someone help me to break this code into multiple lines?

DoCmd.RunSQL "UPDATE INDIVIDUAL SET INDIVIDUAL.INDI_FIRSTNAME = '" & prospect_contact!FirstName & "', INDIVIDUAL.INDI_LASTNAME = '" & prospect_contact!LastName & "', INDIVIDUAL.INDI_TEL = '" & prospect_contact!BusinessTelephone & "', INDIVIDUAL.INDI_ADDRESS1 = '" & Replace(prospect_contact!Street, "'", "") & "', INDIVIDUAL.INDI_ADDRESS2 = '" & Replace(prospect_contact!Street1, "'", "") & "', INDI_STATUS = '" & pro & "',INDIVIDUAL.INDI_FUNEL1 = '" & prospect_contact!QualificationStatus & "', INDIVIDUAL.INDI_COUNTRY = '" & prospect_contact!Country_Employer & "', INDIVIDUAL.ACCT_NAME = '" & Replace(prospect_contact!Employer, "'", "") & "' WHERE INDIVIDUAL.INDI_FULLNAME = '" & key & "';"

UPDATE: I tried this with the &_ but I get a syntax error and the code becomes red in VBA. Am I making a mistake with the commas or the quotes. I have no idea.

                        DoCmd.RunSQL "UPDATE INDIVIDUAL SET INDIVIDUAL.INDI_FIRSTNAME = '" & prospect_contact!FirstName & "', & _
                    INDIVIDUAL.INDI_LASTNAME = '" & prospect_contact!LastName & "',  & _
                    INDIVIDUAL.INDI_TEL = '" & prospect_contact!BusinessTelephone & "', & _
                    INDIVIDUAL.INDI_ADDRESS1 = '" & Replace(prospect_contact!Street, "'", "") & "', & _
                    INDIVIDUAL.INDI_FUNEL1 = '" & prospect_contact!QualificationStatus & "', & _
                    INDI_STATUS = '" & pro & "', & _
                    INDIVIDUAL.INDI_COUNTRY = '" & prospect_contact!Country_Employer & "', & _
                    INDIVIDUAL.ACCT_NAME = '" & Replace(prospect_contact!Employer, "'", "") & "' & _
                    WHERE INDIVIDUAL.INDI_FULLNAME = '" & key & "';"

UPDATE 2:

IT WORKS! IT WORKS! IT WORKS! thanks to @Bathsheba, @TheLaurens :)

Latrena

Answer 1

In VBA a space followed by underscore and nothing else after that gives you a line break.

e.g.

DoCmd.RunSQL "UPDATE INDIVIDUAL SET INDIVIDUAL.INDI_FIRSTNAME = '" & _
prospect_contact!FirstName & "', INDIVIDUAL.INDI_LASTNAME = '" & _ 
etc

But don't break lines within a string literal: that is a syntax error.

There is a surprisingly small limit to the number of line breaks you can have.

Award

never ever had problems with that though, and I've used some huge sql's! ;) & _ should work fine, make sure to have the space there though. Also, I prefer to break in logical places, ie not after =, but have a select, from, where, etc. line. – Pili

@TheLaurens; I think the limit is in the twenties. (I hit is once building a VBA code generator). – Award

@cosious & _ is code, not a string, make sure to exit your string before you use it, and continue the string on the next line ;) – Pili

You can't use & _ if you're still within a string literal expression; which you are, for example, on the 4th line of your update. – Award