Error 3265 Item Not In Collection When Loading Table With .AddNew (1 Viewer)

[JAB]

I am populating a table using vba and the routine fails on the field "DrCrInd" with the error 3265. If I comment out this field under the .AddNew the routine works fine. The field name spelling has been checked and it is correct. I even renamed the field in both tables and still the same error. I also put in a print statement ahead of the .AddNew and the values for "DrCrInd" are there so I am baffled as to why this is happening. I am using Office 365 and in both tables the field is ShortText with a length of 1.

Background: I have summary data from SAP where each record stores values and quantities in separate fields for each of the 12 monthly periods (Value 1, Value 2, Value n, etc...). This routine will add a fiscal period field "FP" and create a record for each period that contains either a value or quantity.

Thank you in advance.

Here is the code:

Set dbs = CurrentDb

strSQL = "SELECT tblSAPData.SAPTable, tblSAPData.ObjNumber, tblSAPData.FY, " _
& "tblSAPData.ValueType, tblSAPData.Version, tblSAPData.CostElem, " _
& "tblSAPData.BusTrans, tblSAPData.DrCrInd, tblSAPData.Value1, " _
& "tblSAPData.Qty1 " _
& "FROM tblSAPData;"

Set rstqry = dbs.OpenRecordset(strSQL)

rstqry.MoveLast
rstqry.MoveFirst
Set rsttbl = dbs.OpenRecordset("tblSAPDataTransposed")

'Add the records to tblSAPDataTransposed.
For i = 1 To 20 'rstqry.RecordCount
If rstqry.EOF Then
GoTo SkipLoad
End If
For c = 1 To 12
Debug.Print rstqry.Fields("SAPTable"), rstqry.Fields("ObjNumber"), rstqry.Fields("DrCrInd"), c
If rstqry.Fields("Value" & c) = 0 Then
If rstqry.Fields("Qty" & c) = 0 Then
GoTo SkipPeriod
End If
End If
With rsttbl
.AddNew
!SAPTable = rstqry.Fields("SAPTable")
!ObjNumber = rstqry.Fields("ObjNumber")
!FY = rstqry.Fields("FY")
!ValueType = rstqry.Fields("ValueType")
!Version = rstqry.Fields("Version")
!CostElem = rstqry.Fields("CostElem")
!BusTrans = rstqry.Fields("BusTrans")
!DbCrInd = rstqry.Fields("DrCrInd")
!FP = c
!Amt = rstqry.Fields("Value" & c)
!Qty = rstqry.Fields("Qty" & c)
.Update
End With
SkipPeriod:
Next c
rstqry.MoveNext
Next i
'Close and clean up.
rsttbl.Close
Set rsttbl = Nothing
SkipLoad:
'Display message box when done.
Beep
MsgBox "SAP data has been transposed and added to tblSAPDataTransposed."

 

[theDBguy]

!DbCrInd = rstqry.Fields("DrCrInd")

Just checking, is there a typo in there?

 

[JAB]

That was the first thing I checked for. The spelling is correct on both sides (a capital "eye" not a lower case "ell"). And I tried changing the field name in both tables to just "DrCr" and "DrCrNew" to no avail. Another point to add is when I step through the code and hover over each of the fields under .AddNew, the value for DrCrInd displays just like all of the other fields.

 

[theDBguy]

The spelling is correct on both sides (a capital "eye" not a lower case "ell").

If you say so, but I see a lower case "bee" on the left side and a lower case "arr" on the right side.

 

[MajP]

as @theDBguy stated this looks strange

!DbCrInd = rstqry.Fields("DrCrInd")

 

[JAB]

DOH! I was so focused on the "Ind" part that I just didn't notice the Db vs Dr. Works now. Thank you for the extra set of eyes.

 

[theDBguy]

DOH! I was so focused on the "Ind" part that I just didn't notice the Db vs Dr. Works now. Thank you for the extra set of eyes.

You're welcome. Glad we could assist. Good luck with your project.