EOF or BOF error on recordset loop

[kclarico]

I can't figure out why I'm getting EOF or BOF error when looping through only a couple of times...

Do While rst.EOF = False
testNo_Rows = rst.RecordCount

iPhoneNumber = CStr(Replace(rst("SUBSCRIBER #"), "-", ""))
iAmountLoaded = rst("SUBSCRIBER TOTAL")
iAmountBilled = iAmountLoaded + acct_Amt_allocate
iStampDateTime = Now()

rstMobileDevices.Open ("select Phone_Number, User_Id, first(Mobile_Type) as Mobile_Type, max(STAMP_DATETIME) FROM tbl_Mobile_Devices WHERE Phone_Number = '" & iPhoneNumber & "' GROUP BY Phone_Number, User_Id")
If rstMobileDevices("Mobile_Type") = "Blackberry" Then iChgTypeId = 7 Else iChgTypeId = 6

rstUsers.Open ("select * from tbl_Users where User_Id = " & rstMobileDevices("User_Id"))
iUserId = rstUsers("User_Id")
iCostCtr = rstUsers("CostCtr_Id")

rstMobileDevices.Close
rstUsers.Close
testCount = testCount + 1
If rst.EOF = True Then Exit Do
If rst.BOF = True Then Exit Do
rst.MoveNext
If rst.EOF = True Then Exit Do
If rst.BOF = True Then Exit Do

Loop

 

[Golom]

You're getting an error or you're just hitting EOF?

Just having rst.EOF = True isn't an error. It's just telling you that you have processed all the records in the recordset. Did you "MoveFirst" before you entered the loop?

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[kclarico]

Thank-you for responding!!!!!

Yes, I used a MoveFirst...here's the beginning of the code...

Dim iPhoneNumber As String
Dim iAmountLoaded As Currency
Dim iAmountBilled As Currency
Dim iUserId As Integer
Dim iCostCtr As String
Dim iBillYear As Integer
Dim iBillMonth As Integer
Dim iEntryBy As String
Dim iStampDateTime As Date
Dim iEntryMethod As String
Dim iChgTypeId As Integer

rst.Open ("select * from [T-Mobile] where [SUBSCRIBER #]<>'311641764'")

iBillYear = iYear
iBillMonth = iMonth
iEntryBy = sUser
iEntryMethod = "A"

rst.MoveFirst

Dim testNo_Rows As Integer

Do While rst.EOF = False
testNo_Rows = rst.RecordCount

iPhoneNumber = CStr(Replace(rst("SUBSCRIBER #"), "-", ""))
iAmountLoaded = rst("SUBSCRIBER TOTAL")
iAmountBilled = iAmountLoaded + acct_Amt_allocate
iStampDateTime = Now()

rstMobileDevices.Open ("select Phone_Number, User_Id, first(Mobile_Type) as Mobile_Type, max(STAMP_DATETIME) FROM tbl_Mobile_Devices WHERE Phone_Number = '" & iPhoneNumber & "' GROUP BY Phone_Number, User_Id")
If rstMobileDevices("Mobile_Type") = "Blackberry" Then iChgTypeId = 7 Else iChgTypeId = 6

rstUsers.Open ("select * from tbl_Users where User_Id = " & rstMobileDevices("User_Id"))
iUserId = rstUsers("User_Id")
iCostCtr = rstUsers("CostCtr_Id")

rstMobileDevices.Close
rstUsers.Close
testCount = testCount + 1
rst.MoveNext

Loop

 

[Golom]

Your code looks OK ... except that not all cursor types in ADO will return a valid Recordcount.

What error are you getting?

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[kclarico]

Ok, it figures, after messing with this for 6 hours, finally figured out that the error was from the inner recordset, not the main one. I was troubleshooting the wrong recordset! I incorporated an if statement in case it doesn't find a record and now it works.

Thanks for your help!


Dim iPhoneNumber As String
Dim iAmountLoaded As Currency
Dim iAmountBilled As Currency
Dim iUserId As Integer
Dim iCostCtr As String
Dim iBillYear As Integer
Dim iBillMonth As Integer
Dim iEntryBy As String
Dim iStampDateTime As Date
Dim iEntryMethod As String
Dim iChgTypeId As Integer
Dim iComments As String


