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."
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."