[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How do I stop reading at EOF

Posted on 2005-05-05
16
Medium Priority
?
269 Views
Last Modified: 2012-06-27
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
0
Comment
Question by:tupac1979
  • 7
  • 5
  • 3
  • +1
16 Comments
 
LVL 43

Assisted Solution

by:TimCottee
TimCottee earned 400 total points
ID: 13935105
Hi tupac1979,
 If Not (rec.EOF) Then
     occid = Me.cmbocc1.Column(0)
     rec.FindFirst "OccupationID =" & occid
     rec.MoveNext
     Me.cmbocc1.Value = rec(0)
        If rec.EOF Then cmdNext.Enabled=False
 Else
   cmdNext.Enabled = False
 End If

You need it inside the first clause of the IF statement as well, the last movenext will set .eof True, when you come back in the next time, you are not at the end of the recordset because you have reloaded it and not done the find yet.

Tim Cottee
0
 

Author Comment

by:tupac1979
ID: 13935217
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
0
 
LVL 17

Expert Comment

by:Arji
ID: 13935303
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
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:tupac1979
ID: 13935409
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.
0
 
LVL 17

Expert Comment

by:Arji
ID: 13935684
Then I would do this:

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

Author Comment

by:tupac1979
ID: 13935780
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
0
 
LVL 17

Expert Comment

by:Arji
ID: 13935896
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)?

0
 
LVL 6

Accepted Solution

by:
Plamodo earned 1200 total points
ID: 13935926
Since you are doing a .findfirst operation, you'll need to check for .nomatch in the case that nothing is found.  If nothing is found, you'll be put at the EOF... something like this:

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

    occid = Me.cmbocc1.Column(0)
    rec.FindFirst "OccupationID =" & occid
    if not rec.nomatch then
       Me.cmbocc1.Value = rec(0)
    Else
      cmdNext.Enabled = False
    End If
End Sub
0
 

Author Comment

by:tupac1979
ID: 13936057
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
0
 

Author Comment

by:tupac1979
ID: 13936066
Also Plamado I tried your way and I am still getting an error message about null thanks.
0
 
LVL 6

Assisted Solution

by:Plamodo
Plamodo earned 1200 total points
ID: 13936135
Then your problem lies with rec(0) .. I'm not sure if that format even works.
Try something like this instead (substitute FieldName with the name of the field you want):

Me.cmbocc1.Value = rec![FieldName]

0
 
LVL 17

Assisted Solution

by:Arji
Arji earned 400 total points
ID: 13936403
You should use rec("IDField") or like Plamodo said rec![IDField] instead of rec(0).  This means that IDField should be a field that you expect to match with the column(0) of the combobox. As long as there are matching records in your combobox it should work.  In other words this condition must be met:

rec(0) = Me.[cmbocc1].Column(0) for each time through the loop.  

What are the field values in column(0) of your combobox?  If they don't match any of the values in rec(0) or rec("IDField") , you'll get an error.

However, I don't understand why you'd want to force that value onto the combobox for each time through the loop.  It would make sense if you only had one record in your recordset but with your code you are changing the value of cmbocc1 13 times.

Using my Do Loop try setting you recordset this way and remove the findfirst:

occid = Me.cmbocc1.Column(0)
Set rec = db.OpenRecordset("SELECT * FROM Occupation WHERE OccupationID =" & occid, dbOpenDynaset)

Do While not rec.EOF                        
     Me.cmbocc1.Value = rec(0)      
     rec.MoveNext    
Loop                    

That way it'l only go through the loop once.
0
 
LVL 17

Expert Comment

by:Arji
ID: 13936533
Also, you probably want to requery the combobox after all is done:

Me.cmbocc1.requery

0
 

Author Comment

by:tupac1979
ID: 13938111
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
0
 
LVL 6

Expert Comment

by:Plamodo
ID: 13938244
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.
0
 

Author Comment

by:tupac1979
ID: 13938356
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
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question