Loop does not stop at recordset condition

[AshikHusein]

I am trying to use this code when a form opens. Essentially what the code should do is to stop at the record when a certain field("ccc") has no data in it.

The problem is that it does not stop at the first record with the above condition but goes on till the EOF and the gives me an error message. Can anyone tell me what is wrong with the code?

Private Sub Form_Open(Cancel As Integer)

Dim rec As Variant

Set db = CurrentDb
Set rec = Me.RecordsetClone

Do Until rec("ccc") = ""
rec.MoveNext
Loop

Me.Bookmark = rec.Bookmark

rec.Edit
rec("ccc") = "1"
rec.Update

rec.Close
End Sub

 

[fuzzygeek]

What is null

In Access, "" does not equal null. If the field has not been initialized, then it is null, if it has been intialized and is variant, then it is empty.

IsNull(rec("ccc")) or IsEmpty(rec("ccc"))

 

[AshikHusein]

Hi!

I am trying to use the following but it is still giving me the same error message (#3021)


do until IsNull(rec("ccc")).

I have also tried the IsEmpty(rec("ccc")) with the same results ie EOF error.

ccc is declared as a TEXT field if that is of any help. Please let me know if there could be something more here.

 

[The_Doc_Man]

You should not use ANY attribute of a field to test for the end of your recordset. When visiting the records of a recordset, you want to use .EOF and .BOF to find the endpoints.

When you first open a recordset you are going to traverse, you need to do a

recset.MoveFirst

at which time recset.BOF should be true. IF recset.EOF is ALSO true, you have an empty recordset. Just quit right there. Otherwise, you want to do a loop based on a

Do

... yada, yada, yada

recset.MoveNext
Until recset.EOF

(or Until recset.EOF = True, if you are a purist)

 

[AshikHusein]

Hi!

I am not trying to find the end points. I am just trying to movenext till I get to the record where field "ccc" is null.

The problem is that the loop

Do until isEmpty(rec("ccc")) does not stop at the recordset where the field "ccc" is null or empty.

 

[The_Doc_Man]

Well, I won't second-guess why your problem is like this, but try...

Do


Until Len(Nz([myfield],"")) = 0

 

[namliam]

try: Len(trim(Nz([myfield],""))) = 0

just in case of only space values....

If that should also fail try:
Dim rec As Recordset
or
Dim rec As DAO.Recordset

Changing
rec("ccc")
to
rec.field("ccc")
or
rec!ccc

Regards

 

[AshikHusein]

Would like to apologize for all who tried to hel!

Actually IsNull(rec("ccc")) does work. I was experimenting on a new db file and did not know that the table was empty.

thanks again for all your help guys and/or gals!