asked on

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

ASKER CERTIFIED SOLUTION

Shaun Kline

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jim Dettman (EE MVE)

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 (EE MVE)

Note also you can build up the string:

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

in which case, no line continuation characters.

Jim.

Gustav Brock

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

/gustav

aikimark

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

Brogrim

ASKER

straight forward answer that resolved the problem