Using For Loop through an access Database

[gilsygirl]

Hello everyone

Am using a For Loop to access records from my database but the code opens instead of a specific record base on values passed by the user. These values a fields in the database which i want to make keys.

This a bit of the code
Vb 6 and Microsof Access

code Vb:

If rs.RecordCount = 0 Then Exit Sub
rs.MoveFirst
For i = 1 To rs.RecordCount ' Read all the records from database

cbosch = rs("CentNo")
Cand_No = rs("IndexNo")
Sex = rs("Sex")
DOB = rs("DOB")
Cnd_Name = rs("CandName")
Grd11 = rs("Grd1")
Subj2 = rs("Subj2")
Grd12 = rs("Grd2")
Subj3 = rs("Subj3")
Grd13 = rs("Grd3")
Subj4 = rs("Subj4")
Grd14 = rs("Grd4")
Subj5 = rs("Subj5")
Grd15 = rs("Grd5")
Subj6 = rs("Subj6")
Grd16 = rs("Grd6")
Subj7 = rs("Subj7")
Grd17 = rs("Grd7")
Subj8 = rs("Subj8")
Grd18 = rs("Grd8")



'validate the Key fields (Examseries, year, candNo and index no.)
Exam_Sr = rs("ExamSeries")
If Exam_Sr = "" Then
MsgBox ("Pls enter the correct Exam Serie"), vbOKOnly
ElseIf Exam_Sr = 1 Then
frmListing.Exam_Series.Caption = "May/June"
Else
Exam_Sr = 2
frmListing.Exam_Series.Caption = "Nov/Dec"

Me.Exam_Sr.SetFocus
Me.Refresh
End If

Exam_Yr = rs("ExamYear")
If Exam_Yr = "" Then
MsgBox ("Please Enter the exam year"), vbOKOnly

Me.Exam_Yr.SetFocus
End If
It prints the fields on to another form after getting them
'Show a modal form named frmListing.
frmListing.Show vbModal, Me

rs.MoveNext
Next i

code ends

[AHMEDYO]

HI....

i understand your code and every thing is ok, but i didnt find your question, or i miss something???

Best Regards

[gilsygirl]

Thanks for the reply

The problem is this

the for loop am using counts all the records and the prints all of the records from the database.

What i can't do is to be able to choose record by record using the the Candidate No, Year of exam and centre No.

I just don't how to use these three values as a key to search for a record.

Thanks for your help.

Newbie

[Killer42]

One possibility would be to check these fields inside the For loop, to decide whether to process the record.

Generally though, you would probably want to use a WHERE clause in your SQL to determine which records are placed in the recordset. Can you show us how rs was created?

[gilsygirl]

One possibility would be to check these fields inside the For loop, to decide whether to process the record.

Generally though, you would probably want to use a WHERE clause in your SQL to determine which records are placed in the recordset. Can you show us how rs was created?

Thanks for the reply. This is where i create the recordset.

Public Function initdb()
On Error GoTo error
Set ws = DBEngine.Workspaces(0)
Set db = ws.OpenDatabase("c:\resultchecker.mdb")
Set rs = db.OpenRecordset("candres", dbOpenTable)
Exit Function

This is the for loop and the select statement where i check the records.

If rs.RecordCount = 0 Then Exit Sub
     rs.MoveFirst
   For i = 1 To rs.RecordCount ' Read all the records from database
 
   sqlStmt = "select * From candres where ExamSeries='" & Exam_Sr & "'and ExamYear='" & Exam_Yr & "'and CentNo='" & cbosch & "' and IndexNo = '" & Cand_No & "'"
    rs.OpenRecordset sqlStmt, dbOpenTable this where i get the error
   (Datatype conversion error)
 
       If Not (rs.EOF) Then
          frmCandResult.Show vbModal, Me
       Else
          MsgBox "sorry not a perfect match"
    End If

Thanks a lot for all the help i really want this work.
gilsygirl (Newbie)

[QVeen72]

Hi,

Check this Modified code.
Remove the If Condition and the For Loop:

 
 
sqlStmt = "select * From candres where ExamSeries='" & Exam_Sr & "'and ExamYear='" & Exam_Yr & "'and CentNo='" & cbosch & "' and IndexNo = '" & Cand_No & "'"
Set rs = db.OpenRecordset(sqlStmt, dbOpenDynaset)
If RS.RecordCount>0 Then
     frmCandResult.Show vbModal, Me
Else
    MsgBox "sorry not a perfect match"
End If
 

Regards
Veena

