I need some advice regarding this code that is erroring out on the not match statement. It is giving up error 3021 on the rs2.findfirst line and I can not figure out what is wrong. I am just trying fill value for each month. Like a crosstab report
Code:
On Error GoTo ErrorHandle
Set db = CurrentDb
Dim SQL As String
Dim RS2 As Recordset
DelTbl ("tbcolumnreport")
Set tdef = db.CreateTableDef("tbcolumnreport")
tdef.Fields.Append tdef.CreateField("PlanCarrier", dbInteger)
tdef.Fields.Append tdef.CreateField("EntityID", dbInteger)
tdef.Fields.Append tdef.CreateField("CompanyID", dbInteger)
tdef.Fields.Append tdef.CreateField("Benefitsid", dbInteger)
tdef.Fields.Append tdef.CreateField("Plantype", dbInteger)
tdef.Fields.Append tdef.CreateField("plandatailid", dbInteger)
tdef.Fields.Append tdef.CreateField("Jan", dbDouble)
tdef.Fields.Append tdef.CreateField("Feb", dbDouble)
tdef.Fields.Append tdef.CreateField("Mar", dbDouble)
tdef.Fields.Append tdef.CreateField("Apr", dbDouble)
tdef.Fields.Append tdef.CreateField("May", dbDouble)
tdef.Fields.Append tdef.CreateField("Jun", dbDouble)
tdef.Fields.Append tdef.CreateField("Jul", dbDouble)
tdef.Fields.Append tdef.CreateField("Aug", dbDouble)
tdef.Fields.Append tdef.CreateField("Sept", dbDouble)
tdef.Fields.Append tdef.CreateField("Oct", dbDouble)
tdef.Fields.Append tdef.CreateField("Nov", dbDouble)
tdef.Fields.Append tdef.CreateField("Dec", dbDouble)
Dim STSearch As String
db.TableDefs.Append tdef
db.TableDefs.Refresh
Set rst = db.OpenRecordset("tbbilling", dbOpenDynaset, dbSeeChanges)
Set RS2 = db.OpenRecordset("tbcolumnreport", dbOpenDynaset, dbSeeChanges)
SQL = "INSERT INTO tbcolumnreport ( PlanCarrier, EntityID, CompanyID, benefitsid, PlanType, plandatailid )" _
& " SELECT DISTINCT tbbilling.PlanCarrier, tbbilling.EntityID, tbbilling.companyid, tbbilling.benefitsid, tbbilling.PlanType, tbbilling.plandatailid " _
& "FROM tbbilling;"
DoCmd.RunSQL SQL
Do While rst.EOF = False
STSearch = rst!PlanCarrier & rst!EntityID & rst!benefitsid & rst!PlanType & rst!plandatailid
'
' I need to search RS2 to see if the value is there
RS2.FindFirst "[PlanCarrier] & [EntityID] & [benefitsid] & [PlanType] & [plandatailid]='" & STSearch & "'"
If RS2.NoMatch = False Then
Select Case month(rst!Invoicedate)
Case 1
RS2.AddNew
RS2!Jan = rst!Invoiceamount
RS2.Update