EOF vs Recordcount?? 1

[lachesis]

I have a form with a tab control (each tab reprsenting a country) and two lists: Size and Colour.

When the user selects size and colour combinations, the selected size and colour list ids are populated to their respective temp tables.

The temp values are later recalled when the user clicks a Country tab: Size and Colour lists update to reflect data stored in the temp tables.

My problem is when there are no records in the temp tables, I get "No current record" errors. It is possible to have Country tabs without Size/Colour data.

But in order to reset the appearance of listSize or listColour, I have to perform a recordset count to establish how many times to loop thru the code.

It's a catch 22 to me. What am I missing? Maybe I'm just not seeing the wood for the trees...

Here's my code to repopulate the listSize control after the AU(stralia) tab is selected:
[blue]
Dim ctl1 as Control
Dim intRow as Integer, counter as Integer, searchID as Integer
...

Set rs = CurrentDb.OpenRecordset("SELECT * FROM tbltmpSize WHERE [toTab] = 'AU' ORDER BY [recid];&quot

' count to establish recordset count - used in counter loop
rs.MoveLast
rs.MoveFirst
If rs.RecordCount > 0 Then ' do if records

' update listSize appearance with stored selections
Set ctl1 = me.listSize
For counter = 1 To rs.RecordCount
searchID = rs![recID]
For intRow = 0 To ctl1.listCount - 1
If ctl1.Column(0, intRow) = searchID Then
ctl1.Selected(intRow) = True
Exit For
End If
Next intRow
rs.MoveNext
Next counter

Else:
...

End If[/blue]


??!! L.

 

[gusbrunston]

Hi, L:
I have a hard time reading blue letters on a blue background. So I took the liberty of surrounding your code in the proper TGML tags to make it easier to read. Maybe this will help others who know more than I do to help you figure out what you need to do. Hope you're not offended.

Dim ctl1 as Control
Dim intRow as Integer, counter as Integer, _
  searchID as Integer...
Set rs = CurrentDb.OpenRecordset("SELECT * " _
  & "FROM tbltmpSize " _
  & "WHERE [toTab] = 'AU' " _
  & "ORDER BY [recid];")
  ' count to establish recordset count - 
  'used in counter loop
rs.MoveLast
rs.MoveFirst
If rs.RecordCount > 0 Then ' do if records
  ' update listSize appearance with"
  ' stored selections
  Set ctl1 = me.listSize
  For counter = 1 To rs.RecordCount
    searchID = rs![recID]
    For intRow = 0 To ctl1.listCount - 1
     If ctl1.Column(0, intRow) = searchID Then
      ctl1.Selected(intRow) = True
      Exit For
     End If
    Next intRow
    rs.MoveNext
  Next counter
Else:
   ...
End If

Cheers,


[tt]Gus Brunston - Access2000(DAO)
Skill level based on 1-10: 7, on the way to 6, and beyond!
Webmaster: www.rentdex.com[/tt]

 

[Edski]

lachesis,
If there are no records in RS then rs.MoveLast and rs.MoveFirst will give errors. You need to trap it before these line, eg:

If RS.EOF then Exit Sub

or something ilke that. I don't know why you couldn't figure it out yourself, especially since you have the answer in the title of your post !!

Cheers.

 

[Rolliee]

L & E,

I would do a

if rs.eof and rs.bof to find a vacuous recordset. I would also go thru a recordset like this


rs.movelast:rs.movefirst
do while not rs.eof

rs.movenext
loop


rollie@bwsys.net

 

[lachesis]

Ed:
Exit Sub won't do - that would escape from the rest of the conditional tab tests. This procedure is for each tab so

If tab1
... do the proc
elseif tab2
... do the proc
elseif tab3
.... and so on

Rolliee:
Your suggestion is welcome but the original error trap with Movefirst: movelast is not avoided on an empty table.

 

[Rolliee]

After an rs.eof and rs.bof is true, you must exit the sub and not do the rs.move's

 

[lachesis]

Rolliee:
Can't be doing that 'Exit Sub' for the reasons I originally posted here, and latterly for my further explanation to Ed. Nevertheless, I've come up with a workaround.

The rs.MoveLast: rs.MoveFirst is not required at all, in the context of your example. I don't know enough about Access, but Im assuming the recordset opened from a table, always opens at the first record. That being so, I can leave out the rs.recordcount to populate my counter, and just just your Do..Loop code.

Thanks both... got to see the wood again!
L.

 

[Edski]

lachesis,
the Exit Sub was just a suggestion. You didn't have to use it. The main point was to use rs.EOF before the rs.MoveFirst command.

Rollie,
Seeing as lachesis didn't give you a star, I will. Can you explain why you need to check RS.BOF if you issue the RS.EOF command, i.e. why do you need both?

 

[lachesis]

Ed: Don't really need you to state the obvious now, do we? I do give credit where its due, but Ive been too busy with this problem, amongst others.