Multiple lines in VBA

I am trying to split vba lines of code
INSERT INTO tblddai ( DDAIID, DDAI_TitleID, PermitApplicantSourseID, DDAIStatusID, DateEntered, SName, CName, Addres1, Addres2, Addres3, CountyID, DOB, Disability, DiagnosticCodeID, PrimMedCert, Photo, Signature, Phone, Mobile, email, DDAID_UserID, DDAI_ConditionID, DDAI_CriteriaID, GenderID )
SELECT deleted_ddaiid.DDAIID, deleted_ddaiid.DDAI_TitleID, deleted_ddaiid.PermitApplicantSourseID, deleted_ddaiid.DDAIStatusID, deleted_ddaiid.DateEntered, deleted_ddaiid.SName, deleted_ddaiid.CName, deleted_ddaiid.Addres1, deleted_ddaiid.Addres2, deleted_ddaiid.Addres3, deleted_ddaiid.CountyID, deleted_ddaiid.DOB, deleted_ddaiid.Disability, deleted_ddaiid.DiagnosticCodeID, deleted_ddaiid.PrimMedCert, deleted_ddaiid.Photo, deleted_ddaiid.Signature, deleted_ddaiid.Phone, deleted_ddaiid.Mobile, deleted_ddaiid.email, deleted_ddaiid.DDAID_UserID, deleted_ddaiid.DDAI_ConditionID, deleted_ddaiid.DDAI_CriteriaID, deleted_ddaiid.GenderID
FROM deleted_ddaiid
WHERE (((deleted_ddaiid.DDAIID)=[Forms]![frmDDAI_SB]![txt_Archived_DDAIID]));

Open in new window




The problem is in the VBA Editor it wont allow that many characters and when i do split the lines as follows

       
DoCmd.RunSQL "INSERT INTO tblddai ( DDAIID, DDAI_TitleID, PermitApplicantSourseID, DDAIStatusID, DateEntered, SName, CName, Addres1, Addres2, Addres3, CountyID, DOB, Disability, DiagnosticCodeID, PrimMedCert, Photo, Signature, Phone, Mobile, email, DDAID_UserID, DDAI_ConditionID, DDAI_CriteriaID, GenderID ) SELECT deleted_ddaiid.DDAIID, deleted_ddaiid.DDAI_TitleID, deleted_ddaiid.PermitApplicantSourseID, deleted_ddaiid.DDAIStatusID, deleted_ddaiid.DateEntered, deleted_ddaiid.SName, deleted_ddaiid.CName, deleted_ddaiid.Addres1, deleted_ddaiid.Addres2, deleted_ddaiid.Addres3, deleted_ddaiid.CountyID, deleted_ddaiid.DOB, deleted_ddaiid.Disability, deleted_ddaiid.DiagnosticCodeID, deleted_ddaiid.PrimMedCert, deleted_ddaiid.Photo, deleted_ddaiid.Signature, deleted_ddaiid.Phone, deleted_ddaiid.Mobile, deleted_ddaiid.email, deleted_ddaiid.DDAID_UserID, deleted_ddaiid.DDAI_ConditionID, deleted_ddaiid.DDAI_CriteriaID, deleted_ddaiid.GenderID FROM deleted_ddaiid " [b]_
                        &[/b] WHERE(((deleted_ddaiid.DDAIID) = [Forms]![frmDDAI_SB]![txt_Archived_DDAIID]))

Open in new window


I get the following highlighted and an error message
![txt_Archived_DDAIID]

Compile Error
Sub or Function not Defined
BrogrimInformation Systems Development ManagerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Shaun KlineLead Software EngineerCommented:
You need to add a quotation mark in front of WHERE on the second line.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
I'd also add two other points:

Do it like this:

Dim strSQL as string

