Navigate record sets

[pedcab]

I have encountered a problem in VB when I am trying to navigate the ADO record set

after connection and before going into the loop to movenext,etc

I have a movefirst. All this within a WITH statement.

It finds the first record listed in the combo box but when I select for another, it crashes.

 

[zemp]

Post your code and identify the line the error is occuring on and which error is being thrown. Thanks and Good Luck!

zemp

 

[PraveenMenon]

If u are opening the rst by executing an SQL statement, (as in "Cn.Execute strSQL&quot, the recordset returned is forward only. Anyway still i feel zemp is right.. post the code! All the Best
Praveen Menon
pcmin@rediffmail.com

 

[pedcab]

this is the coding. It just does not allow me to select a second element.
Private Sub cmbCoopName_Click()
'On CoopNom selection, need to load all the data in the fields
'on the Search form.

Dim Rnum As Integer

With AdoCoop.Recordset 'Look into the CooperativeInformation table
If .BOF Then
.MoveFirst
Else
.MoveFirst
End If

Do Until .EOF
If ![CoopNom] = cmbCoopName.Text Then 'verify that the name in the combo box exist in the database
'if it does, all field connect to the ADO will load their
'proper information from that record

'The region cannot be link with a DataSource
'since what is displayed comes from the Region Table.
Rnum = ![Régionnum]
With AdoRegion.Recordset ' Look into the Region table
.MoveFirst
Do Until .EOF
If Rnum = ![Regionnum] Then
cmbCoopRegion.Text = ![Region]
Exit Do
Else
.MoveNext
End If
Loop
End With
Exit Do

Else
.MoveNext
End If
Loop
.MoveFirst
End With

'Convert 0 and 1 to user-friendly interface
'for the Assurance
If ComboAssurance.Text = 0 Then
ComboAssurance.Text = "Non"
Else
ComboAssurance.Text = "Oui"
End If

'Convert 0 and 1 to user-friendly interface
'for the AFIC
If ComboAFIC.Text = 0 Then
ComboAFIC.Text = "Non"
Else
ComboAFIC.Text = "Oui"
End If

'Convert 0 and 1 to user-friendly interface
'for the Animaux Permis
If comboAnimal.Text = 0 Then
comboAnimal.Text = "Non"
Else
comboAnimal.Text = "Oui"
End If


End Sub

Private Sub cmbCoopRegion_Click()

Dim R As String
'The region cannot be link with a DataSource
'since what is displayed comes from the Region Table.
R = cmbCoopRegion.Text

With AdoRegion.Recordset ' Look into the Region table
.MoveFirst

Do Until .EOF
If R = ![Region] Then
MsgBox "take all from that region"
'Bring out all Coop under that region.
Exit Do
Else
.MoveNext
End If
Loop
End With
End Sub

Thanks

 

[zemp]

Looking at your code it seems that you are exiting the entire loop if the first record returns a true on the if statement.

Do Until .EOF
If ![CoopNom] = cmbCoopName.Text Then
... some code
Exit Do
Else
.MoveNext
End If
Loop

Assuming that the first name does exist you are done and gone from the loop. I would try removing the 'exit do' and the 'else' and move the '.movenext' outside the if statement. Try,

Do Until .EOF
If ![CoopNom] = cmbCoopName.Text Then
... some code
Endif
.MoveNext
Loop

This way you should loop through the entire recordset. Thanks and Good Luck!

zemp