Code Where No Corresponding Record Found

N

Noel

Hi. I have a form with a Command Button whose On Click
Event is as follows. This is designed to open another
form where the School ID for the currently viewed School
record is the same as that in tblMentorSBTSchool. At
present this opens a blank form where no corresponding
SchoolID exists in tblMentorSBTSchool. What code could I
add that would instead bring up a message such as No
Mentor/SBT Record Exists For This School. Thanks, Noel

Private Sub Mentors_Click()
On Error GoTo Err_Mentors_Click

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

Exit_Mentors_Click:
Exit Sub

Err_Mentors_Click:
MsgBox Err.Description
Resume Exit_Mentors_Click

End Sub
 
I

Ivan Grozney

Noel said:
-----Original Message-----
Hi. I have a form with a Command Button whose On Click
Event is as follows. This is designed to open another
form where the School ID for the currently viewed School
record is the same as that in tblMentorSBTSchool. At
present this opens a blank form where no corresponding
SchoolID exists in tblMentorSBTSchool. What code could I
add that would instead bring up a message such as No
Mentor/SBT Record Exists For This School. Thanks, Noel

Private Sub Mentors_Click()
On Error GoTo Err_Mentors_Click

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

Exit_Mentors_Click:
Exit Sub

Err_Mentors_Click:
MsgBox Err.Description
Resume Exit_Mentors_Click

End Sub
.
Click to expand...


Noel,

This may not be the best way but it works (I am doing
this at home without access to ACCESS so it might be a
little off). I took your code and input my changes. The
lines with my changes will begin with IG.
Private Sub Mentors_Click()
On Error GoTo Err_Mentors_Click
Click to expand...
IG Dim varCheck as variant
IG Dim strMsg as string
IG varCheck =
Dlookup "[MentorID]", "[tblMentorSBTSchool]", "[tblMentorSB
TSchool.SchoolID=" & SchoolID )
IG if not isnull(varCheck) then
DoCmd.OpenForm "frmMentorsAndSBTs", , , "MentorID IN
(SELECT MentorID FROM tblMentorSBTSchool WHERE
tblMentorSBTSchool.SchoolID=" & SchoolID & ")"
Click to expand...
IG else
IG strMsg = "This school is not in the Mentoring Sys"
IG MsgBox(strMsg, vbOkay, "Bummer")
IG Cancel = True
IG end if
Exit_Mentors_Click:
Exit Sub

Err_Mentors_Click:
MsgBox Err.Description
Resume Exit_Mentors_Click

End Sub
Click to expand...


hth
Ivan
 
N

Noel

Thanks Ivan. Im trying this now but Im getting a bit of
red code, so to speak. The following code, which Im
putting all in one line, goes red and gives me the
message Complie Error Expected end of statement, with
the "[MentorID]" bit highlighted. If I put an opening
round bracket just after DLookup, it goes black. Is that
correct?

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

Next, the line MsgBox (strMsg, vbOkay, "Bummer") goes red
but no error message appears.

Can you see what the problem is here?

Thanks again for the help. Noel
-----Original Message-----
Noel said:
-----Original Message-----
Hi. I have a form with a Command Button whose On Click
Event is as follows. This is designed to open another
form where the School ID for the currently viewed School
record is the same as that in tblMentorSBTSchool. At
present this opens a blank form where no corresponding
SchoolID exists in tblMentorSBTSchool. What code could I
add that would instead bring up a message such as No
Mentor/SBT Record Exists For This School. Thanks, Noel

Private Sub Mentors_Click()
On Error GoTo Err_Mentors_Click

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

Exit_Mentors_Click:
Exit Sub

Err_Mentors_Click:
MsgBox Err.Description
Resume Exit_Mentors_Click

End Sub
.
Click to expand...


Noel,

This may not be the best way but it works (I am doing
this at home without access to ACCESS so it might be a
little off). I took your code and input my changes. The
lines with my changes will begin with IG.
Private Sub Mentors_Click()
On Error GoTo Err_Mentors_Click
Click to expand...
IG Dim varCheck as variant
IG Dim strMsg as string
IG varCheck =
Dlookup "[MentorID]", "[tblMentorSBTSchool]", "[tblMentor SB
TSchool.SchoolID=" & SchoolID )
IG if not isnull(varCheck) then
DoCmd.OpenForm "frmMentorsAndSBTs", , , "MentorID IN
(SELECT MentorID FROM tblMentorSBTSchool WHERE
tblMentorSBTSchool.SchoolID=" & SchoolID & ")"
Click to expand...
IG else
IG strMsg = "This school is not in the Mentoring Sys"
IG MsgBox(strMsg, vbOkay, "Bummer")
IG Cancel = True
IG end if
Exit_Mentors_Click:
Exit Sub

Err_Mentors_Click:
MsgBox Err.Description
Resume Exit_Mentors_Click

