asked on

How do I stop reading at EOF

I am having trouble with the following code not reading the EOF or end of the record. The code works by clicking a button and it moves to next record in the field but when it reaches the last record it gives a null error even though I have an if statement for it stop at the EOF.

Private Sub cmdNext_Click()
Dim db As Database
Dim rec As DAO.Recordset
Dim occid As Integer
Set db = CurrentDb
Set rec = db.OpenRecordset("SELECT * FROM Occupation", dbOpenDynaset)
Debug.Print cmbocc1.Value
If Not (rec.EOF) Then
occid = Me.cmbocc1.Column(0)
rec.FindFirst "OccupationID =" & occid
rec.MoveNext
Me.cmbocc1.Value = rec(0)
Else
cmdNext.Enabled = False
End If
End Sub

SOLUTION

TimCottee

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

tupac1979

ASKER

Thanks Tim,
It is still giving me the null error I dont understand why it does not see the EOF. Do you have any other suggestions

Arji

Where is the Null error occurring? Also, what is Rec(0). You might try:

Do While not rec.EOF '<-------- this will do the loop only once if there is one record or loop until all records are read
occid = Me.cmbocc1.Column(0)
rec.FindFirst "OccupationID =" & occid
rec.MoveNext '<---------- this will cause an error in an if statement if you are at .eof
Me.cmbocc1.Value = rec(0) '<----------where is this value coming from. This is questionable because you can't set the value of a combo
Loop ' unless the value exists in the combo rowsource.

cmdNext.Enabled = False

tupac1979

ASKER

The null error is occuring on line:
Me.cmbocc1.Value = rec(0)

and Rec(0) is just setting the value of my combo box to the first value to the first value in the query.

Arji

Then I would do this:

If not isnull(Rec(0)) then Me.cmbocc1 = rec(0)

tupac1979

ASKER

Now I am getting another runtime error on the above line that states "The value you entered isnt valid for this field" I appreciate all of your help guys. If anyone can help me get this resolved I would appreciate it. Thanks

Dave

Arji

Put a break right at:

If not isnull(Rec(0)) then Me.cmbocc1 = rec(0)

Then do debug.print rec(0) in the immediate window(ctrl-G) and hit enter. What the value of rec(0)?

ASKER CERTIFIED SOLUTION

Plamodo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

tupac1979

ASKER

Thanks Arji I used the debug statement and here are the results BTW there are 13 records in the table
2
3
4
5
6
7
8
9
10
11
12
13

tupac1979

ASKER

Also Plamado I tried your way and I am still getting an error message about null thanks.

SOLUTION

Plamodo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Arji

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Arji

Also, you probably want to requery the combobox after all is done:

Me.cmbocc1.requery

tupac1979

ASKER

Thanks guys for all of your help I guess there is really no way to do this but I have learned a lot in the process

Plamodo

tupac1979:

What you are trying to do seems pretty standard, and I have no doubt that with a bit of patience we can lead you towards a solution.

tupac1979

ASKER

Actually I figured it out with everyones help I had to put another if statement in there to loop back to the first record and the end of the record. I would much rather the program to stop but if it goes back to the first record that would be fine. Thanks everyone.

Dave