[gilsygirl]

Hello QVeena72

Thanks for the code. I tried it but the problem is i have already set db in the General section. In this code we are trying to set it again.
Set rs = db.OpenRecordset(sqlStmt, dbOpenDynaset)
If RS.RecordCount>0 Then
frmCandResult.Show vbModal, Me

What do think i should do.

Thanks for ur help.

[QVeen72]

Hi,

I guess you mean "RS" Object, you are opening in General Section.
Declare Another Recordset (say RSNew) and open RSNew, with the above statament..

Regards
Veena

[Killer42]

If RS is already defined and you don't want to change it, probably your best bet is to create a temporary recordset as Veena suggested, which includes just the records you want to play with.

Alternatively, you could simply use an IF test within the loop. Something like...

Dim FoundAny As Boolean
If rs.RecordCount > 0 Then
  rs.MoveFirst
  Do Until rs.EOF
    If rs("ExamSeries") = Exam_Sr _
    And rs("ExamYear") = Exam_Yr _
    And rs("CentNo") = cbosch _
    And rs("IndexNo") = Cand_No Then
      frmCandResult.Show vbModal, Me
      FoundAny = True
    End If
    rs.MoveNext
  Loop
End If
If Not FoundAny Then
  MsgBox "Not a erfect match"
End If

You might also want to investigate the FindFirst and FindNext methods.

You really don't want to try and change the recordset while you're in the middle of processing it (as in the rs.OpenRecordset in post #5).

One other thing, which may have caused you some confusion. When a recordset is opened, you cannot trust the RecordCount property to tell you how many records there are - only whether there are any. It starts out as either 0 or 1, I believe. If you want the actual count of records, you need to do a MoveLast to force the DB engine to read them all.

[gilsygirl]

Can any body tell me what wrong with this code. Been trying for ages but it only displays the first record from th database.

If rs.RecordCount = 0 Then Exit Sub
For i = 1 To rs.RecordCount ' Read all the records from database
 
  Do While rs.EOF
    rs.MoveFirst
    sqlStmt = "select * From candres where ExamSeries='" & Exam_Sr & "' and ExamYear='" & Exam_Yr & "' and CentNo='" & Cent_No & "' and IndexNo='" & Index_No & "'"
 
  Loop
  If ((rs.ExamSeries = Exam_Sr) And (rs.ExamYear = Exam_Yr) And (rs.CentNo = Cent_No) And (rs.IndexNo = Index_No)) Then
    Call dispres
  End If
 
  If rs.EOF <> True Then
    MsgBox "No records found"
  End If
 
  frmListing.Show vbModal, Me
 
Next i
rs.MoveNext
End Sub

just don't know what is wrong with the code.

[QVeen72]

Hi,

Why you are Moving First (4th line) ...?
Remove that part and Just before Loop, Write RST.MoveNext

Regards
Veena

[Killer42]

I'm going to try rearranging the code so it makes a bit more sense. The looping seems all mixed up. (That Do While loop, especially, looks as though it's simply being used in place of an IF statement).

Note that I'm not entirely familiar with what you're trying to do, so don't take this as gospel...

If rs.RecordCount = 0 Then Exit Sub
 
AnyFound = False ' (AnyFound would be declared as Boolean)
rs.MoveFirst
Do Until rs.EOF
  If (rs.ExamSeries = Exam_Sr) And (rs.ExamYear = Exam_Yr) And (rs.CentNo = Cent_No) And (rs.IndexNo = Index_No) Then
    AnyFound = True
    Call dispres
  End If
  rs.MoveNext
Loop
 
If Not AnyFound Then
  MsgBox "No records found"
End If
 
frmListing.Show vbModal, Me
 

[gilsygirl]

Hi,

Why you are Moving First (4th line) ...?
Remove that part and Just before Loop, Write RST.MoveNext

Regards
Veena

Hi Qveen72
I have tried putting moving that line but it is still not working.
the Cent_No text box keeps changing when the mouse is moved but dont not call the disprec fuction.
Just can figure the error.

[QVeen72]

Hi,

Probably the IF condition is not being satisfied...

If (rs.ExamSeries = Exam_Sr) And (rs.ExamYear = Exam_Yr) And (rs.CentNo = Cent_No) And (rs.IndexNo = Index_No) Then
 

Regards
Veena

[Killer42]

gilsygirl, you need to get familiar with the debugging tools built into VB. You can track exactly what the code is doing, statement by statement, and examine/modify variables on the fly, and so on. This will be your best weapon in attacking this bug.