End Sub
Click to expand...


hth
Ivan

.
Click to expand...
 
N

Noel

Hi again. Further to my last post, I seem to have got
this workig using good old Poke and Hope. The full code
is now as follows. I have added an opening round bracket
and a square bracket to your DLookup line. Also I have
had to replace your MsgBox line with just MsgBox
(strMsg). Finally Ive had to rem out your Cancel = True
line, which was giving me an error when I ran the code
proper. So its working OK for me. Do you see anything
wrong with my changes? - I know code can seem to be OK at
first then something happens to make it bring up errors
later. Thanks for the help, Noel

Full code:
Private Sub Mentors_Click()
On Error GoTo Err_Mentors_Click
Dim varCheck As Variant
Dim strMsg As String
varCheck = DLookup
("[MentorID]", "[tblMentorSBTSchool]", "[tblMentorSBTSchoo
l].SchoolID = " & SchoolID)
If Not IsNull(varCheck) Then

DoCmd.OpenForm "frmMentorsAndSBTs", , , "MentorID IN
(SELECT MentorID FROM tblMentorSBTSchool WHERE
tblMentorSBTSchool.SchoolID=" & SchoolID & ")"
Else
strMsg = "There is no Mentor or School Based Tutor
Associated with this School"
'MsgBox (strMsg, vbOkay, "Bummer")
MsgBox (strMsg)
'Cancel = True
End If


Exit_Mentors_Click:
Exit Sub

Err_Mentors_Click:
MsgBox Err.Description
Resume Exit_Mentors_Click

End Sub
-----Original Message-----
Noel said:
-----Original Message-----
Hi. I have a form with a Command Button whose On Click
Event is as follows. This is designed to open another
form where the School ID for the currently viewed School
record is the same as that in tblMentorSBTSchool. At
present this opens a blank form where no corresponding
SchoolID exists in tblMentorSBTSchool. What code could I
add that would instead bring up a message such as No
Mentor/SBT Record Exists For This School. Thanks, Noel

Private Sub Mentors_Click()
On Error GoTo Err_Mentors_Click

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

Exit_Mentors_Click:
Exit Sub

Err_Mentors_Click:
MsgBox Err.Description
Resume Exit_Mentors_Click

End Sub
.
Click to expand...


Noel,

This may not be the best way but it works (I am doing
this at home without access to ACCESS so it might be a
little off). I took your code and input my changes. The
lines with my changes will begin with IG.
Private Sub Mentors_Click()
On Error GoTo Err_Mentors_Click
Click to expand...
IG Dim varCheck as variant
IG Dim strMsg as string
IG varCheck =
Dlookup "[MentorID]", "[tblMentorSBTSchool]", "[tblMentor SB
TSchool.SchoolID=" & SchoolID )
IG if not isnull(varCheck) then
DoCmd.OpenForm "frmMentorsAndSBTs", , , "MentorID IN
(SELECT MentorID FROM tblMentorSBTSchool WHERE
tblMentorSBTSchool.SchoolID=" & SchoolID & ")"
Click to expand...
IG else
IG strMsg = "This school is not in the Mentoring Sys"
IG MsgBox(strMsg, vbOkay, "Bummer")
IG Cancel = True
IG end if
Exit_Mentors_Click:
Exit Sub

Err_Mentors_Click:
MsgBox Err.Description
Resume Exit_Mentors_Click

End Sub
Click to expand...


hth
Ivan

.
Click to expand...
 
I

Ivan Grozney

Noel,

Sorry about that... When I do msgbox from memory, I
almost NEVER get it right. On my work PC I have a bunch
of examples. Here is what should work, again sorry about
that.

MsgBox (strMSG), vbOkay, "Bummer"

Ivan.

-----Original Message-----
Thanks Ivan. Im trying this now but Im getting a bit of
red code, so to speak. The following code, which Im
putting all in one line, goes red and gives me the
message Complie Error Expected end of statement, with
the "[MentorID]" bit highlighted. If I put an opening
round bracket just after DLookup, it goes black. Is that
correct?

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

Next, the line MsgBox (strMsg, vbOkay, "Bummer") goes red
but no error message appears.

Can you see what the problem is here?

Thanks again for the help. Noel
-----Original Message-----
Noel said:
-----Original Message-----
Hi. I have a form with a Command Button whose On Click
Event is as follows. This is designed to open another
form where the School ID for the currently viewed School
record is the same as that in tblMentorSBTSchool. At
present this opens a blank form where no corresponding
SchoolID exists in tblMentorSBTSchool. What code could I
add that would instead bring up a message such as No
Mentor/SBT Record Exists For This School. Thanks, Noel

Private Sub Mentors_Click()
On Error GoTo Err_Mentors_Click

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

Exit_Mentors_Click:
Exit Sub

