Doing a do while not rst.eof loop not passing on else statement why?

[deanvilar]

guys .... am already restructuring my codes as am doing a loop after it says that .eof = true if goes directly to endif ... not passing thru else, any idea why?

Set db = CurrentDb
Set rst = db.OpenRecordset("documentLog", dbOpenDynaset)
 
rst.MoveFirst
 
If Not rst.EOF Then
    Do While Not rst.EOF
        If rst!docLogNo = Me.txtDocLogNo.Value Then
            Me.txtBadgeNo = rst!badgeNo
            Me.txtFullName = rst!fullName
            Me.txtWBRef = rst!wayBillRef
            Me.txtLocation = rst!location
            Me.txtFileCode = rst!fileCode
            Me.txtDocType = rst!docNo
            Me.txtDocNo = rst!docType
            Me.txtDocTitle = rst!docTitle
            Me.txtAssetNo = rst!assetNo
            Me.txtEquipType = rst!equipType
            Me.txtEquipModel = rst!equipModel
            Me.txtAssetOwner = rst!assetOwner
            Me.cmbTakeOutDay = rst!takeOutDay
            Me.cmbTakeOutMonth = rst!takeOutMonth
            Me.txtTakeOutYear = rst!takeOutYear
            Me.cmbReturnedDay = rst!returnedDay
            Me.cmbReturnedMonth = rst!returnedMonth
            Me.txtReturnedYear = rst!returnedYear
 
            Exit Do
        End If
        rst.MoveNext
    Loop
Else
    MsgBox "Record not Found!"
 
    Me.txtDocLogNo.Value = ""
    Me.txtBadgeNo.Value = ""
    Me.txtFullName.Value = ""
    Me.txtWBRef.Value = ""
    Me.txtLocation.Value = ""
    Me.txtFileCode.Value = ""
    Me.txtDocType.Value = ""
    Me.txtDocNo.Value = ""
    Me.txtDocTitle.Value = ""
    Me.txtAssetNo.Value = ""
    Me.txtEquipType.Value = ""
    Me.txtEquipModel.Value = ""
    Me.txtAssetOwner.Value = ""
    Me.cmbTakeOutDay.Value = ""
    Me.cmbTakeOutMonth.Value = ""
    Me.txtTakeOutYear.Value = ""
    Me.cmbReturnedDay.Value = ""
    Me.cmbReturnedMonth.Value = ""
    Me.txtReturnedYear.Value = ""
 
    Me.txtDocLogNo.SetFocus
End If
 
rst.Close
 
Set rst = Nothing
Set db = Nothing

[MikeTheBike]

Hi

My initial thought here is that rst.EOF is actually False, but none ot the returned records match the txtDocLogNo value. This would then appear to do absolutly nothing !!

??


MTB

[deanvilar]

hi Mike, yes initially it's false ... my input in txtDocLogNo <> with rst!docLogNo so when .eof = true it must tell the user that record is not found.

[Seth Schrock]

I'm a little confused about your question. Can you explain the path you expect the code to take using line numbers from above? It sounds like you are expecting it to do the Do While loop starting on line 7 and then take the Else path on line 32 based on your title and question.

Also, are there multiple records where rst!docLogNo = Me.txtDocLogNo.Value? If not, there is a much better way to do this.

[deanvilar]

Hello master seth, to give a clear picture on this ... user will be searching onto me.txtDocLogNo, do while not rst.eof will go thru the records to find rst!docLogNo = me.txtDocLogNo(entry), the log number is not existing it must pass thru line 32 to tell that record not found .. I hope you understand my explanation =)

[Seth Schrock]

Once the test in line 6 is executed, it will either go to the True path or the False path (the Else). If it goes to the true path (there are records), then it won't go to the False side as the condition (Not rst.EOF) was already tested as true. So you need to reorder where you have your IF/Then statements to test for things as they will occur.

[deanvilar]

understood the explanation sir seth, I have tried re-organizing my if/then statements ... till now I can't figure out how to verify that record not found uuuuuuuuuufffff...

[Seth Schrock]

A much better way to do this would be to setup a query that has the criteria equivalent to rst!docLogNo = Me.txtDocLogNo.Value. This is much more efficient that looping through every single record, which is what your code will do. If/when you get thousands of records, this will take a lot of time and the database can't benefit from any indexes. Try something like this (this assumes docLogNo is a number):

Dim db As DAO.Database
Dim strQuery As String
Dim rst As DAO.Recordset
 
Set db = CurrentDb
strQuery = "SELECT * FROM documentlog WHERE docLogNo = " & Me.txtDocLogNo
set rst = db.OpenRecordset(strQuery, dbOpenDynaset)
 
If rst.RecordCount > 0 Then
    'Do your code here
Else
    'Do your false code here
End If
 
rst.Close
 
Set rst = Nothing
Set db = Nothing

Now if only one record can match the criteria in the query, then you don't need a loop.

[deanvilar]

sir seth, yes docLogNo is number but I made it as docLogNo as text in table .... yes .. only 1 records can match the criteria ...

[deanvilar]

sir error --> DATA TYPE MISMATCH IN CRITERIA EXPRESSION in line

Set rst = db.OpenRecordset(strQuery, dbOpenDynaset)

[deanvilar]

now it's ok sir seth ... i changed something in

strQuery = "SELECT * FROM documentLog WHERE docLogNo =  '" & Me.txtDocLogNo & "'"

now its working just as smooth as silk! thanks to you sir!

[Seth Schrock]

Yep. That is what needed to change so that it would handle the text type. Glad you got it to work.

[deanvilar]

thank you for your help as always sir seth .. i really appreciate it .. thank you