strSQL = "INSERT INTO tblddai ( DDAIID, DDAI_TitleID, PermitApplicantSourseID, DDAIStatusID, DateEntered, SName, CName, Addres1, Addres2, Addres3, CountyID, DOB, Disability, DiagnosticCodeID, PrimMedCert, Photo, Signature, Phone, Mobile, email, DDAID_UserID, DDAI_ConditionID, DDAI_CriteriaID, GenderID ) SELECT deleted_ddaiid.DDAIID, deleted_ddaiid.DDAI_TitleID, deleted_ddaiid.PermitApplicantSourseID, deleted_ddaiid.DDAIStatusID, deleted_ddaiid.DateEntered, deleted_ddaiid.SName, deleted_ddaiid.CName, deleted_ddaiid.Addres1, deleted_ddaiid.Addres2, deleted_ddaiid.Addres3, deleted_ddaiid.CountyID, deleted_ddaiid.DOB, deleted_ddaiid.Disability, deleted_ddaiid.DiagnosticCodeID, deleted_ddaiid.PrimMedCert, deleted_ddaiid.Photo, deleted_ddaiid.Signature, deleted_ddaiid.Phone, deleted_ddaiid.Mobile, deleted_ddaiid.email, deleted_ddaiid.DDAID_UserID, deleted_ddaiid.DDAI_ConditionID, deleted_ddaiid.DDAI_CriteriaID, deleted_ddaiid.GenderID FROM deleted_ddaiid " _
                        &
" WHERE(((deleted_ddaiid.DDAIID) = " & [Forms]![frmDDAI_SB]![txt_Archived_DDAIID] & "))"

DoCmd.RunSQL strSQL

 This lets you put a breakpoint on the DoCmd and inspect the SQL statement that is about to be executed.  Nice way to pin-point problems.

2. Don't use runsql.   You can't trap errors with it.  Instead, use:

 CurrentDB().Execute strSQL, dbFailOnError

Jim.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Note also you can build up the string:

strSQL = "INSERT...."
strSQL = strSQL & " FROM...."
strSQL = strSQL & " WHERE..."

in which case, no line continuation characters.

Jim.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Gustav BrockCIOCommented:
Listen to Jim. But you also need proper quotes:
" .... FROM deleted_ddaiid " & _
"WHERE(((deleted_ddaiid.DDAIID) = [Forms]![frmDDAI_SB]![txt_Archived_DDAIID]))"

/gustav
aikimarkCommented:
1. Since you are selecting the source data from deleted_ddaiid, you might be able to shorten your SQL by dropping the table name qualifier.
INSERT INTO tblddai ( DDAIID, DDAI_TitleID, PermitApplicantSourseID, DDAIStatusID, DateEntered, SName, CName, Addres1, Addres2, Addres3, CountyID, DOB, Disability, DiagnosticCodeID, PrimMedCert, Photo, Signature, Phone, Mobile, email, DDAID_UserID, DDAI_ConditionID, DDAI_CriteriaID, GenderID )
SELECT DDAIID, DDAI_TitleID, PermitApplicantSourseID, DDAIStatusID, DateEntered, SName, CName, Addres1, Addres2, Addres3, CountyID, DOB, Disability, DiagnosticCodeID, PrimMedCert, Photo, Signature, Phone, Mobile, email, DDAID_UserID, DDAI_ConditionID, DDAI_CriteriaID, GenderID
FROM deleted_ddaiid
WHERE (((deleted_ddaiid.DDAIID)=[Forms]![frmDDAI_SB]![txt_Archived_DDAIID]));

Open in new window


2. Since the source and destination table fields seem to be the same, you might shorten the SQL as
INSERT INTO tblddai 
SELECT *
FROM deleted_ddaiid
WHERE (((deleted_ddaiid.DDAIID)=[Forms]![frmDDAI_SB]![txt_Archived_DDAIID]));

Open in new window

Note: This isn't a best practice.

3. You could store your SQL in a database table or other external source.

4. You could create a routine to create your SQL string by iterating the source/target fields or a field-mapping table.

5. I concur with Jim about not invoking the RunSQL method.  However, my preference is
dbEngine(0)(0).Execute strSQL

Open in new window

BrogrimInformation Systems Development ManagerAuthor Commented:
straight forward answer that resolved the problem
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.