Return Msgbox if no record found

[upsman via AccessMonster.com]

I have the following code behind a Find button

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Achievement"

stLinkCriteria = "[StudentID]= " & Forms!Achievement.StudentID & " And
[CurrentMonth]= " & Me!CurrMonth.Column(0)
DoCmd.OpenForm stDocName, , , stLinkCriteria

DoCmd.Close acForm, "AchieveFind"

If no record is found, I'd like to display a msgbox with a message telling
the user this but I'm not sure how or where to code that. What is the
syntax and where in my code would it go?

Thanks,
Rod

 

[Ken Snell [MVP]]

There are many ways to do this. One way is to use DCount function to see if
there are records to be displayed in the new form.

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Achievement"

stLinkCriteria = "[StudentID]= " & Forms!Achievement.StudentID & " And
[CurrentMonth]= " & Me!CurrMonth.Column(0)

If DCount("*", "QueryOrTableUsedForAchievementForm", _
stLinkCriteria) > 0 Then
DoCmd.OpenForm stDocName, , , stLinkCriteria

DoCmd.Close acForm, "AchieveFind"

Else
MsgBox "No data to show."
End If

 

[tina]

from the code, it looks like you are clicking a button on form
"AchieveFind", which then runs the code to open form "Achievement", with
its' recordset filtered by a value...on form "Achievement" ? i don't see
how that can work for you. if it runs at all, the value of
Forms!Achievement.StudentID is always going to be the value in the current
record that the recordset opens to - either the same record every time, or a
random record that you haven't controlled.

do you have a StudentID control in form "AchieveFind", to pick the student
record you want to see in form "Achievement"? if so, then your code should
read

stLinkCriteria = "[StudentID]= " & Me!StudentID & " And
[CurrentMonth]= " & Me!CurrMonth.Column(0)

to check for the existence of a record before opening the form, add the
following code before your Dim statements, as

If DCount(1, "MyTableOrQuery", "[StudentID]= " & Me!StudentID & " And
[CurrentMonth]= " & Me!CurrMonth.Column(0)) < 1 Then
Msgbox "No record found."
Exit Sub
End If

for MyTableOrQuery, substitute the name of the table or query that form
"Achievement" is bound to.

hth

 

[upsman via AccessMonster.com]

Thank you; the DCount worked. I'm new to Access and I'm still learning all
the functions.

from the code, it looks like you are clicking a button on form
"AchieveFind", which then runs the code to open form "Achievement", with
its' recordset filtered by a value...on form "Achievement" ? i don't see
how that can work for you. if it runs at all, the value of
Forms!Achievement.StudentID is always going to be the value in the current
record that the recordset opens to - either the same record every time, or a
random record that you haven't controlled.

do you have a StudentID control in form "AchieveFind", to pick the student
record you want to see in form "Achievement"? if so, then your code should
read

stLinkCriteria = "[StudentID]= " & Me!StudentID & " And
[CurrentMonth]= " & Me!CurrMonth.Column(0)

to check for the existence of a record before opening the form, add the
following code before your Dim statements, as

If DCount(1, "MyTableOrQuery", "[StudentID]= " & Me!StudentID & " And
[CurrentMonth]= " & Me!CurrMonth.Column(0)) < 1 Then
Msgbox "No record found."
Exit Sub
End If

for MyTableOrQuery, substitute the name of the table or query that form
"Achievement" is bound to.

hth

I have the following code behind a Find button

[quoted text clipped - 15 lines]

Thanks,
Rod