SQL statement not working

[Brogrim]

I have converted the following from a Macro, my problem is the 2nd part

' Move all the Members record to the tblMember Deceased

I am getting an error message on syntax and end if

Thanks in advance
__________________________________________________________

' Move the member who is marked deceased into a different table, Member Deceased Notes
DoCmd.RunSQL "INSERT INTO tblMemberDeceasedNotes ( MemberID ) SELECT [tblMember].[MemberID] FROM tblMember WHERE ((([tblMember].[MemberID])=[Forms]![frmMember]![MemberID]) And (([tblMember].[MemStatusID])=8)); ", -1
' Move all the Members record to the tblMember Deceased
DoCmd.RunSQL "INSERT INTO tblMemberDeceased ( MemberID, DateEntered, AssesmentDate, ReferralDate, CommencementOfService, TitleID, FirstName, SecondName, GenderID, Member, MemStatusID, BadgeHolder, Spokeout, HouseNo, HouseLetter, HouseName, Address1, Address2, Address3, CountyID, PostCode, PostCodePrefix, DOB, PSDSID, AgeGroupID, ROCID, Telephone, Email, PrimaryDiagnosticID, SecondaryDiagnosticID, DisabilityLevelID, DisabilityNotes, HealthBoardID, ComCareAreaID, WheelchairTypeID, DRC, ASL, WCSalesRepairLoan, Motoring, ILS, InformationServices, Sports1, Respite1, EducationTraining, PeerCounselling, YouthServices, Other, IWAAdvice, WheelchairUser, IWATransport, Employment, TrainingED, Housing, AidsAppliances, IncomeSupport, IWAOther, SupportIWA, ActivelySupport, LocalInvolved, WritingLetters, Campaigns, AssistingPrograms, ConsumerPanels, AdvocacyLobbying, Fundraising, PreferenceOther, OtherComments, Signed, [Date], MemStatus, InputtedID, AddressStatus, last_modified )"
SELECT tblMember.MemberID, tblMember.DateEntered, tblMember.AssesmentDate, tblMember.ReferralDate, tblMember.CommencementOfService, tblMember.TitleID, tblMember.FirstName, tblMember.SecondName, tblMember.GenderID, tblMember.Member, tblMember.MemStatusID, tblMember.BadgeHolder, tblMember.Spokeout, tblMember.HouseNo, tblMember.HouseLetter, tblMember.HouseName, tblMember.Address1, tblMember.Address2, tblMember.Address3, tblMember.CountyID, tblMember.PostCode, tblMember.PostCodePrefix, tblMember.DOB, tblMember.PSDSID, tblMember.AgeGroupID, tblMember.ROCID, tblMember.Telephone, tblMember.Email, tblMember.PrimaryDiagnosticID, tblMember.SecondaryDiagnosticID, tblMember.DisabilityLevelID, tblMember.DisabilityNotes, tblMember.HealthBoardID, tblMember.ComCareAreaID, tblMember.WheelchairTypeID, tblMember.DRC, tblMember.ASL, tblMember.WCSalesRepairLoan, tblMember.Motoring, tblMember.ILS, tblMember.InformationServices, tblMember.Sports1, tblMember.Respite1, tblMember.EducationTraining, tblMember.PeerCounselling,
tblMember.YouthServices , tblMember.Other, tblMember.IWAAdvice, tblMember.WheelchairUser, tblMember.IWATransport, tblMember.Employment, tblMember.TrainingED, tblMember.Housing, tblMember.AidsAppliances, tblMember.IncomeSupport, tblMember.IWAOther, tblMember.SupportIWA, tblMember.ActivelySupport, tblMember.LocalInvolved, tblMember.WritingLetters, tblMember.Campaigns, tblMember.AssistingPrograms, tblMember.ConsumerPanels, tblMember.AdvocacyLobbying, tblMember.Fundraising, tblMember.PreferenceOther, tblMember.OtherComments, tblMember.Signed, tblMember.Date, tblMember.MemStatus, tblMember.InputtedID, tblMember.AddressStatus, tblMember.last_modified
FROM tblMember
WHERE (((tblMember.MemberID)=Forms!frmMember!MemberID) And ((tblMember.MemStatusID)=8)); ", -1
' frm Member Deceased Notes
DoCmd.OpenForm "frmMemberDeceasedNotes", acNormal, "", "", , acNormal
' Delete the Member from the member Table
DoCmd.RunSQL "DELETE [tblMember].[MemberID] FROM tblMember WHERE ((([tblMember].[MemberID])=[Forms]![frmMember]![MemberID])); ", -1
' Close the Member Form
DoCmd.Close acForm, "frmMember"

 

