Reputation: 3511
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 :)
Upvotes: 8
Views: 53769
Reputation: 15
Another method (very similar to Bathsheba's method above) is to use the _
underscore first and then use the &
ampersand character on the following line.
With mclsQuery
.SQLCommand = "SELECT rp.ITEM_CD " _
& "FROM RECIPE_PRODUCT rp " _
& "JOIN RECIPE_STEP rs ON rs.RECIPE_STEP_ID = rp.RECIPE_STEP_ID " _
& "JOIN RECIPE_REV rr ON rr.RECIPE_REV_ID = rs.RECIPE_REV_ID " _
& "JOIN RECIPE r ON r.RECIPE_CD = rr.RECIPE_CD " _
& "WHERE r.RECIPE_CD = '" & SimpleTouchRecipe.Value & "'"
It's up to your personal preference! Both the & _
and _
w/ &
on the next line works.
I like the _
+ new line + &
method because I sometimes build very long lines of code (still!) and the &
at the left margin reminds me it's a multi line statement if the right margin isn't visible.
See To break a single statement into multiple lines (Visual Basic) from the Microsoft Learn documentation.
Upvotes: 1
Reputation: 234885
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.
Upvotes: 14