Help with Code please

[Guest]

Hi. Im using Access 2002 on XP.

Can anyone help me modify the following code? Its part of a set of On Click
code associated with a forms command button and is intended to identify a set
of records where the SchoolID field has the same value as the record being
viewed on the form. I want to add an AND statement to this because the set of
records identified must also have a null value in a field called
MovedFromSchoolID, which is also in tblMentorSBTSchool.

varCheck = DLookup("[MentorID]", "[tblMentorSBTSchool]",
"[tblMentorSBTSchool].SchoolID=" & SchoolID)

Thanks in advance, JohnB

 

[Rick Brandt]

Hi. Im using Access 2002 on XP.

Can anyone help me modify the following code? Its part of a set of On
Click code associated with a forms command button and is intended to
identify a set of records where the SchoolID field has the same value
as the record being viewed on the form. I want to add an AND
statement to this because the set of records identified must also
have a null value in a field called MovedFromSchoolID, which is also
in tblMentorSBTSchool.

varCheck = DLookup("[MentorID]", "[tblMentorSBTSchool]",
"[tblMentorSBTSchool].SchoolID=" & SchoolID)

Thanks in advance, JohnB

varCheck = DLookup("[MentorID]", "[tblMentorSBTSchool]",
"[tblMentorSBTSchool].SchoolID=" & SchoolID & " And [MovedFromSchoolID] Is
Null")

 

[Guest]

Thanks Rick. I wont get a chance to try this until tomorrow around 10.30am
GMT. Ill get back to you on this thread if I have any problems. Thanks again,
JohnB

Hi. Im using Access 2002 on XP.

Can anyone help me modify the following code? Its part of a set of On
Click code associated with a forms command button and is intended to
identify a set of records where the SchoolID field has the same value
as the record being viewed on the form. I want to add an AND
statement to this because the set of records identified must also
have a null value in a field called MovedFromSchoolID, which is also
in tblMentorSBTSchool.

varCheck = DLookup("[MentorID]", "[tblMentorSBTSchool]",
"[tblMentorSBTSchool].SchoolID=" & SchoolID)

Thanks in advance, JohnB

varCheck = DLookup("[MentorID]", "[tblMentorSBTSchool]",
"[tblMentorSBTSchool].SchoolID=" & SchoolID & " And [MovedFromSchoolID] Is
Null")

 

[Guest]

Hi again Rick.

Your code does work, in itself, but it didnt do what Id hoped. Im now
focusing on a different line in the code but Ive played around for ages and
always get a syntax error when I try to run it. Any chance you could look at
this line?

DoCmd.OpenForm "frmMentorsAndSBTs", , , "MentorID IN (SELECT MentorID FROM
tblMentorSBTSchool WHERE tblMentorSBTSchool.SchoolID=" & SchoolID & ")"

The line of code youve already helped me with just checks if theres anything
to work on and the above code then should select Mentor records from
tblMentorSBTSchool where the SchoolID in the records is the same as the
School currently being shown on the form. I want to add the AND ststement to
this code because the records selected must also have the MovedFromSChoolID
as a Null.

As I say Ive poked and prodded and cant get the syntax right. Also, can I
assume that IsNull is always entered as one word?

Finally, Ive been reluctant to explain the real problem because Im worried
about it being too longwinded. If you reply and it still doesnt do what I
want, would you be willing to hear the full story?

Thanks in advance. JohnB

Hi. Im using Access 2002 on XP.

Can anyone help me modify the following code? Its part of a set of On
Click code associated with a forms command button and is intended to
identify a set of records where the SchoolID field has the same value
as the record being viewed on the form. I want to add an AND
statement to this because the set of records identified must also
have a null value in a field called MovedFromSchoolID, which is also
in tblMentorSBTSchool.

varCheck = DLookup("[MentorID]", "[tblMentorSBTSchool]",
"[tblMentorSBTSchool].SchoolID=" & SchoolID)

Thanks in advance, JohnB

varCheck = DLookup("[MentorID]", "[tblMentorSBTSchool]",
"[tblMentorSBTSchool].SchoolID=" & SchoolID & " And [MovedFromSchoolID] Is
Null")

 

[Guest]

Hi Rick. No need to reply now. I played around with query to help me sort out
the SQL syntax and Ive got it working as I want now. Thanks for the help.
JohnB

Hi. Im using Access 2002 on XP.

Can anyone help me modify the following code? Its part of a set of On
Click code associated with a forms command button and is intended to
identify a set of records where the SchoolID field has the same value
as the record being viewed on the form. I want to add an AND
statement to this because the set of records identified must also
have a null value in a field called MovedFromSchoolID, which is also
in tblMentorSBTSchool.

varCheck = DLookup("[MentorID]", "[tblMentorSBTSchool]",
"[tblMentorSBTSchool].SchoolID=" & SchoolID)

Thanks in advance, JohnB

varCheck = DLookup("[MentorID]", "[tblMentorSBTSchool]",
"[tblMentorSBTSchool].SchoolID=" & SchoolID & " And [MovedFromSchoolID] Is
Null")