Option Button/Recordset

[Maypen]

Hi Guys, I have a large table in a database.I would like to populate a datagrid so that rows can be selected by scrolling. This would be too large.

I therefore have 5 option buttons for which i will create 5 recordset that i need to load into a datagrid depending on selection. I am doing this to reduce the size of the datagrid that needs to be scrolled.
I know the buttons are in an array but how do you point to the correct recordset. Is this the correct way of going about it?

'the option array
Private Sub Option1_Click(Index As Integer)
Select Case Index
Case 0:
Case 1:
Case 2:
Case 3:
Case 4:

End Select
End Sub


'the recordset

Private Sub Form_Load()

Set selectStock = New ADODB.Connection

selectStock.ConnectionString = _
"Provider = Microsoft.Jet.OLEDB.4.0; Data Source=C:\My Documents\GP project\gpframings.mdb"

selectStock.Open
Set rsSelect = New ADODB.Recordset
selectSql = "Select stockcode, stockdescription, price From stock Where category = glass"
'stockRS.Source = rSql

With rsSelect
.ActiveConnection = selectStock
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockOptimistic
.Open selectSql

End With

If rsSelect.BOF = True Or rsSelect.EOF = True Then
Exit Sub

End If

Set DataGrid1.DataSource = rsSelect
DataGrid1.Refresh

End Sub

'Cheers!!

 

[BobRodes]

Of course, your code as shown will do nothing in the select statement. I'm assuming that you are planning to put different versions of your selectSql variable in each of the case blocks. In which case, that's one way to go about it. Another way would be to open 5 recordset objects at load time, create a 6th, and set it equal to whichever of the 5 that suits whenever you like. This adds to memory overhead, but lessens processing overhead.

Also, I would change this:

If rsSelect.BOF = True Or rsSelect.EOF = True Then
        Exit Sub
        
End If

Set DataGrid1.DataSource = rsSelect
        DataGrid1.Refresh

End Sub

to this:

If not (rsSelect.BOF And rsSelect.EOF) Then
   Set DataGrid1.DataSource = rsSelect
   DataGrid1.Refresh
End If
End Sub

The point is that the only way you can be sure in any context that there is an empty recordset is for both BOF and EOF to be true. It's true that in a freshly opened recordset one or the other will be true only if the recordset is empty, since you haven't navigated yet, but it's more precise to evaluate both. Your if statement could be true in conditions other than an empty recordset, if context were eliminated, and minimizing the implications of context is a standard defensive programming technique.

HTH

Bob

 

[Maypen]

Thank you Bob for your reply.

1)Your assumption was correct. But i was uncertain about the syntax required.

2)I dont fully understand your second solution. Why would you create a sixth and how would you "set it equal to whichever of the 5 that suits whenever you like".

3) Cheers for your explanation of BOF and EOF. I will adopt your method.

Cheers

 

[BobRodes]

Sorry to be slow to answer, been sick.

Here's an example of what I mean:

Dim cn as ADODB.Connection
Dim rs(4) as ADODB.Recordset
Dim rsWorking as ADODB.Recordset
Dim i as Integer

Public Sub Form_Load
Dim strSQL(4) as String
Set cn = New ADODB.Connection
cn.ConnectionString = _
   "Provider = Microsoft.Jet.OLEDB.4.0; Data Source=C:\My Documents\GP project\gpframings.mdb"
cn.Open
strSQL(0) = "Select this"
strSQL(1) = "Select that"
strSQL(2) = "Select the other"
'etc

for i = 0 to 4
   Set rs(i) = New ADODB.Recordset
   With rs(i)
      .CursorLocation = adUseClient 'automatically static cursortype
      'you don't need to specify a locktype either
      .Open strSQL(i), cn  'I prefer to pass the connection rather than set the property, since state implies overhead
      If .BOF And .EOF then
          'handle the empty recordset condition
      End If
   End With
Next i
Set rsWorking = rs(0) 'or whichever one you want to start with
Set DataGrid1.DataSource = rsWorking
DataGrid1.Refresh
End Sub

Private Sub Option1_Click(Index As Integer)
set rsWorking = rs(index)
DataGrid1.Refresh
End Sub

HTH

Bob

 

[Maypen]

Hi Bob
Thank you for your answer. It looks like exactly what i was after and i will get back to you later after trying it. Cheers again.