[Golom]

It may be just an artifact of how you posted but it looks like your INSERT statement is terminated with

InputtedID, AddressStatus, last_modified )[COLOR=red yellow]"[/color]

The part of the statement beginning SELECT tblMember.MemberID... doesn't start with a quote nor is it concatenated with the previous line.

When I paste your code into my system it highlights the Select ... line as invalid code and does not show it as part of the preceding statement.

 

[Remou]

Aside: I have often found it better to have a Member Status, which would included Deceased, Inactive etc.

 

[JerryKlmns]

A big sql statement... It could be the red thing

 DoCmd.RunSQL "INSERT INTO tblMemberDeceased ( MemberID, DateEntered, AssesmentDate, ReferralDate, CommencementOfService, TitleID, FirstName, SecondName, GenderID, Member, MemStatusID, BadgeHolder, Spokeout, HouseNo, HouseLetter, HouseName, Address1, Address2, Address3, CountyID, PostCode, PostCodePrefix, DOB, PSDSID, AgeGroupID, ROCID, Telephone, Email, PrimaryDiagnosticID, SecondaryDiagnosticID, DisabilityLevelID, DisabilityNotes, HealthBoardID, ComCareAreaID, WheelchairTypeID, DRC, ASL, WCSalesRepairLoan, Motoring, ILS, InformationServices, Sports1, Respite1, EducationTraining, PeerCounselling, YouthServices, Other, IWAAdvice, WheelchairUser, IWATransport, Employment, TrainingED, Housing, AidsAppliances, IncomeSupport, IWAOther, SupportIWA, ActivelySupport, LocalInvolved, WritingLetters, Campaigns, AssistingPrograms, ConsumerPanels, AdvocacyLobbying, Fundraising, PreferenceOther, OtherComments, Signed, [Date], MemStatus, InputtedID, AddressStatus, last_modified )[RED][B]"[/B][/RED]
    SELECT tblMember.MemberID, tblMember.DateEntered, tblMember.AssesmentDate, tblMember.ReferralDate, tblMember.CommencementOfService, tblMember.TitleID, tblMember.FirstName, tblMember.SecondName, tblMember.GenderID, tblMember.Member, tblMember.MemStatusID, tblMember.BadgeHolder, tblMember.Spokeout, tblMember.HouseNo, tblMember.HouseLetter, tblMember.HouseName, tblMember.Address1, tblMember.Address2, tblMember.Address3, tblMember.CountyID, tblMember.PostCode, tblMember.PostCodePrefix, tblMember.DOB, tblMember.PSDSID, tblMember.AgeGroupID, tblMember.ROCID, tblMember.Telephone, tblMember.Email, tblMember.PrimaryDiagnosticID, tblMember.SecondaryDiagnosticID, tblMember.DisabilityLevelID, tblMember.DisabilityNotes, tblMember.HealthBoardID, tblMember.ComCareAreaID, tblMember.WheelchairTypeID, tblMember.DRC, tblMember.ASL, tblMember.WCSalesRepairLoan, tblMember.Motoring, tblMember.ILS, tblMember.InformationServices, tblMember.Sports1, tblMember.Respite1, tblMember.EducationTraining, tblMember.PeerCounselling,
    tblMember.YouthServices , tblMember.Other, tblMember.IWAAdvice, tblMember.WheelchairUser, tblMember.IWATransport, tblMember.Employment, tblMember.TrainingED, tblMember.Housing, tblMember.AidsAppliances, tblMember.IncomeSupport, tblMember.IWAOther, tblMember.SupportIWA, tblMember.ActivelySupport, tblMember.LocalInvolved, tblMember.WritingLetters, tblMember.Campaigns, tblMember.AssistingPrograms, tblMember.ConsumerPanels, tblMember.AdvocacyLobbying, tblMember.Fundraising, tblMember.PreferenceOther, tblMember.OtherComments, tblMember.Signed, tblMember.Date, tblMember.MemStatus, tblMember.InputtedID, tblMember.AddressStatus, tblMember.last_modified
    FROM tblMember
    WHERE (((tblMember.MemberID)=Forms!frmMember!MemberID) And ((tblMember.MemStatusID)=8));  ", -1

