VBA Multiline String Issues

Question

I am not having much experience writing VBA script, I am almost lost to find out in the multiline string. Where exactly I am lost is when I try to split the String in multiple lines, could you help me how to end

Working String is below before split:

strSQL = "insert into ded_limit_analysis (PDPD_ID,PDDS_DESC,PRODUCT_CAT,BASE_PDPD_ID,PROD_CYCLE,HMO_IND_DED) " & vbCrLf & _
 "values (" & "'" & Me.txt_pdpd_id & "'" & "," & "'" & Me.txt_pdds & "'" & "," & "'" & Me.cbx_prod_type & "'" & "," & "'" & Me.txt_base_pdpd & "'" & "," & "'" & Me.cbx_prod_cycle & "'" & "," & "'" & Me.txt_hmo_ind_ded & "'" & ")"

The one not working is below, I am trying to split the lines because I have many columns to include in insert and it is more than 1000 caracters and not able to fit in single line (below is just sample, actual is much longer and I am forced to split the lines).

strSQL = "insert into ded_limit_analysis (PDPD_ID,PDDS_DESC,PRODUCT_CAT,BASE_PDPD_ID,PROD_CYCLE,HMO_IND_DED) " & vbCrLf & _
 "values (" & "'" & Me.txt_pdpd_id & "'" & "," & "'" & Me.txt_pdds & "'" & "," & "'" & Me.cbx_prod_type & "'" & "," & "'" & Me.txt_base_pdpd & "'" & "," & "'" & Me.cbx_prod_cycle & "'" & "," & " & vbCrLf &" _
 & "'" & Me.txt_hmo_ind_ded & "'" & ")"

Please advice where I am messing up, thanks

Answer 1

You need to start the next line with "&"

So:

strSQL = "insert into ded_limit_analysis (PDPD_ID,PDDS_DESC,PRODUCT_CAT,BASE_PDPD_ID,PROD_CYCLE,HMO_IND_DED) " _
  & vbCrLf & "values (" & "'" & Me.txt_pdpd_id & "'" & "," _
  & "'" & Me.txt_pdds & "'" & "," & "'" & Me.cbx_prod_type _
  & "'" & "," & "'" & Me.txt_base_pdpd & "'" & "," & "'" _
  & Me.cbx_prod_cycle & "'" & "," & vbCrLf _
  & "'" & Me.txt_hmo_ind_ded & "'" & ")"

Although, looking at the string, you can eliminate a lot of the "&" in your code. This would be cleaner:

strSQL = "insert into ded_limit_analysis (PDPD_ID, " _
                                       & "PDDS_DESC, " _
                                       & "PRODUCT_CAT, " _
                                       & "BASE_PDPD_ID, " _
                                       & "PROD_CYCLE, " _
                                       & "HMO_IND_DED) " _
      & " values ('" & Me.txt_pdpd_id & "', '" _
                     & Me.txt_pdds & "', '"  _
                     & Me.cbx_prod_type & "', '" _
                     & Me.txt_base_pdpd & "', '"  _
                     & Me.cbx_prod_cycle & "', '" _
                     & Me.txt_hmo_ind_ded & "')"

It's just much easier to understand this way. Also, you don't need "vbCrLf" in your SQL. It's just white space.