Code not recognising correct fields in a sharepoint list (1 Viewer)

[Drand]

Hi all

I hope I am posting this in the correct part of the forum.

I have an application which I designed thinking the back end was to be deployed to an sql server. I now find they want it on Sharepoint which I have never used before.

I have managed to split the back end to a sharepoint site. All my queries are working perfectly however some code is not.

I have the following code which identifies Null records in the tblConsolRawData based on a set of rules in tblDataRules (which basically determines if data is required for a specific field for each country). This works perfectly outside of Sharepoint.

Public Function fnRunRule()
Dim db As DAO.Database
Dim rRule As DAO.Recordset
Dim rSource As DAO.Recordset
Dim rTarget As DAO.Recordset
Dim fldName As String, tfValue As Boolean
Dim varValue As Variant
Dim i As Integer
Set db = CurrentDb
db.Execute "delete * from tblDump;"
Set rRule = db.OpenRecordset("tblDataRules", dbOpenSnapshot, dbReadOnly)
Set rTarget = db.OpenRecordset("tblDump", dbOpenDynaset)
With rRule
    If Not (.BOF And .EOF) Then
        .MoveFirst
    End If
    Do Until .EOF
        Set rSource = db.OpenRecordset( _
                "select * from tblConsolRawData " & _
                "where CountryCode = " & !CountryCode, dbOpenSnapshot, dbReadOnly)
        With rSource
            If Not (.BOF And .EOF) Then
                .MoveFirst
            End If
            On Error Resume Next
            
                Do Until .EOF
                
                For i = 5 To rRule.Fields.Count - 1
                    fldName = rRule.Fields(i).Name
                    tfValue = rRule.Fields(i)
                    varValue = .Fields(fldName).Value
                    If Err Then
                        Err.Clear
                    Else
                        If tfValue And IsNull(varValue) Then
                            rTarget.AddNew
                            rTarget("id") = .Fields("id")
                            rTarget("CountryCode") = !CountryCode
                            rTarget.Update
                            Exit For
                        End If
                    End If
                Next
                .MoveNext
            Loop
            On Error GoTo 0
            .Close
        End With
        .MoveNext
    Loop
    .Close
End With
rTarget.Close
Set rRule = Nothing
Set rSource = Nothing
Set rTarget = Nothing
Set db = Nothing

                        
                        
                
End Function

The issue is the referencing to the column numbers at

" For i = 5 To rRule.Fields.Count - 1
fldName = rRule.Fields(i).Name
tfValue = rRule.Fields(i)
varValue = .Fields(fldName).Value"
where I am getting field names which seem to generated by sharepoint such as "item" which is not part of my tables.

I suspect that I need to set rRule etc to the sharepoint list but have no idea how to progress this.

Would appreciate any guidance on this.

Many thanks

 

[theDBguy]

Maybe you could step through the code and determine how many columns the SharePoint list has and then adjust the index number for your loop. Just a thought...

 

[Drand]

Thanks for that. I tried that but to no avail.