Help with SQL Insert statement in VBA Code

ausmoran · December 26th, 2006, 11:16 AM

I have a piece of VBA code in which I am having some trouble with a simple SQL insert statement. I believe the problem comes from syntax at the end of the first line (though I may be wrong). I am hoping that someone can give me a hand here:

Code:

sql = "insert into dbo_tbl_Call (Member_Number,CallDate,Question,Answer,StartTime,StopTime,CallDurationMinutes,Commission,CommissionBetweenBrokers,CommissionBetweenBrokersandAgents,CommissionBetweenBrokersandSellerBuyers,CommissionRipoff,RebateNegotiationOfCommission,CommissionOther,LicenseLaw,LicenseLawAdvertising,LicenseLawOwnership,LicenseLawChangingBrokers,LicenseLawExclusiveListing,LicenseLawContinuingEducation,LicenseLawLLC,HowTo_LicenseLaw,PersonalAssistant,LicenseLawOther,FairHousing,FairHousingDiscrimination,FairHousingOther,Contract,ListingAgreement,ContractBinder,ContractPurchaseOffer,ContractMultipleCompetingOffers,ContractDepositsEscrow,ContractOther,Disclosure,DisclosureBrokerStatusAgency,DisclosurePropertyCondition,DisclosureSexOffender, _
DisclosureLeadPaint,DisclosureOther,Agnecy,BrokersAgency,SellersAgent,BuyersAgent,ChangingAgency,AgencyOther,DOS,ChangingBrokers,ComplaintForViolation,DOSother,Referrals,NonLicensees,Licensees,OutOfStateBrokers,ReferralsOther,Arbitration,ArbitrationArbitrableIssue,ArbitrationCurrentlyInArbitration,ArbitrationArbitrationCompletedAppeal,ArbitrationCompliance,ArbitrationOther,CodeOfEthics,CCodeOfEthicsViolation,CodeOfEthicsProcess,CodeOfEthicsDecision,CodeOfEthicsAppeal,CodeOfEthicsOther,BoardAssociation,MembershipIssue,ElectionIssue,ReferralAgent,BoardAssociationOther,MLS,MLSVOW,MLSIDX,MLSPolicy,MLSother,OtherHotlineIssue,DoNotCall,DoNotFax,CANSPAM,RESPA,MortgageBroker,IndependentContractor,AntiTrust,Other) " & _
"values " & _
"(" & Me.Member_Number & ",#" & Me.CallDate & "#,'" & Replace(Me.Question, "'", "''") & "','" & Replace(Me.Answer, "'", "''") & "',#" & Me. _
StartTime & "#,#" & Me.EndTime & "#," & CallDurationMinutes & _
"," & Commission & "," & CommissionBetweenBrokers & "," & CommissionBetweenBrokersandAgents & "," & CommissionBetweenBrokersandSellerBuyers & "," & CommissionRipoff & "," & RebateNegotiationOfCommission & "," & CommissionsOther & "," & LicenseLaw & "," & LicenseLawAdvertising & "," & LicenseLawOwnership & "," & LicenseLawChangingBrokers & "," & LicenseLawExclusiveListing & "," & LicenseLawContinuingEducation & "," & LicenseLawLLC & "," & _
HowTo_LicenseLaw & "," & PersonalAssistant & "," & LicenseLawOther & "," & FairHousing & "," & FairHousingDiscrimination & "," & FairHousingOther & "," & Contract & "," & ListingAgreement & "," & ContractBinder & "," & ContractPurchaseOffer & "," & ContractMultipleCompetingOffers & "," & ContractDepositsEscrow & "," & ContractOther & "," & Disclosure & "," & DisclosureBrokerStatusAgency & "," & DisclosurePropertyCondition & "," & DisclosureSexOffender & "," & _
DisclosureLeadPaint & "," & DisclosureOther & "," & Agency & "," & BrokersAgency & "," & SellersAgent & "," & BuyersAgent & "," & ChangingAgency & "," & AgencyOther & "," & DOS & "," & ChangingBrokers & "," & ComplaintForViolation & "," & DOSother & "," & Referrals & "," & NonLicensees & "," & Licensees & "," & OutOfStateBrokers & "," & ReferralsOther & "," & _
Arbitration & "," & ArbitrationArbitrableIssue & "," & ArbitrationCurrentlyInArbitration & "," & ArbitrationArbitrationCompletedAppeal & "," & ArbitrationCompliance & "," & ArbitrationOther & "," & CodeOfEthics & "," & CodeOfEthicsViolation & "," & CodeOfEthicsProcess & "," & CodeOfEthicsDecision & "," & _
CodeOfEthicsAppeal & "," & CodeOfEthicsOther & "," & BoardAssociation & "," & MembershipIssue & "," & ElectionIssue & "," & ReferralAgent & "," & BoardAssociationOther & "," & MLS & "," & MLSVOW & "," & MLSIDX & "," & MLSPolicy & "," & MLSOther & "," & OtherHotlineIssue & "," & DoNotCall & "," & DoNotFax & "," & CANSPAM & "," & RESPA & "," & MortgageBroker & "," & IndependentContractor & "," & AntiTrust & "," & Other & ")"

Thanks in advance for any help you might be able to offer!

Austin Moran

stealthdevil · December 28th, 2006, 06:16 PM

Here is a sample piece of code I have used that works.

        DoCmd.RunSQL ("INSERT INTO tbl_os_seq ( LOGO, YEAR, OCC, [WHLSL PACK], [WHLSL AGIN], [WHLSL AGIN DESC], HOLIDAY, PLG, [PLG DESCRIPTION]) " _
               & " SELECT tbl_import.LOGO, tbl_import.YEAR, tbl_import.OCC, tbl_import.[NEW SEQ], tbl_import.OVERSIZED, tbl_import.[MARKETING NUMBER], tbl_import.[WHLSL AGIN DESC], tbl_import.HOLIDAY, tbl_import.PLG, tbl_import.[PLG DESCRIPTION] " _
               & " From tbl_import WHERE tbl_import.OCC = " & occasion_lkup & "ORDER BY tbl_import.OVERSIZED, tbl_import.[NEW SEQ]")

HTH,

Dave