If - Then Syntax and Records on a Form

[Kevin McBrearty]

I have a module with the following if statment:

If Not rstStr.EOF Or rstMftr.EOF Or rstDistbr.EOF Then

I added a watch for all three recordsets and EOF is true for all three of
them; however, it is evaluating the if statement as true.

The second problem I have is I am opening a form that lists the records that
have a duplicate store id for the user to resolve the conflicts. On the form
I want the user to click on continue then for it to check and see if new
values that were enter will conflict with any of the other store id's. I
thought an easy way to do this would be to have the query refresh and then
display any new confilcts if they exist. If not close the form and continue
with the rest of the code. I tried to refresh the data on the form but that
didn't work. One thing I would like to mention is that it needs to be this
way. This is part of the code that synchronizes the database. If I made the
store id unique it would cause other problems. If the remote user added a new
store, manufacturer, or distrubutor with a duplicate Id. The key field for
these tables is a random autonumber.

Any help you could provide would be appreciated.

Thank you,

 

[Brendan Reynolds]

My guess would be that the expression is being evaluated as follows ...

If (Not rstStr.EOF) Or rstMftr.EOF Or rstDistrbr.EOF

That is to say if the first recordset is not at EOF or either of the second
recordsets are at EOF.

If you want the code in the If statement to run only if none of the three
recordsets are at EOF, try ...

If Not (rstStr.EOF Or rstMftr.EOF Or rstDistrbr.EOF)

or perhaps ...

If Not(rstStr.EOF) And Not(rstMftr.EOF) And Not(rstDistrbr.EOF) ...

.... which is a bit verbose, but has the advantage of making the intention
more clear to any other developer who may have to maintain the code (or
yourself 12 months down the road!

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[Tim Ferguson]

=?Utf-8?B?S2V2aW4gTWNCcmVhcnR5?=

If Not rstStr.EOF Or rstMftr.EOF Or rstDistbr.EOF Then

I added a watch for all three recordsets and EOF is true for all three
of them; however, it is evaluating the if statement as true.

Check out operator precedence: what I guess you want is

If Not (rstStr.EOF Or rstMftr.EOF Or rstDistbr.EOF) Then
or
If Not (rstStr.EOF And rstMftr.EOF And rstDistbr.EOF) Then

but I can't tell which from the question.

The second problem I have is I am opening a form that lists the
records that have a duplicate store id for the user to resolve the
conflicts.

SELECT i.StoreID, i.RandomAN, i.SomethingElse
FROM Stores AS i
WHERE i.StoreID IN
( SELECT g.StoreID
FROM Stores AS g
GROUP BY g.StoreID
HAVING COUNT(*)>1
)
ORDER BY i.StoreID, i.RandonAN

On the form I want the user to click on continue then for
it to check and see if new values that were enter will conflict with
any of the other store id's. I thought an easy way to do this would
be to have the query refresh

Probably this should be Form.Requery rather than Refresh. I think the
latter only looks for changes to records caused by other users.

If not close the form and continue with the rest of the
code.

This probably needs a SELECT COUNT(*) similar to the inner grouping query
above.

The whole thing is probably a bit fiddly to get right, but should not be
too hard.

Hope that helps


Tim F