|
|
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
--
Learning
|
|
=?Utf-8?B?dHJhY2t0cmFpbmluZw==?=
<tracktraining[ at ]discussions.microsoft.com> wrote in
news:D3D5F61F-85F7-45C6-B5DB-42A704B820FE[ at ]microsoft.com:
[Quoted Text] > 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
--
Bob Quintal
PA is y I've altered my email address.
** Posted from http://www.teranews.com **
|
|
tracktraining wrote:
[Quoted Text] > 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
|
|
tracktraining wrote:
[Quoted Text] >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.
--
Marsh
MVP [MS Access]
|
|
|
[Quoted Text] > 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?
|
|
Is there anything else after the loop? If not, then the code will
automatically exit the sub.
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)
"tracktraining" <tracktraining[ at ]discussions.microsoft.com> wrote in message
news:B1FDFFD9-423F-4B9E-9F83-C6421C82D692[ at ]microsoft.com...
[Quoted Text] >> 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?
|
|
tracktraining wrote:
[Quoted Text] > 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.
|
|
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!
--
Learning
"Douglas J. Steele" wrote:
[Quoted Text] > Is there anything else after the loop? If not, then the code will > automatically exit the sub. > > -- > Doug Steele, Microsoft Access MVP > http://I.Am/DougSteele> (no private e-mails, please) > > > "tracktraining" <tracktraining[ at ]discussions.microsoft.com> wrote in message > news:B1FDFFD9-423F-4B9E-9F83-C6421C82D692[ at ]microsoft.com... > >> 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? > > > |
|
On Tue, 16 Dec 2008 09:41:01 -0800, tracktraining
<tracktraining[ at ]discussions.microsoft.com> wrote:
[Quoted Text] >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.
--
John W. Vinson [MVP]
|
|
thanks very much. I understand it now!
--
Learning
"John W. Vinson" wrote:
[Quoted Text] > On Tue, 16 Dec 2008 09:41:01 -0800, tracktraining
> <tracktraining[ at ]discussions.microsoft.com> wrote:
>
> >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.
> --
>
> John W. Vinson [MVP]
>
|
|
=?Utf-8?B?dHJhY2t0cmFpbmluZw==?=
<tracktraining[ at ]discussions.microsoft.com> wrote in
news:B1FDFFD9-423F-4B9E-9F83-C6421C82D692[ at ]microsoft.com:
[Quoted Text] >> 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?
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.
--
Bob Quintal
PA is y I've altered my email address.
** Posted from http://www.teranews.com **
|
|
|