End of the Record set 1

[Junkie88]

rstQuestions is a recordset containing a set of questions. I am using the following code to move from one question to the next. I want my form to close automatically if I try to go to the next record after the last question. With the following code, I am not able to accomplish that. Instead I see one more empty record after the last question and then if I try to go further an error occurs related to the end of record.

If rstQuestions.EOF Then
DoCmd.Close acForm, "frmQuestions", acSaveNo
Else
DoCmd.GoToRecord , , acNext
End If

 

[PWise]

is this a recordset or a form with records

 

[Junkie88]

a text box on the form is based on this recordset.

 

[PWise]

try

If rstQuestions.EOF Then
    DoCmd.Close acForm, "frmQuestions", acSaveNo
Else
    rstQuestions.movenext
End If

 

[Junkie88]

that did not work. now it does not show me the next question in the text box.

 

[PWise]

what is your complete code

 

[Junkie88]

it might be difficult to understand it since it is very complicated. i have a next button on the form that moves me to the next question. before showing the new question, it stores the answer to the current question and then checks if the answer to the next question is already recorded if it is then it shows the next question with its answer otherwise it just shows the next question.

Private Sub cmdNext_Click()
Dim strSQL As String
Dim rstCountAns As DAO.Recordset
Dim rstQuestions As DAO.Recordset

On Error GoTo Err_cmdNext_Click

'record the answer to the current question in the ASK table
DoCmd.SetWarnings False
'If there is a question on the form record the answer
If txtQuesID <> "" Then
subfrmAsk!txtQuesID.Value = txtQuesID.Value
subfrmAsk!txtAns.Value = ComboAnswer.Value
'else close the form
Else
DoCmd.Close acForm, "frmQuestions", acSaveNo
End If

'record information in the PATIENT_SESSION table
strSQL = "SELECT COUNT(ANS_ID) FROM ASK WHERE ANS_ID = " & subfrmAsk!txtAnsID & ";"
Set rstCountAns = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
If rstCountAns.Fields(0) Like 0 Then
strSQL = "INSERT INTO PATIENT_SESSION (PATIENT_ID, UNIQUE_SESSION_ID, ANS_ID) VALUES (" & txtPatientID.Value & " ," & txtSessionID.Value & " ," & subfrmAsk!txtAnsID & ");"
DoCmd.RunSQL strSQL
End If

subfrmAsk!txtAns.Value = ComboAnswer.Value
DoCmd.SetWarnings True

Set rstQuestions = CurrentDb.OpenRecordset("qryQuestionnaire", dbOpenDynaset)

'show the next question on the form with the answer or close the form if end of recordset
If rstQuestions.EOF Then
DoCmd.Close acForm, "frmQuestions", acSaveNo
Else
DoCmd.GoToRecord , , acNext
If txtQuesID <> "" Then
subfrmAsk!txtQuesID.Value = txtQuesID.Value
ComboAnswer.Value = subfrmAsk!txtAns.Value
Else
ComboAnswer.Value = ""
End If
End If

Exit_cmdNext_Click:
Exit Sub

Err_cmdNext_Click:
DoCmd.Echo False
MsgBox "You have reached the end of the questionnaire", vbOKOnly, "Notification"
DoCmd.Close acForm, "frmQuestions", acSaveNo
DoCmd.Echo True
Resume Exit_cmdNext_Click
End Sub

 

[Remou]

Try:

If Me.Recordset.RecordCount = Me.CurrentRecord

EOF is end of file, not last record.

 

[Junkie88]

thanks that worked, but i do not understand why? Me means the current form, and current record returns the index of the current record? what does recordcount return?

 

[Remou]

Me.Recordset.RecordCount is the count of records for the current form.

 

[PHV]

Did you try this ?
DoCmd.GoToRecord , , acNext
If rstQuestions.EOF Then
DoCmd.Close acForm, "frmQuestions", acSaveNo
End If

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886