Hi,
Iam getting this error:
run time error '-2147217900(80040e14)' unclosed quotation mark after tthe character string ".
my code is below..
please help me its very urgent
Option Explicit
Private sdate As Date
Private sql$
Public Sub Run()
sdate = Now
cvfrm.PrgBar.Value = 0
cvfrm.PrgBar.Min = 0
cvfrm.PrgBar.Visible = False
cvfrm.lbl_3.Caption = ""
cvfrm.lbl_1.Caption = "Please wait, Transfer Unitholder file in progress..."
cvfrm.lbl_2.Caption = "Start at:" & sdate
DoEvents
' clear record in UH database
Call ClearRecord
cvfrm.lbl_1.Caption = "Transfer Unitholder file completed."
cvfrm.lbl_2.Caption = "Start at:" & sdate & Space(4) & "Ended at: " & Now
DoEvents
End Sub
Private Sub ClearRecord()
Dim rs As New ADODB.Recordset
sql = "delete from uuhm"
cnCMT.Execute (sql)
sql = " SELECT UUHMID, UUHMCusNo, UUHMIDTyp, UUHMUHTyp, UUHMCSts, UUHMUHSts, "
sql = sql & " UUHMOID, UUHMUNm, UUHMTitle, UUHMDOB, UUHMSex, UUHMNat, "
sql = sql & " UUHMRace, UUHMRelg, UUHMOccp, UUHMIndus, UUHMUMTyp, UUHMUHCatg, "
sql = sql & " UUHMUSTyp, UUHMTelO, UUHMTelH, UUHMTelHP, UUHMFaxNo,"
sql = sql & " UUHMEMail, UUHMCStm, UUHMPer1Nm, UUHMPer1Ds, UUHMPer1TelO, "
sql = sql & " UUHMPer1TelHP, UUHMPer1EMail, UUHMPer2Nm, UUHMPer2Ds,"
sql = sql & " UUHMPer2TelO, UUHMPer2TelHP, UUHMPer2EMail, UUHMEPFNo, "
sql = sql & " UUHMEPFIn, UUHMEPFBr, UUHMEPFInd, UUHMZakat, UUHMEdu, UUHMInc,"
sql = sql & " UUHMLgge, UUHMPasswd, UUHMStf, UUHMStaff, UUHMMStats, UUHMSInc, "
sql = sql & " UUHMMMaidenNm, UUHMQuesCd, UUHMAnswer, UUHMSMSNm,"
sql = sql & " UUHMSCCat, UUHMUsr, UUHMSyDt, UUHMUsrU, UUHMSyDtU, UUHMSts, "
sql = sql & " UUHMEpfSts, UUHMATyp, UUHMENm, UUHMEBN, UUHMEA1, UUHMEA2,"
sql = sql & " UUHMEA3, UUHMEA4, UUHMPIN, UUHMPINDT, UUHMLNO, UUHMPOB, UUHMPWD, "
sql = sql & " UUHMURL, UUHMPRS, UUHMESolFlg, UUHMPBESolFlg,"
sql = sql & " UUHMEPFPDt from uuhm"
Set rs = cnCOM.Execute(sql)
While Not rs.EOF
sql = "INSERT INTO UUHM (UUHMID, UUHMCusNo, UUHMIDTyp, UUHMUHTyp, UUHMCSts, UUHMUHSts, "
sql = sql & " UUHMOID, UUHMUNm, UUHMTitle, UUHMDOB, UUHMSex, UUHMNat, "
sql = sql & " UUHMRace, UUHMRelg, UUHMOccp, UUHMIndus, UUHMUMTyp, UUHMUHCatg, "
sql = sql & " UUHMUSTyp, UUHMTelO, UUHMTelH, UUHMTelHP, UUHMFaxNo,"
sql = sql & " UUHMEMail, UUHMCStm, UUHMPer1Nm, UUHMPer1Ds, UUHMPer1TelO, "
sql = sql & " UUHMPer1TelHP, UUHMPer1EMail, UUHMPer2Nm, UUHMPer2Ds,"
sql = sql & " UUHMPer2TelO, UUHMPer2TelHP, UUHMPer2EMail, UUHMEPFNo, "
sql = sql & " UUHMEPFIn, UUHMEPFBr, UUHMEPFInd, UUHMZakat, UUHMEdu, UUHMInc,"
sql = sql & " UUHMLgge, UUHMPasswd, UUHMStf, UUHMStaff, UUHMMStats, UUHMSInc, "
sql = sql & " UUHMMMaidenNm, UUHMQuesCd, UUHMAnswer, UUHMSMSNm,"
sql = sql & " UUHMSCCat, UUHMUsr, UUHMSyDt, UUHMUsrU, UUHMSyDtU, UUHMSts, "
sql = sql & " UUHMEpfSts, UUHMATyp, UUHMENm, UUHMEBN, UUHMEA1, UUHMEA2,"
sql = sql & " UUHMEA3, UUHMEA4, UUHMPIN, UUHMPINDT, UUHMLNO, UUHMPOB, UUHMPWD, "
sql = sql & " UUHMURL, UUHMPRS, UUHMESolFlg, UUHMPBESolFlg,"
sql = sql & " UUHMEPFPDt)"
sql = sql & " VALUES ('" & Trim(rs!UUHMID) & "', '" & Trim(rs!UUHMCusNo) & "', '" & rs!UUHMIDTyp & "', "
sql = sql & " '" & Trim(rs!UUHMUHTyp) & "', '" & Trim(rs!UUHMCSts) & "', '" & Trim(rs!UUHMUHSts) & "', "
sql = sql & " '" & Trim(rs!UUHMOID) & "', '" & Trim(rs!UUHMUNm) & "', '" & Trim(rs!UUHMTitle) & "', "
sql = sql & " '" & Format(Trim(rs!UUHMDOB), "yyyy/mm/dd") & "', '" & Trim(rs!UUHMSex) & "', '" & Trim(rs!UUHMNat) & "', "
sql = sql & " '" & Trim(rs!UUHMRace) & "', '" & Trim(rs!UUHMRelg) & "', '" & Trim(rs!UUHMOccp) & "', '" & Trim(rs!UUHMIndus) & "', "
sql = sql & " '" & Trim(rs!UUHMUMTyp) & "', '" & Trim(rs!UUHMUHCatg) & "', "
sql = sql & " '" & Trim(rs!UUHMUSTyp) & "', '" & Trim(rs!UUHMTelO) & "', '" & Trim(rs!UUHMTelH) & "', '" & Trim(rs!UUHMTelHP) & "', "
sql = sql & " '" & Trim(rs!UUHMFaxNo) & "',"
sql = sql & " '" & Trim(rs!UUHMEMail) & "', '" & Trim(rs!UUHMCStm) & "', '" & Replace(Trim(rs!UUHMPer1Nm), "'", "`") & "', "
sql = sql & " '" & Trim(rs!UUHMPer1Ds) & "', '" & Trim(rs!UUHMPer1TelO) & "', "
sql = sql & " '" & Trim(rs!UUHMPer1TelHP) & "', '" & Trim(rs!UUHMPer1EMail) & "', '" & Trim(rs!UUHMPer2Nm) & "',"
sql = sql & " '" & Trim(rs!UUHMPer2Ds) & "',"
sql = sql & " '" & Trim(rs!UUHMPer2TelO) & "', '" & Trim(rs!UUHMPer2TelHP) & "', '" & Trim(rs!UUHMPer2EMail) & "',"
sql = sql & " '" & Trim(rs!UUHMEPFNo) & "', "
sql = sql & " '" & Trim(rs!UUHMEPFIn) & "', '" & Trim(rs!UUHMEPFBr) & "', '" & Trim(rs!UUHMEPFInd) & "', "
sql = sql & " '" & Trim(rs!UUHMZakat) & "', '" & Trim(rs!UUHMEdu) & "', '" & Trim(rs!UUHMInc) & "',"
sql = sql & " '" & Trim(rs!UUHMLgge) & "', '" & Trim(rs!UUHMPasswd) & "', '" & Trim(rs!UUHMStf) & "', "
sql = sql & " '" & Trim(rs!UUHMStaff) & "', '" & Trim(rs!UUHMMStats) & "', '" & Trim(rs!UUHMSInc) & "', "
sql = sql & " '" & Trim(rs!UUHMMMaidenNm) & "', '" & Trim(rs!UUHMQuesCd) & "', '" & Trim(rs!UUHMAnswer) & "', '" & Trim(rs!UUHMSMSNm) & "',"
sql = sql & " '" & Trim(rs!UUHMSCCat) & "', '" & Trim(rs!UUHMUsr) & "', '" & Format(Trim(rs!UUHMSyDt), "yyyy/mm/dd") & "', '" & Trim(rs!UUHMUsrU) & "', "
sql = sql & " '" & Format(Trim(rs!UUHMSyDtU), "yyyy/mm/dd") & "', '" & Trim(rs!UUHMSts) & "', "
sql = sql & " '" & Trim(rs!UUHMEpfSts) & "', '" & Trim(rs!UUHMATyp) & "', '" & Trim(rs!UUHMENm) & "', '" & Trim(rs!UUHMEBN) & "',"
sql = sql & " '" & Trim(rs!UUHMEA1) & "', '" & Trim(rs!UUHMEA2) & "',"
sql = sql & " '" & Trim(rs!UUHMEA3) & "', '" & Trim(rs!UUHMEA4) & "', '" & Trim(rs!UUHMPIN) & "', '" & Trim(rs!UUHMPINDT) & "', "
sql = sql & " '" & Trim(rs!UUHMLNO) & "', '" & Trim(rs!UUHMPOB) & "', '" & Trim(rs!UUHMPWD) & "', "
sql = sql & " '" & Trim(rs!UUHMURL) & "', '" & Trim(rs!UUHMPRS) & "', '" & Trim(rs!UUHMESolFlg) & "', '" & Trim(rs!UUHMPBESolFlg) & "',"
sql = sql & " '" & Format(Trim(rs!UUHMEPFPDt), "yyyy/mm/dd") & "')"
cnCMT.Execute (sql)
rs.MoveNext
Wend
rs.Close
Set rs = Nothing
End Sub