Else without IF error

[sherlocked]

Hello experts,

I'm trying to write a function that will check if a record exists, update if so, insert if not.

For some reason when I test this I get an "Else without If" error. There is clearly an IF statement at the top, so I'm a little flummoxed about why.

Any ideas?

Private Sub btnSubmit_Click()

Dim rst1 As Recordset
Dim rst2 As Recordset
Dim rst3 As Recordset

Dim str1 As String
Dim str2 As String
Dim str3 As String

Me.EnteredDate = Date
DoCmd.Save

    str1 = "SELECT tblRecords.AppNo " _
         & "FROM tblRecords " _
         & "IN 'MYDATABASEPATH' " _
         & "WHERE tblRecords.AppNo = '" & Form_frmStatusUpdate.frmUpdateCaseData.Form.AppNo & "'"
         
    Set rst1 = CurrentDb.OpenRecordset(str1)
    
If rst1.RecordCount > 0 Then

    str2 = "Select * FROM tblDSReferralROI " _
         & "WHERE tblDSReferralROI.[App#] = '" & Form_frmStatusUpdate.frmUpdateCaseData.Form.AppNo & "'"
         
    Set rst2 = CurrentDb.OpenRecordset(str2)
             
    With rst2
    
    rst2!AppType = Me.AppType
    rst2!FraudType = Me.FraudType
    rst2!FraudReason = Me.FraudReason
    rst2!DateROIRcvd = Me.ROIReceivedDate
    rst2!ImpFName = Me.ImpFName
    rst2!ImpLName = Me.ImpLName
    rst2!ImpMI = Me.ImpMI
    rst2!ImpGender = Me.ImpGender
    rst2!ImpPOB = Me.ImpPOB
    rst2!ImpDOB = Me.ImpDOB
    rst2!AFileNo = Me.[A#]
    rst2!Alias = Me.Alias
    rst2!TECSLookout = Me.TECSLookout
    rst2!F46 = Me.F46
    rst2!Prosecuted = Me.Prosecuted
    rst2!USCISDocs = Me.USCISDocs
    rst2!USCISDocType = Me.USCISDocType
    rst2!USCISAppDate = Me.USCISAppDate
    rst2!USCISDecisionDate = Me.USCISDecisionDate
    rst2!USCISStatus = Me.USCISStatus
    rst2!Disposition = Me.Disposition
    
    str3 = "SELECT tblRecords.AppNo, tblRecords.AppType, tblRecords.Product, tblRecords.EF, tblRecords.FraudType, tblRecords.FraudReason, tblRecords.Source, " _
               & "tblRecords.Status , tblRecords.DateROIRcvd, tblRecords.DSOffice, tblRecords.Agency, tblRecords.Region, tblRecords.FName, tblRecords.MI, " _
               & "tblRecords.HPhone,tblRecords.LName , tblRecords.CPhone, tblRecords.Gender, tblRecords.DOB, tblRecords.Email, tblRecords.POB, tblRecords.POBSort, " _
               & "tblRecords.SSN, tblRecords.FPMComments, tblRecords.[FPM Status], tblRecords.IAComments, tblRecords.Disposition, " _
               & "tblRecords.[FPM Review Complete], tblRecords.[FPM EmpNo], tblRecords.MailAdd, tblRecords.MailApt, tblRecords.MailCity, " _
               & "tblRecords.MailState, tblRecords.MailZip, tblRecords.MailCareOf, tblRecords.ExecYear, tblRecords.ExecMonth, tblRecords.ExecDate, " _
               & "tblRecords.PermAdd, tblRecords.FacilityNo, tblRecords.PermApt, tblRecords.FacilityType, tblRecords.PermCity, tblRecords.AgentLName, " _
               & "tblRecords.PermState, tblRecords.AgentFName, tblRecords.PermZip, tblRecords.ELName, tblRecords.ImpLName, " _
               & "tblRecords.EMI , tblRecords.ImpMI, tblRecords.EFName, tblRecords.ImpFName, tblRecords.EPhone, tblRecords.ImpGender, tblRecords.EAdd, " _
               & "tblRecords.ImpDOB, tblRecords.EApt, tblRecords.ImpPOB, tblRecords.CCDRecord, tblRecords.ECity, tblRecords.AFileNo, " _
               & "tblRecords.EState, tblRecords.Alias, tblRecords.EZip, tblRecords.ERelationship, tblRecords.PIERS, tblRecords.IDWLName, tblRecords.PLOTS, " _
               & "tblRecords.ACRQ, tblRecords.IDWMI, tblRecords.EVVE, tblRecords.IDWFName, tblRecords.IDWAdd, tblRecords.NLETS, tblRecords.IDWApt, " _
               & "tblRecords.TECS, tblRecords.Clear, tblRecords.IDWCity, tblRecords.IDWState, tblRecords.LEXIS, tblRecords.CCDText, tblRecords.IDWZip, " _
               & "tblRecords.CCDPerson, tblRecords.CCDPCQ, tblRecords.CCDFR, tblRecords.Internet, tblRecords.F46, tblRecords.Prosecuted, " _
               & "tblRecords.TECSLookout, tblRecords.USCISDocs, tblRecords.USCISDocType, tblRecords.USCISAppDate, tblRecords.USCISDecisionDate, " _
               & "tblRecords.USCISStatus " _
               & "FROM tblRecords IN 'MYDATABASEPATH' " _
               & "WHERE (((tblRecords.AppNo)= '" & Form_frmStatusUpdate.frmUpdateCaseData.Form.AppNo & "'))"
               
    Set rst3 = CurrentDb.OpenRecordset(str3)
    
    rst3!AppType = rst2!AppType
    rst3!FraudType = rst2!FraudType
    rst3!FraudReason = rst2!FraudReason
    rst3!DateROIRcvd = rst2!ROIReceivedDate
    rst3!ImpFName = rst2!ImpFName
    rst3!ImpLName = rst2!ImpLName
    rst3!ImpMI = rst2!ImpMI
    rst3!ImpGender = rst2!ImpGender
    rst3!ImpPOB = rst2!ImpPOB
    rst3!ImpDOB = rst2!ImpDOB
    rst3!AFileNo = rst2![A#]
    rst3!Alias = rst2!Alias
    rst3!TECSLookout = rst2!TECSLookout
    rst3!F46 = rst2!F46
    rst3!Prosecuted = rst2!Prosecuted
    rst3!USCISDocs = rst2!USCISDocs
    rst3!USCISDocType = rst2!USCISDocType
    rst3!USCISAppDate = rst2!USCISAppDate
    rst3!USCISDecisionDate = rst2!USCISDecisionDate
    rst3!USCISStatus = rst2!USCISStatus
    rst3!Disposition = rst2!Disposition
    
    rst1.Close
    rst2.Close
    rst3.Close
    
    Set rst1 = Nothing
    Set rst2 = Nothing
    Set rst3 = Nothing
        
    MsgBox "Case Updated!", vbInformation + vbOKOnly
    DoCmd.Close

Else

    DoCmd.SetWarnings False
    
    DoCmd.RunSQL "INSERT INTO tblRecords ( AppNo, AppType, FName, MI, LName, DOB, POB, SSN, POBSort, MailAdd, MailCity, MailState, MailZip, Source, " _
               & "ExecMonth, ExecYear, Agency, Region, ExecDate, DSOffice, FraudType, FraudReason, DateROIRcvd, ImpFName, ImpMI, ImpLName, ImpPOB, ImpDOB, " _
               & "ImpGender, AFileNo, Alias, F46, TECSLookout, Prosecuted, USCISDocs, USCISDocType, USCISAppDate, USCISDecisionDate, USCISStatus, Disposition ) " _
               & "IN 'MYDATABASEPATH' " _
               & "SELECT tblAppData.AppNo, tblAppData.AppType, tblAppData.FName, tblAppData.MI, tblAppData.LName, tblAppData.DOB, tblAppData.POB, " _
               & "tblDSReferralROI.SSN, tblCountries.POBSort, tblAppData.MailAdd, tblAppData.MailCity, tblAppData.MailState, tblAppData.MailZip, " _
               & "'DS Referral' AS Expr1, DatePart('m',[ExecDate]) AS Expr2, Format([ExecDate],'yyyy') AS Expr3, 'NPC' AS Expr4, 'Northeast Region' AS Expr5, " _
               & "tblAppData.ExecDate, tblDSReferralROI.DSOffice, tblDSReferralROI.FraudType, tblDSReferralROI.FraudReason, tblDSReferralROI.ROIReceivedDate, " _
               & "tblDSReferralROI.ImpFName, tblDSReferralROI.ImpMI, tblDSReferralROI.ImpLName, tblDSReferralROI.ImpPOB, tblDSReferralROI.ImpDOB, " _
               & "tblDSReferralROI.ImpGender, tblDSReferralROI.[A#], tblDSReferralROI.Alias, tblDSReferralROI.F46, tblDSReferralROI.TECSLookout, " _
               & "tblDSReferralROI.Prosecuted, tblDSReferralROI.USCISDocs, tblDSReferralROI.USCISDocType, tblDSReferralROI.USCISAppDate, " _
               & "tblDSReferralROI.USCISDecisionDate, tblDSReferralROI.USCISStatus, tblDSReferralROI.Disposition " _
               & "FROM (tblAppData INNER JOIN tblCountries ON tblAppData.POB = tblCountries.CntryCode) INNER JOIN tblDSReferralROI ON tblAppData.AppNo = tblDSReferralROI.[App#] " _
               & "GROUP BY tblAppData.AppNo, tblAppData.AppType, tblAppData.FName, tblAppData.MI, tblAppData.LName, tblAppData.DOB, tblAppData.POB, " _
               & "tblDSReferralROI.SSN, tblCountries.POBSort, tblAppData.MailAdd, tblAppData.MailCity, tblAppData.MailState, tblAppData.MailZip, " _
               & "'DS Referral', DatePart('m',[ExecDate]), Format([ExecDate],'yyyy'), 'NPC', 'Northeast Region', tblAppData.ExecDate, tblDSReferralROI.DSOffice, " _
               & "tblDSReferralROI.FraudType, tblDSReferralROI.FraudReason, tblDSReferralROI.ROIReceivedDate, tblDSReferralROI.ImpFName, tblDSReferralROI.ImpMI, " _
               & "tblDSReferralROI.ImpLName, tblDSReferralROI.ImpPOB, tblDSReferralROI.ImpDOB, tblDSReferralROI.ImpGender, tblDSReferralROI.[A#], " _
               & "tblDSReferralROI.Alias, tblDSReferralROI.F46, tblDSReferralROI.TECSLookout, tblDSReferralROI.Prosecuted, tblDSReferralROI.USCISDocs, " _
               & "tblDSReferralROI.USCISDocType, tblDSReferralROI.USCISAppDate, tblDSReferralROI.USCISDecisionDate, tblDSReferralROI.USCISStatus, " _
               & "tblDSReferralROI.Disposition " _
               & "HAVING (((tblAppData.AppNo)= '" & Form_frmStatusUpdate.frmUpdateCaseData.Form.AppNo & "'))"

    rst1.Close

    Set rst1 = Nothing

    DoCmd.SetWarnings True

    MsgBox "Case Updated!", vbInformation + vbOKOnly
    DoCmd.Save
    DoCmd.Close
 
End If

 

[pbaldy]

I don't see where you close the With block. That can cause this error.

 

[sherlocked]

Don't I feel like a dunderhead! That is precisely the problem.

Thank you so kindly!

 

[pbaldy]

Happy to help!