But I would create an parameter action query to be executed. And also if these are all the fields of tblMember going to tblMemberDeceased, prefer

Insert Into tblMemberDeceased.*
Select tblMember.*
From tblMember
WHERE (((tblMember.MemberID)=Forms!frmMember!MemberID) And ((tblMember.MemStatusID)=8));

 

[dhookom]

I prefer to leave all the field names in the sql but create code with shorter lines since your originals statements are too long for VBA.

 Dim strSQL As String
 strSQL = "INSERT INTO tblMemberDeceased ( MemberID, DateEntered, AssesmentDate, " & _
    "ReferralDate, CommencementOfService, TitleID, FirstName, SecondName, GenderID, " & _
    "Member, MemStatusID, BadgeHolder, Spokeout, HouseNo, HouseLetter, HouseName, Address1, " & _
    "Address2, Address3, CountyID, PostCode, PostCodePrefix, DOB, PSDSID, AgeGroupID, ROCID, " & _
    "Telephone, Email, PrimaryDiagnosticID, SecondaryDiagnosticID, DisabilityLevelID, DisabilityNotes, " & _
    "HealthBoardID, ComCareAreaID, WheelchairTypeID, DRC, ASL, WCSalesRepairLoan, Motoring, ILS, " & _
    "InformationServices, Sports1, Respite1, EducationTraining, PeerCounselling, YouthServices, Other, " & _
    "IWAAdvice, WheelchairUser, IWATransport, Employment, TrainingED, Housing, AidsAppliances, " & _
    "IncomeSupport, IWAOther, SupportIWA, ActivelySupport, LocalInvolved, WritingLetters, Campaigns, " & _
    "AssistingPrograms, ConsumerPanels, AdvocacyLobbying, Fundraising, PreferenceOther, OtherComments, " & _
    "Signed, [Date], MemStatus, InputtedID, AddressStatus, last_modified ) "
    strSQL = strSQL & "SELECT MemberID, DateEntered, AssesmentDate, ReferralDate, CommencementOfService, " & _
    "TitleID, FirstName, SecondName, GenderID, Member, MemStatusID, BadgeHolder, Spokeout, HouseNo, " & _
    "HouseLetter, HouseName, Address1, Address2, Address3, CountyID, PostCode, PostCodePrefix, DOB, " & _
    "PSDSID, AgeGroupID, ROCID, Telephone, Email, PrimaryDiagnosticID, SecondaryDiagnosticID, " & _
    "DisabilityLevelID, DisabilityNotes, HealthBoardID, ComCareAreaID, WheelchairTypeID, DRC, ASL, " & _
    "WCSalesRepairLoan, Motoring, ILS, InformationServices, Sports1, Respite1, EducationTraining, " & _
    "PeerCounselling, YouthServices , Other, IWAAdvice, WheelchairUser, IWATransport, Employment, " & _
    "TrainingED, Housing, AidsAppliances, IncomeSupport, IWAOther, SupportIWA, ActivelySupport, " & _
    "LocalInvolved, WritingLetters, Campaigns, AssistingPrograms, ConsumerPanels, AdvocacyLobbying, " & _
    "Fundraising, PreferenceOther, OtherComments, Signed, Date, MemStatus, InputtedID, AddressStatus, " & _
    "last_modified " & _
    "FROM tblMember " & _
    "WHERE MemberID= " & Forms!frmMember!MemberID & " And MemStatusID=8 "
    DoCmd.RunSQL strSQL, -1

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]