MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Recordset EOF on Filter "HELP" not true

  Asked By: Joao    Date: Nov 20    Category: MS Office    Views: 1025

I pass the recordset into a procedure... I do a recordcount inside the
procedure as well to verify I have data... 422 records to be exact and
when i apply the filter I get EOF... why There is data because I am
looking at the Database as well and see it...

here is the Beginning procedure: (where is passed to is below)

'Instantiate DB connection
Set cnDB = GetAHMCODB()

'Get list of active hotels
Set rsHotel = GetHotelListRS(cnDB, False)

'VBA.Right(s010Acct, 8)

Set cHotels = New Collection
If sFilter <> "" Then
rsHotel.Filter = "HotelCode2 = '" & sFilter & "'"
sHotelCode2 = rsHotel.Fields("HotelCode2").Value
cHotels.Add sHotelCode2
End If

Set rsActualMnthly = GetActualMonthlyRS(cHotels, iYear, iQuarter, , ,
"Revenue", "Rooms", cnDB)
Set rsDetailMnthly = GetDetailMonthlyRS(cHotels, iYear, iQuarter, , ,
"Revenue", "Rooms", cnDB)
Set rsBudgetMnthly = GetBudgetMonthlyRS(cHotels, , , iYear, iQuarter, ,
, "Revenue", "Rooms", cnDB)
Set rsForecastMnthly = GetForecastMonthlyRS(cHotels, , , iYear,
iQuarter, , , "Revenue", "Rooms", cnDB)
Set rsLastYrMnthly = GetActualMonthlyRS(cHotels, (iYear - 1), iQuarter,
, , "Revenue", "Rooms", cnDB)

iRecCount = rsActualMnthly.RecordCount

'If rsActualMnthly.EOF Then MsgBox ("TRUE")

'Iterate through hotels
While Not rsHotel.EOF
'Initialize variables
sHotelID = rsHotel.Fields("HotelID").Value
sHotelCode2 = rsHotel.Fields("HotelCode2").Value
sHotelName = rsHotel.Fields("Name").Value

'Create a new sheet from our template
Set oBook = Application.Workbooks.Add(sTemplateFile)

'UnProtect each worksheet
For Each oSheet In oBook.Worksheets
'UnProtect sheet
oSheet.Unprotect Password:=conSegReview
Next oSheet

'Set up Summary sheet with Hotel Name
Set oSheet = oBook.Worksheets("Summary")
oSheet.Range("A1").Value = sHotelName
oSheet.Range("A2").Value = iYear

'Pass in each sheet for updating
For Each oSheet In oBook.Worksheets
ssheet = oSheet.Name
Call UpdateWorksheet(oSheet, sHotelCode2, rsActualMnthly,
rsDetailMnthly, rsBudgetMnthly, rsForecastMnthly, rsLastYrMnthly, iYear,
Next oSheet

here is where the Recordset is passed to:

'Special Case here for thes 2 because of the months for the Q
Select Case oSheet.Name

Case Is = "Summary"

Call PutDatesOnSumTab(oSheet, iQuarter, oRange)

Case Is = "Rack Corp"

Call PutDatesOnRackTab(oSheet, iQuarter, oRange)

iMonthFrom = GetMonthsForQtr(iQuarter, iMonthFrom)

Case Else

iMonthFrom = GetMonthsForQtr(iQuarter, iMonthFrom)

End Select
'Exits to go to the next sheet... no data need for this sheet
If oSheet.Name = "Summary" Then Exit Function

s199Acct = Get199Acct(oSheet, s199Acct)
s010Acct = Get010Acct(oSheet, s010Acct)

'Set the first Row value
iRow = 9

For iMonth = iMonthFrom To (iMonthFrom + 2)

iRecCount = rsActualMnthly.RecordCount

'Get Rooms Rented for hotel, month and account
rsActualMnthly.Filter = "HotelCode2 = '" & sHotelCode2 & "' And
TranMM = " & iMonth & " AND AccountCode = '" & s199Acct & "'"
'iRecCount = rsActualMnthly.RecordCount
If Not rsActualMnthly.EOF Then
i199RmsSold = rsActualMnthly.Fields("SumOfAmount").Value
oSheet.Cells(iRow, ActualRms_Col) = i199RmsSold
i199RmsSold = 0
'Get Rooms Rented for hotel, month and account
rsDetailMnthly.Filter = "HotelCode2 = '" & sHotelCode2 & "' And
TranMM = " & iMonth & " And AccountCode = '" & s199Acct & "'"
If Not rsDetailMnthly.EOF Then i199RmsSold =
oSheet.Cells(iRow, ActualRms_Col) = i199RmsSold
i199RmsSold = 0
End If



No Answers Found. Be the First, To Post Answer.

Didn't find what you were looking for? Find more on Recordset EOF on Filter "HELP" not true Or get search suggestion and latest updates.