loop eof problem

[tracktraining]

Hi All,

I am trying to code the following procedure: Get the jobfunc assign to
empemail from empjob table. Compare the jobfunc to the new jobfunc being
assigned in the form. If the jobfunc being assigned to the empemail at the
form stage is the same with any of the jobfunc already assigned (stored in
empjob table), then exit a particular sub.

The following code is a outline of what I am trying to write. This code put
me in a continuous loop. =(


ChkJobFunc = "SELECT * FROM EmpJob WHERE EmpJob.EmpEmail = '" +
newEmpEmail + "'"

Set dbt = CurrentDb
Set rst = dbt.OpenRecordset(ChkJobFunc)
MsgBox rst!JobFunc
MsgBox Me.txtJobFunc

Do Until rst.EOF
MsgBox "not end yet"
If rst!JobFunc = Me.txtJobFunc Then 'when this is true, exit sub right
away
MsgBox "job function same" 'if not true ever, then perform
the other operation in the sub
Exit Sub
End If
Loop


please help if possible. thanks so much in advance.

Thanks,
Tracktraining

 

[Bob Quintal]

=?Utf-8?B?dHJhY2t0cmFpbmluZw==?=

Hi All,

I am trying to code the following procedure: Get the jobfunc
assign to empemail from empjob table. Compare the jobfunc to the
new jobfunc being assigned in the form. If the jobfunc being
assigned to the empemail at the form stage is the same with any of
the jobfunc already assigned (stored in empjob table), then exit a
particular sub.

The following code is a outline of what I am trying to write. This
code put me in a continuous loop. =(


ChkJobFunc = "SELECT * FROM EmpJob WHERE EmpJob.EmpEmail = '"
+
newEmpEmail + "'"

Set dbt = CurrentDb
Set rst = dbt.OpenRecordset(ChkJobFunc)
MsgBox rst!JobFunc
MsgBox Me.txtJobFunc

Do Until rst.EOF
MsgBox "not end yet"
If rst!JobFunc = Me.txtJobFunc Then 'when this is true, exit
sub right
away
MsgBox "job function same" 'if not true ever, then
perform
the other operation in the sub
Exit Sub
End If
Loop


please help if possible. thanks so much in advance.

Thanks,
Tracktraining

your code does not move through the recordset, so you will never get
an eof unless there are no records to loop through.

Do Until rst.EOF
If rst!JobFunc = Me.txtJobFunc Then
MsgBox "job function same"
Exit Sub' this should really be Exit Do
' and exit sub properly after closing the rst.
End If
rst.movenext
loop

 

[rquintal]

Hi All,

I am trying to code the following procedure: Get the jobfunc assign to
empemail from empjob table. Compare the jobfunc to the new jobfunc being
assigned in the form. If the jobfunc being assigned to the empemail at the
form stage is the same with any of the jobfunc already assigned (stored in
empjob table), then exit a particular sub.

The following code is a outline of what I am trying to write. This code put
me in a continuous loop. =(


ChkJobFunc = "SELECT * FROM EmpJob WHERE EmpJob.EmpEmail = '" +
newEmpEmail + "'"

Set dbt = CurrentDb
Set rst = dbt.OpenRecordset(ChkJobFunc)
MsgBox rst!JobFunc
MsgBox Me.txtJobFunc

Do Until rst.EOF
MsgBox "not end yet"
If rst!JobFunc = Me.txtJobFunc Then 'when this is true, exit sub right
away
MsgBox "job function same" 'if not true ever, then perform
the other operation in the sub
Exit Sub
End If
Loop


please help if possible. thanks so much in advance.

Thanks,
Tracktraining

your code does not move through the recordset, so you will never get
an eof unless there are no records to loop through.

Do Until rst.EOF
If rst!JobFunc = Me.txtJobFunc Then
MsgBox "job function same"
Exit Sub' this should really be Exit Do
' and exit sub properly after closing the rst.
End If
rst.movenext
loop

 

[Marshall Barton]

I am trying to code the following procedure: Get the jobfunc assign to
empemail from empjob table. Compare the jobfunc to the new jobfunc being
assigned in the form. If the jobfunc being assigned to the empemail at the
form stage is the same with any of the jobfunc already assigned (stored in
empjob table), then exit a particular sub.

The following code is a outline of what I am trying to write. This code put
me in a continuous loop. =(


ChkJobFunc = "SELECT * FROM EmpJob WHERE EmpJob.EmpEmail = '" +
newEmpEmail + "'"

Set dbt = CurrentDb
Set rst = dbt.OpenRecordset(ChkJobFunc)
MsgBox rst!JobFunc
MsgBox Me.txtJobFunc

Do Until rst.EOF
MsgBox "not end yet"
If rst!JobFunc = Me.txtJobFunc Then 'when this is true, exit sub right
away
MsgBox "job function same" 'if not true ever, then perform
the other operation in the sub
Exit Sub
End If
Loop

You forgot the rst.MoveNext line just befor the Loop line.

Depending on what you are going to do when you don'r find a
match, you should probably open the recordset using a query
with criteria or just do a DLookup to check for a match.

 

[tracktraining]

Do Until rst.EOF

If rst!JobFunc = Me.txtJobFunc Then
MsgBox "job function same"
Exit Sub' this should really be Exit Do
' and exit sub properly after closing the rst.
End If
rst.movenext
loop

Thanks!

so if rst!JobFunc = Me.txtjobfunc is true, then Exit Do.... and how do we
write if Exit Do happens, then Exit Sub?

 

[Douglas J. Steele]

Is there anything else after the loop? If not, then the code will
automatically exit the sub.

 

[Mike Painter]

Hi All,

I am trying to code the following procedure: Get the jobfunc assign to
empemail from empjob table. Compare the jobfunc to the new jobfunc
being assigned in the form. If the jobfunc being assigned to the
empemail at the form stage is the same with any of the jobfunc
already assigned (stored in empjob table), then exit a particular sub.

The following code is a outline of what I am trying to write. This
code put me in a continuous loop. =(


ChkJobFunc = "SELECT * FROM EmpJob WHERE EmpJob.EmpEmail = '" +
newEmpEmail + "'"

At this point, if you select for Employee ID and the newEmpEmail you know
if there are any duplicates and if you don't allow dupes you know there is
only one other.
No looping should be needed.
Without a bit more needed code

If ChkJobFunc.recordcount > 0 then
do your thing.
End IF.

 

[tracktraining]

yes there is something after the loop. i don't want the stuff after the loop
to happen if rst!JobFunc = Me.txtJobFunc at any time (i.e. exit DO is true).

Do Until rst.EOF
MsgBox "not end yet"
If rst!JobFunc = Me.txtJobFunc Then
MsgBox "job function same"
Exit Do
End If
rst.MoveNext
Loop

' other stuff - code that should be run if exit do doens't happen when
the loop is completed

' exit sub if exit do happens <--- I need help in writing this code..

thanks!

 

[John W. Vinson]

yes there is something after the loop. i don't want the stuff after the loop
to happen if rst!JobFunc = Me.txtJobFunc at any time (i.e. exit DO is true).

Do Until rst.EOF
MsgBox "not end yet"
If rst!JobFunc = Me.txtJobFunc Then
MsgBox "job function same"
Exit Do
End If
rst.MoveNext
Loop

' other stuff - code that should be run if exit do doens't happen when
the loop is completed

' exit sub if exit do happens <--- I need help in writing this code..

thanks!
--

If ALL you want to do if txtJobFunc exists in the table, you don't need either
a recordset or a loop!! Just use

DLookUp("JobFunc", "tablename", "JobFunc = '" & Me.txtJobFunc & "'")

If it's Null the jobfunc doesn't exist.

That said... if you just want to exit the sub if the jobfunc is found, simply
replace the Exit Do with an Exit Sub.

 

[tracktraining]

thanks very much. I understand it now!

 

[Bob Quintal]

=?Utf-8?B?dHJhY2t0cmFpbmluZw==?=

Thanks!

so if rst!JobFunc = Me.txtjobfunc is true, then Exit Do.... and
how do we write if Exit Do happens, then Exit Sub?

That depends on the code after your loop.

Not having your code I can only suggest
1)an If Endif block.that repeats your test,
2) setting a boolean variable in the loop and testing that,
3) Changing the exit DO to a Goto label statement.
4) removing the loop as suggested by others and using a dLookup or
dCount instead.

The main reason is that Access has a known problem with recordsets
that are not properly closed before exiting a subprocedure.