SQL Errror 3021

[VSolano]

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

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

 

[June7]

Why do you need to build table with code?

If you want to provide db for analysis, follow instructions at bottom of my post.

 

[vba_php]

Why do you need to build table with code? .

exactly.. creating a table and appending fields with code is not usually necessary. but your code is missing 2 things anyway I think. before this:

RS2.FindFirst "[PlanCarrier] & [EntityID] & [benefitsid] & [PlanType] & [plandatailid]='" & STSearch & "'"

you need this I think:

RS2.movelast
RS2.movefirst

when a recordset is opened, there are no records visible unless you "movelast" first (or maybe 1 record is visible only, I can't remember). that would explain y u r getting the error. additionally, the code is referring to field names just using [] braces and not qualifiers like a "." period or an "!" exclamation point like you have written in this line:

STSearch = rst!PlanCarrier & rst!EntityID & rst!benefitsid & rst!PlanType & rst!plandatailid

I don't think the compiler will recognize that as valid. try compiling the vba project and see if it errors out on that line to see if this is true.

 

[June7]

@vba_php, MoveFirst and MoveLast are not needed. Not sure what line your last comments about brackets refer to. I really don't see anything wrong with code syntax.

 

[vba_php]

Not sure what line your last comments about brackets refer to.

he wrote:

RS2.FindFirst "[PlanCarrier] & [EntityID] & [benefitsid] & [PlanType] & [plandatailid]='" & STSearch & "'"

like I said, I'm not sure if my points are accurate, both they might be. it's worth a check, that's all.

 

[June7]

Nothing wrong with that syntax. And yes, I have checked.

 

[VSolano]

I want to create and delete a table so I can post value from another table and then create a report with the final table value.
My issue is populating the value of each month to the table recently created

 

[VSolano]

he wrote:

RS2.FindFirst "[PlanCarrier] & [EntityID] & [benefitsid] & [PlanType] & [plandatailid]='" & STSearch & "'"

like I said, I'm not sure if my points are accurate, both they might be. it's worth a check, that's all.

I have the same code in anther place and it works fine

 

[VSolano]

My approach is looking the matching value in the original table against the new table and then post the invoice amount in the month column on the new table

 

[theDBguy]

Hi. Pardon me for jumping in, but what is error 3021?

 

[Gasman]

I have the same code in anther place and it works fine

That would infer the data is at fault then, would it not.?
Why not check for EOF or BOF.?
Whats does the rs2 recordcount show in debug with breakpoint.?

 

[Gasman]

Hi. Pardon me for jumping in, but what is error 3021?

No current record

 

[theDBguy]

No current record

Thanks. In that case, maybe use rs2.Requery before using rs2.FindFirst or don't open rs2 until after running the SQL that populates the table. Right now, it looks like the code may be opening an empty table and then populating it. If so, it may be necessary to refresh the recordset to "see" the inserted data.

 

[Gasman]

Thanks. In that case, maybe use rs2.Requery before using rs2.FindFirst or don't open rs2 until after running the SQL that populates the table. Right now, it looks like you may be opening an empty table and then populating it.

Perhaps the INSERT sql should execute before opening as a recordset?

Edit: Sorry, just seen that DBG suggested the same thing.

 

[vba_php]

Perhaps the INSERT sql should execute before opening as a recordset?

it does:

      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

 

[VSolano]

Thanks all for your input.

I requery the recordset and I not longer has that error.

 

[VSolano]

Quick Question.

When I am posting the value how do I tell vba where to post the value because the value is just posting to the end of the table no to the corresponding record

 

[theDBguy]

Thanks all for your input.

I requery the recordset and I not longer has that error.

Hi. Glad to hear that helped. Cheers!

 

[theDBguy]

Quick Question.

When I am posting the value how do I tell vba where to post the value because the value is just posting to the end of the table no to the corresponding record

Hi. Can you post the part of the code where you're using the value and putting it into the table?

 

[June7]

I still don't understand need to build table as opposed to using a query.

Again, if you want to provide db for analysis, follow instructions at bottom of my post.