Do Until Loop Suggestions

[Guest]

I have a table for holding users data. I would like to check that table to
make sure that I am still in it. (Note: This is mostly an exercise to help
me understand how to do this kind of action. This is just an example I am
making up.) If the code loops through the recordset and does not find my
network ID, it give the user a message box that I am not there.

Assuming I have already created my recordset, I would want to exit sub if it
finds me, or if it reaches rst.EOF, then give me a msgbox.

Even though this is a sample, I have encountered times when this approach
may be useful.

Thoughts?
Thanks in advance!
PJ

 

[Guest]

Hi PJ,
If I've understood well you can try this:

flag_found=0
do while not rst.eof
'Let's say you save your network ID in a variable named your_network_id
if rst![network id]=your_network_id then
flag_found=1
exit do
endif
rst.movenext
loop
'If, after looping through the entire recordset, flag_found is still=0 means
that the network id has not been found
if flag_found=0 then
msgbox "Network id not found", vbExclamation, "Warning"
endif

HTH Paolo

 

[Guest]

Perfect! I was missing the rst.movenext.

Thanks!

Hi PJ,
If I've understood well you can try this:

flag_found=0
do while not rst.eof
'Let's say you save your network ID in a variable named your_network_id
if rst![network id]=your_network_id then
flag_found=1
exit do
endif
rst.movenext
loop
'If, after looping through the entire recordset, flag_found is still=0 means
that the network id has not been found
if flag_found=0 then
msgbox "Network id not found", vbExclamation, "Warning"
endif

HTH Paolo

I have a table for holding users data. I would like to check that table to
make sure that I am still in it. (Note: This is mostly an exercise to help
me understand how to do this kind of action. This is just an example I am
making up.) If the code loops through the recordset and does not find my
network ID, it give the user a message box that I am not there.

Assuming I have already created my recordset, I would want to exit sub if it
finds me, or if it reaches rst.EOF, then give me a msgbox.

Even though this is a sample, I have encountered times when this approach
may be useful.

Thoughts?
Thanks in advance!
PJ

 

[John W. Vinson]

I have a table for holding users data. I would like to check that table to
make sure that I am still in it. (Note: This is mostly an exercise to help
me understand how to do this kind of action. This is just an example I am
making up.) If the code loops through the recordset and does not find my
network ID, it give the user a message box that I am not there.

Ummm...

How about using Access as a relational database, rather than as a serial
programming exercise? The whole PURPOSE of tables is that they can be
searched; looping through all records in a table would very rarely be either
necessary or appropriate.

Create a Query with a criterion on the network ID field. If the query returns
no records, that ID isn't there; if it does, you have the record. No code is
needed AT ALL.

Or - if you're already in a VBA code environment -

If IsNull(DLookUp("[NetworkID]", "[tablename]", "[NetworkID] = " & iNet) Then
<the network ID in the variable iNet was not found>
End If


John W. Vinson [MVP]