Err_Mentors_Click:
MsgBox Err.Description
Resume Exit_Mentors_Click

End Sub
.
Click to expand...


Noel,

This may not be the best way but it works (I am doing
this at home without access to ACCESS so it might be a
little off). I took your code and input my changes. The
lines with my changes will begin with IG.
Private Sub Mentors_Click()
On Error GoTo Err_Mentors_Click
Click to expand...
IG Dim varCheck as variant
IG Dim strMsg as string
IG varCheck =
Dlookup "[MentorID]", "[tblMentorSBTSchool]", "[tblMentor SB
TSchool.SchoolID=" & SchoolID )
IG if not isnull(varCheck) then
DoCmd.OpenForm "frmMentorsAndSBTs", , , "MentorID IN
(SELECT MentorID FROM tblMentorSBTSchool WHERE
tblMentorSBTSchool.SchoolID=" & SchoolID & ")"
Click to expand...
IG else
IG strMsg = "This school is not in the Mentoring Sys"
IG MsgBox(strMsg, vbOkay, "Bummer")
IG Cancel = True
IG end if
Exit_Mentors_Click:
Exit Sub

Err_Mentors_Click:
MsgBox Err.Description
Resume Exit_Mentors_Click

End Sub
Click to expand...


hth
Ivan

.
Click to expand...
.
Click to expand...
 
N

Noel

Hi Ivan. No problem. I actually sent you a second post,
which seems to have completely dissapeared, to say that I
got it all working by changing your MsgBox line to MsgBox
(strMSG). Ill try your ammended code for this on Monday -
looks like it should be interesting. I also had to rem out
your Cancel = True line which didnt go red but which
brought up an error when I opened the form and clicked on
the command button. Not to worry - it works exactly as I
want now. Thanks for the help with this. Cheers, Noel
-----Original Message-----
Noel,

Sorry about that... When I do msgbox from memory, I
almost NEVER get it right. On my work PC I have a bunch
of examples. Here is what should work, again sorry about
that.

MsgBox (strMSG), vbOkay, "Bummer"

Ivan.

-----Original Message-----
Thanks Ivan. Im trying this now but Im getting a bit of
red code, so to speak. The following code, which Im
putting all in one line, goes red and gives me the
message Complie Error Expected end of statement, with
the "[MentorID]" bit highlighted. If I put an opening
round bracket just after DLookup, it goes black. Is that
correct?

varCheck =
DLookup "[MentorID]", "[tblMentorSBTSchool]", "[tblMentor S
BTSchool.SchoolID = " & SchoolID )

Next, the line MsgBox (strMsg, vbOkay, "Bummer") goes red
but no error message appears.

Can you see what the problem is here?

Thanks again for the help. Noel
-----Original Message-----
Noel wrote:

-----Original Message-----
Hi. I have a form with a Command Button whose On Click
Event is as follows. This is designed to open another
form where the School ID for the currently viewed School
record is the same as that in tblMentorSBTSchool. At
present this opens a blank form where no corresponding
SchoolID exists in tblMentorSBTSchool. What code could I
add that would instead bring up a message such as No
Mentor/SBT Record Exists For This School. Thanks, Noel

Private Sub Mentors_Click()
On Error GoTo Err_Mentors_Click

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

Exit_Mentors_Click:
Exit Sub

Err_Mentors_Click:
MsgBox Err.Description
Resume Exit_Mentors_Click

End Sub
.


Noel,

This may not be the best way but it works (I am doing
this at home without access to ACCESS so it might be a
little off). I took your code and input my changes. The
lines with my changes will begin with IG.

Private Sub Mentors_Click()
On Error GoTo Err_Mentors_Click

IG Dim varCheck as variant
IG Dim strMsg as string
IG varCheck =
Dlookup "[MentorID]", "[tblMentorSBTSchool]", "[tblMento
Click to expand...
r
SB
TSchool.SchoolID=" & SchoolID )
IG if not isnull(varCheck) then

DoCmd.OpenForm "frmMentorsAndSBTs", , , "MentorID IN
(SELECT MentorID FROM tblMentorSBTSchool WHERE
tblMentorSBTSchool.SchoolID=" & SchoolID & ")"
IG else
IG strMsg = "This school is not in the Mentoring Sys"
IG MsgBox(strMsg, vbOkay, "Bummer")
IG Cancel = True
IG end if

Exit_Mentors_Click:
Exit Sub

Err_Mentors_Click:
MsgBox Err.Description
Resume Exit_Mentors_Click

End Sub


hth
Ivan

.
Click to expand...
.
Click to expand...
.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Linked Forms Code 1
Repost - Linked Forms Problem 1
Further help with code please 6
Repost - Help with code 9
Help with Code please 4
Changing Linked Form code 8
Cascading Combo problem. 11
msg for no record found 5

Top