Do While Not rs.EOF Looping Problem 1

[swk003]

Can anyone help with this loop query? I am trying to validate an ID using recordset, here's the plan: If ID correct a message is displayed and focus advanced to next txtbox. If ID incorrect a message displays "incorrect" message AND the txtbox focus is moved back to a previous txtbox!

In the below code HOWEVER; If the ID is correct the loop skips straight through to the else statement regardless of whether the if statement is correct or not. Can anyone help with the coding??

Dim rs As DAO.Recordset ' requires DAO reference Set rs = CurrentDb.OpenRecordset("PrimaryData")
rs.MoveLast
rs.MoveFirst
Do While Not rs.EOF
If rs("field1") = Me.[Primary_PID] Then
MsgBox rs(0)' display correct ID
[Primary_Eye_right].SetFocus' move txtbox focus
Else
MsgBox ("incorrect")'incorrect ID
[Primary_grading_date].SetFocus 'reset focus
End If
rs.MoveNext
Loop
rs.Close
Set rs = Nothing

thanks

Simon

 

[luceze]

Try this:

Dim rs As DAO.Recordset ' requires DAO reference Set rs = CurrentDb.OpenRecordset("PrimaryData")

rs.MoveLast
rs.MoveFirst
Do Until rs.EOF
    If rs!Field1 = Me.[Primary_PID] Then
   MsgBox rs!0' display correct ID
   [Primary_Eye_right].SetFocus' move txtbox focus
 Else
   MsgBox "incorrect"  'incorrect ID
   me.[Primary_grading_date].SetFocus 'reset focus
   End If
rs.MoveNext
Loop
rs.Close
Set rs = Nothing

HTH,
Eric

 

[swk003]

Hi Luceze

Tried the code but application still drops through to the "incorrect" msgbox and this gets stuck in an infinite!!

any ideas?

Simon

 

[luceze]

The following works fine on my machine:

Dim rs As DAO.Recordset ' requires DAO reference Set rs = CurrentDb.OpenRecordset("PrimaryData")

rs.MoveLast
rs.MoveFirst
Do Until rs.EOF
    If rs!Field1 = Me.[Primary_PID] Then
   CkSt = 1
   strmsg = rs![0] ' display correct ID
   GoTo Finish
 Else
   CkSt = 0
   strmsg = "incorrect"  'incorrect ID
   End If
rs.MoveNext
Loop
Finish:
If CkSt = 1 Then
    MsgBox strmsg
   [Primary_Eye_right].SetFocus ' move txtbox focus
   Else
   MsgBox strmsg
   Me.[Primary_grading_date].SetFocus 'reset focus
End If
rs.Close
Set rs = Nothing

HTH,
Eric

 

[swk003]

Luceze

Am getting a runtim error '3265' item not found in collection. I have tried to declare the CkSt and strmsg variables see below, can't be right!! what do you have in your application?


Private Sub Primary_PID_AfterUpdate()

Dim CkSt As Integer
Dim strmsg As Variant

Dim rs As DAO.Recordset ' requires DAO reference Set rs = CurrentDb.OpenRecordset("PrimaryData")

rs.MoveLast
rs.MoveFirst
Do Until rs.EOF
If rs!Field1 = Me.[Primary_PID] Then
CkSt = 1
strmsg = rs![0] ' display correct ID
GoTo Finish
Else
CkSt = 0
strmsg = "incorrect" 'incorrect ID
End If
rs.MoveNext
Loop
Finish:
If CkSt = 1 Then
MsgBox strmsg
[Primary_Eye_right].SetFocus ' move txtbox focus
Else
MsgBox strmsg
Me.[Primary_grading_date].SetFocus 'reset focus
End If
rs.Close
Set rs = Nothing
End Sub

simon

 

[luceze]

What line are you getting the error on?

 

[Golom]

The syntax rs![0] expects there to be a field named "0".
If you want the first field then you need to use rs.Fields(0). The following may be a bit more efficient
[tt]
Private Sub Primary_PID_AfterUpdate()

Dim CkSt As Integer
Dim strmsg As Variant
Dim rs As DAO.Recordset ' requires DAO reference

Set rs = CurrentDb.OpenRecordset("PrimaryData")
rs.FindFirst "Field1='" & Me.[Primary_PID] & "'"

If rs.NoMatch Then
CkSt = 0
strmsg = incorrect
Else
CkSt = 1
strmsg = rs.Fields(0)
End If

MsgBox strmsg
If CkSt = 1 Then
Me.[Primary_Eye_right].SetFocus ' move txtbox focus
Else
Me.[Primary_grading_date].SetFocus 'reset focus
End If
rs.Close
Set rs = Nothing
End Sub
[/tt]

 

[swk003]

line:

strmsg = rs![0] ' display correct ID

 

[Golom]

Or you could try
[tt]
Private Sub Primary_PID_AfterUpdate()

Dim CkSt As Integer
Dim strmsg As Variant
Dim rs As DAO.Recordset ' requires DAO reference

Set rs = CurrentDb.OpenRecordset( _
"Select * From PrimaryData " & _
"Where Field1 ='" & Me.[Primary_PID] & "'")

If rs.EOF And rs.BOF Then
CkSt = 0
strmsg = "incorrect"
Else
CkSt = 1
strmsg = rs.Fields(0)
End If

MsgBox strmsg
If CkSt = 1 Then
Me.[Primary_Eye_right].SetFocus ' move txtbox focus
Else
Me.[Primary_grading_date].SetFocus 'reset focus
End If
rs.Close
Set rs = Nothing
End Sub
[/tt]

 

[swk003]

get the following error:

data type mismatch in criteria expression runtime error 3464.

line:

Set rs = CurrentDb.OpenRecordset( _
"Select * From PrimaryData " & _
"Where Field1 ='" & Me.[Primary_PID] & "'")

 

[Golom]

Sorry ... I assumed that your fields were text. If they are numeric then change it to
[tt]
Set rs = CurrentDb.OpenRecordset( _
"Select * From PrimaryData " & _
"Where Field1 =" & Me.[Primary_PID] )
[/tt]

 

[luceze]

Hey Golom,

I was trying to time each of the examples to see which ran fastest and I could not get rs.findfirst to run. I kept getting the error "Operation is not supported for this type of object"

I have the DAO3.6 reference set. Have you seen this before?

By the way, with small recordsets (10 records) the loop in my example performs better than your second example. With a larger recordset (10,000 records),as I expected, your second example blew away my example.

Eric

 

[Golom]

My fault I guess. When you specify a table then DAO opens the database with the "dbOpenTable" option and "FindFirst" is not supported for recordsets opened as tables. You would need to specify
[tt]
Set rs = CurrentDb.OpenRecordset("PrimaryData",dbOpenDynaset)
[/tt]
to use "FindFirst".

Your performance test is about right. There's rather low overhead in opening a table as opposed to opening an SQL Select query and flipping through records when there only a few (10 for example) is also fairly quick. The SQL select is going to win when you have a lot of records AND your search field is indexed. Your program loop is of course a sequential search.

 

[swk003]

Golom + Eric

Many thanks for your help. the data mismatch has been sorted and I can now validate all PID's. Thanks for being so patient.

all the best

Simon