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.SetF ocus
Me.Refresh
End If

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

Me.Exam_Yr.SetF ocus
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.
[CODE=vb]Public Function initdb()
On Error GoTo error
Set ws = DBEngine.Worksp aces(0)
Set db = ws.OpenDatabase ("c:\resultchec ker.mdb")
Set rs = db.OpenRecordse t("candres", dbOpenTable)
Exit Function[/CODE]

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

[CODE=vb]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.OpenRecordse t sqlStmt, dbOpenTable this where i get the error
(Datatype conversion error)

If Not (rs.EOF) Then
frmCandResult.S how vbModal, Me
Else
MsgBox "sorry not a perfect match"
End If[/CODE]

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:

[code=vb]

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

Regards
Veena

[gilsygirl]

Set rs to db

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.OpenRecordse t(sqlStmt, dbOpenDynaset)
If RS.RecordCount> 0 Then
frmCandResult.S how 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...
[CODE=vb]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.S how vbModal, Me
FoundAny = True
End If
rs.MoveNext
Loop
End If
If Not FoundAny Then
MsgBox "Not a erfect match"
End If[/CODE]

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.OpenRecordse t 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.


[CODE=vb]
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[/CODE]

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...
[CODE=vb]
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
[/CODE]

[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...

[code=vb]
If (rs.ExamSeries = Exam_Sr) And (rs.ExamYear = Exam_Yr) And (rs.CentNo = Cent_No) And (rs.IndexNo = Index_No) Then
[/code]
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.