Hi,
I have a module which parses each row of an input table to see that it is in the correct format. Details of rows which do not conform are written to an error table for review. The module works great. One problem. The database is used by several different users, who access it on a common server. So far it has worked correctly for four different users, but has not worked for one particular user.
I have determined the problem for this user but not the reason. When ReadRst is opened EOF is immediately true even though the SQL(changes daily with a date function) is the same as it would be for any other user. Plus when I take the SQL string into a standard query and execute populated result set is returned.
I have no idea as to the cause but know that it cant it be logic related and that it must have something to do with the environment. Is there some user configuration that affects how record sets run ?
Confused
Mordja
Public Function parseAdjustments() As Boolean
Dim ReadRst As ADODB.Recordset
Dim WriteRst As ADODB.Recordset
Dim cmd As ADODB.Command
Dim cnn As ADODB.Connection
Dim count As Integer
Dim strFromDate, strToDate, strType, strShortDesc, strRollUp As String
Dim strSubRollUp, strAllocationGroup, StrUsdMtdPandL, strManagementSummary As String
Dim strAddedBy, strDateAdded, strTAPSAccount, strCcyCode As String
Dim strCompanyCode, strCusip As String
Dim boolError, boolReturn As Boolean
'Iniatilise
Set cnn = New ADODB.Connection
Set ReadRst = New ADODB.Recordset
Set WriteRst = New ADODB.Recordset
'Clear existing data
cnn.Open CurrentProject.Connection
cnn.Execute "Delete * From Sterling_tblAdjustmentErrors"
'Open Record Set to Read ClientCode and CompanyCode
ReadRst.ActiveConnection = cnn
ReadRst.Open "SELECT * From Sterling_tblSterlingBulkAdjustment WHERE fromDate = #" & LastUpdateDay & "#"
'Open Write Record Set to write results
WriteRst.ActiveConnection = cnn
WriteRst.CursorType = adOpenKeyset
WriteRst.LockType = adLockOptimistic
WriteRst.Open "Select * From Sterling_tblAdjustmentErrors"
boolReturn = True
count = 1
'Loop through ReadSet Parse Columns
Do Until ReadRst.EOF
boolError = False
'parse fromDate
If (ReadRst!fromDate >= LastUpdateDay) And (Month(ReadRst!fromDate) = Month(LastUpdateDay)) Then
strFromDate = "Correct"
Else
strFromDate = "Error"
boolError = True
End If
'parse toDate
If (ReadRst!toDate >= LastUpdateDay) And (Month(ReadRst!toDate) = Month(LastUpdateDay)) And _
(ReadRst!toDate >= ReadRst!fromDate) Then
strToDate = "Correct"
Else
strToDate = "Error"
boolError = True
End If
'parse type
If (ReadRst!Type = "Monthly") Or (ReadRst!Type = "Daily") Then
strType = "Correct"
Else
strType = "Error"
boolError = True
End If
'parseShortDesc
If (ReadRst!shortDesc <> "") Then
strShortDesc = "Correct"
Else
strShortDesc = "Error"
boolError = True
End If
'parseRollup
If DLookup("[rollup]", "tblRollUp", "[rollup] = '" & ReadRst!rollUp & "'") <> "" Then
strRollUp = "Correct"
Else
strRollUp = "Error"
boolError = True
End If
'parseSubRollUp
If ((ReadRst!rollUp = "Long Financing Revenue") And (ReadRst!subRollUp = " ")) Or _
((ReadRst!rollUp = "Non Conventional Trading") And (ReadRst!subRollUp = " ")) Or _
DLookup("[subrollup]", "tblSubRollUp", "[subrollup] = '" & ReadRst!subRollUp & "'") <> "" Then
strSubRollUp = "Correct"
Else
strSubRollUp = "Error"
boolError = True
End If
'parseAllocationGroup
If (Len(ReadRst!allocationGroup) = 1 And ReadRst!allocationGroup Like "[A-Za-z]") Then
strAllocationGroup = "Correct"
Else
strAllocationGroup = "Error"
boolError = True
End If
'parseUsd
'do nothing
'parseManagementSummary
If ReadRst!managementSummary = "Y" Or ReadRst!managementSummary = "N" Then
strManagementSummary = "Correct"
Else
strSubRollUp = "Error"
boolError = True
End If
'parseAddedBy
If ReadRst!addedBy <> "" Then
strAddedBy = "Correct"
Else
strAddedBy = "Error"
boolError = True
End If
'parseDateAdded
If ReadRst!dateAdded = DateValue(Now()) Then
strDateAdded = "Correct"
Else
strDateAdded = "Error"
boolError = True
End If
'parseNote
'do nothing
'parseTAPSAccount
If Len(ReadRst!TAPSAccount) = 8 Then
strTAPSAccount = "Correct"
Else
strTAPSAccount = "Error"
boolError = True
End If
'parseCcyCode
If Len(ReadRst!ccyCode) = 3 And Mid(ReadRst!ccyCode, 1, 1) Like "[A-Za-z]" And _
Mid(ReadRst!ccyCode, 2, 1) Like "[A-Za-z]" And _
Mid(ReadRst!ccyCode, 3, 1) Like "[A-Za-z]" Then
strCcyCode = "Correct"
Else
strCcyCode = "Error"
boolError = True
End If
'parseMxAMount
'do nothing
'parseCompanyCode
If Len(ReadRst!CompanyCode) = 4 And Mid(ReadRst!CompanyCode, 1, 1) Like "[0-9]" And _
Mid(ReadRst!CompanyCode, 2, 1) Like "[0-9]" And _
Mid(ReadRst!CompanyCode, 3, 1) Like "[0-9]" And _
Mid(ReadRst!CompanyCode, 4, 1) Like "[0-9]" Then
strCompanyCode = "Correct"
Else
strCompanyCode = "Error"
boolError = True
End If
'parseCusip
If Len(ReadRst!Cusip) = 9 Then
strCusip = "Correct"
Else
strCusip = "Error"
boolError = True
End If
If boolError Then
boolReturn = False
WriteRst.AddNew
WriteRst!fromDate = strFromDate
WriteRst!toDate = strToDate
WriteRst!Type = strType
WriteRst!shortDesc = strShortDesc
WriteRst!rollUp = strRollUp
WriteRst!subRollUp = strSubRollUp
WriteRst!allocationGroup = strAllocationGroup
WriteRst!usdMtdPandL = "Correct"
WriteRst!managementSummary = strManagementSummary
WriteRst!addedBy = strAddedBy
WriteRst!dateAdded = strDateAdded
WriteRst!Note = "Correct"
WriteRst!TAPSAccount = strTAPSAccount
WriteRst!ccyCode = strCcyCode
WriteRst!MXAmount = "Correct"
WriteRst!CompanyCode = strCompanyCode
WriteRst!Cusip = strCusip
WriteRst!rowNumber = count
WriteRst.Update
End If
count = count + 1
ReadRst.MoveNext
Loop
ReadRst.Close
WriteRst.Close
cnn.Close
Set ReadRst = Nothing
Set cnn = Nothing
Set WriteRst = Nothing
parseAdjustments = boolReturn
End Function