rst.Open ("select * from [T-Mobile] where [SUBSCRIBER #] not in ('311641764','Totals')")

iBillYear = iYear
iBillMonth = iMonth
iEntryBy = sUser
iEntryMethod = "A"

rst.MoveFirst

Do While rst.EOF = False

iPhoneNumber = CStr(Replace(rst("SUBSCRIBER #"), "-", ""))
iAmountLoaded = rst("SUBSCRIBER TOTAL")
iAmountBilled = iAmountLoaded + acct_Amt_allocate
iStampDateTime = Now()

rstMobileDevices.Open ("select Phone_Number, User_Id, first(Mobile_Type) as Mobile_Type, max(STAMP_DATETIME) FROM tbl_Mobile_Devices WHERE Phone_Number = '" & iPhoneNumber & "' GROUP BY Phone_Number, User_Id")
If rstMobileDevices.EOF = True Then
iUserId = "15757"
iCostCtr = "1319-temp"
iComments = "Mobile Number Missing"
Else
If rstMobileDevices("Mobile_Type") = "Blackberry" Then
iChgTypeId = 7
Else
iChgTypeId = 6
End If

rstUsers.Open ("select * from tbl_Users where User_Id = " & rstMobileDevices("User_Id"))

If rstUsers.EOF = True Then
iUserId = "15757"
iCostCtr = "1319-temp"
iComments = "User Record Missing"
Else
iUserId = rstUsers("User_Id")
iCostCtr = rstUsers("CostCtr_Id")
End If
rstUsers.Close
End If

rstMobileDevices.Close
rst.MoveNext

iComments = ""

Loop

 

[Zion7]

kclarico, if I may Ask, why do you prefix all your variables,
with "i"?
VERY UNINTUITIVE!and unconvential?

what does "i" stand for.

common way is to use letter of data type.
3 letters is usually most clear

Dim strPhoneNumber As String
Dim curAmountLoaded As Currency
Dim curAmountBilled As Currency
Dim intUserId As Integer
Dim strCostCtr As String
Dim intBillYear As Integer
Dim intBillMonth As Integer
Dim strEntryBy As String
Dim dteStampDateTime As Date
Dim strEntryMethod As String
Dim intChgTypeId As Integer
Dim strComments As String

Dim blnComplete As Boolean
Dim sngPercentage As Single
Dim lngPopulation As Long

 

[JerryKlmns]

kclarico

i also see that for every record in rst you have 2 recordsets openning and closing. Microsoft says about 40.000 times of that brings corruption! You do compact/repair often, dont't you? and wouldn't be faster instead of openning/closing to filter?

 

[Zion7]

I agree Jerry, I was wondering if DLookUp(), may even work?

...for the 2nd 2 recordsets, since no iteration is required?

 

[kclarico]

That's a good point. I'm new to VBA, so not sure if this is totally optimized. I thought about using DLookup, but didn't know how to accommodate the max(STAMP_DATETIME) criteria.

Any suggestions?

I may change the other recordset to a DLookup, though, and hopefully it will run faster. It's kind of slow right now.

Thanks!

Kristen

 

[Zion7]

right Kristen, now i remember why I didn't ask it right away.

You could make your recordset SQL a query, then use it
in DLookUp()

for ex, call your query "qryMobileDevices"

qryMobileDevices's SQL would be,

"select Phone_Number, User_Id, first(Mobile_Type) as Mobile_Type, max(STAMP_DATETIME) FROM tbl_Mobile_Devices GROUP BY Phone_Number, User_Id;"

You would call it,

Dim varMobileType As variant
varMobileType = DLooKUp("Mobile_Type","qryMobileDevices", _
"Phone_Number = '" & iPhoneNumber & "'")

If IsNull(varMobileType) Then
iUserId = "15757"
iCostCtr = "1319-temp"
iComments = "Mobile Number Missing"
Else
If varMobileType = "Blackberry" Then
iChgTypeId = 7
Else
iChgTypeId = 6
End If

 

[kclarico]

That's very good. I think I will use it... And hopefully it will speed up a bit.

Thank-you!

Kristen

 

[Zion7]

just for the record, the point is to do the same thing for
tblUsers....just in case I wasn't clear.