msg for no record found

D

Dan @BCBS

The user enters a number (in Me.txtMBR) and clicks the button for this code
and then the form opens with the information for that number.
If the number entered does not exist it opens a blank form. That is the
problem.
I am trying to add code to pop up a message saying no record found.

Could someone please help.

Private Sub Member_Click()
Dim stDocName As String
Dim stLinkCriteria As String

If IsNull(Me.txtMBR) Then
MsgBox "You must enter a valid Member Number."
Exit Sub
Else
If Not (IsNull(Me.txtMBR)) Then
stLinkCriteria = "[MemberNo]=" & "'" & Me.txtMBR & "'"
End If

stDocName = "frmQualityData"
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If

Exit_Member_Click:
Exit Sub

Err_Member_Click:
MsgBox Err.Description
Resume Exit_Member_Click
End Sub
 
D

Dirk Goldgar

Dan @BCBS said:
The user enters a number (in Me.txtMBR) and clicks the button for this
code
and then the form opens with the information for that number.
If the number entered does not exist it opens a blank form. That is the
problem.
I am trying to add code to pop up a message saying no record found.

Could someone please help.

Private Sub Member_Click()
Dim stDocName As String
Dim stLinkCriteria As String

If IsNull(Me.txtMBR) Then
MsgBox "You must enter a valid Member Number."
Exit Sub
Else
If Not (IsNull(Me.txtMBR)) Then
stLinkCriteria = "[MemberNo]=" & "'" & Me.txtMBR & "'"
End If

stDocName = "frmQualityData"
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If

Exit_Member_Click:
Exit Sub

Err_Member_Click:
MsgBox Err.Description
Resume Exit_Member_Click
End Sub


You could either (a) detect this condition in the form's Open or Load event,
like this:


Private Sub Form_Open(Cancel As Integer)

If Me.Recordset.RecordCount = 0 Then
MsgBox "No records to display!"
Cancel = True
End If

End Sub

Or (b) you could use a DLookup to see if the form will have any records,
before you open it, like this:

If IsNull(DLookup("MemberNo", "QualityData", stLinkCriteria)) Then
MsgBox "No records for this member!"
Else
DoCmd.OpenForm "frmQualityData", , , stLinkCriteria
End If
 
R

Ron2006

or could also use:

If Dcount("MemberNo", "QualityData", stLinkCriteria)) = 0 Then
MsgBox "No records for this member!"
Else
DoCmd.OpenForm "frmQualityData", , , stLinkCriteria
End If
 
D

Dirk Goldgar

Ron2006 said:
or could also use:

If Dcount("MemberNo", "QualityData", stLinkCriteria)) = 0 Then
MsgBox "No records for this member!"
Else
DoCmd.OpenForm "frmQualityData", , , stLinkCriteria
End If


Yep. I tend not to use DCount to check for existence, though, for fear that
it may force a full pass through a table when all I want to know is whether
any matching record exists. I've never benchmarked these alternatives,
though, to see how much difference (if any) it makes.
 
D

Dan @BCBS

Perfect
Thank you

Dirk Goldgar said:
Dan @BCBS said:
The user enters a number (in Me.txtMBR) and clicks the button for this
code
and then the form opens with the information for that number.
If the number entered does not exist it opens a blank form. That is the
problem.
I am trying to add code to pop up a message saying no record found.

Could someone please help.

Private Sub Member_Click()
Dim stDocName As String
Dim stLinkCriteria As String

If IsNull(Me.txtMBR) Then
MsgBox "You must enter a valid Member Number."
Exit Sub
Else
If Not (IsNull(Me.txtMBR)) Then
stLinkCriteria = "[MemberNo]=" & "'" & Me.txtMBR & "'"
End If

stDocName = "frmQualityData"
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If

Exit_Member_Click:
Exit Sub

Err_Member_Click:
MsgBox Err.Description
Resume Exit_Member_Click
End Sub


You could either (a) detect this condition in the form's Open or Load event,
like this:


Private Sub Form_Open(Cancel As Integer)

If Me.Recordset.RecordCount = 0 Then
MsgBox "No records to display!"
Cancel = True
End If

End Sub

Or (b) you could use a DLookup to see if the form will have any records,
before you open it, like this:

If IsNull(DLookup("MemberNo", "QualityData", stLinkCriteria)) Then
MsgBox "No records for this member!"
Else
DoCmd.OpenForm "frmQualityData", , , stLinkCriteria
End If


--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
 
R

Ron2006

That is a possibility, (I have not tested it either) Although if the
field that you do the dlookup on is NOT indexed, then there very well
would be no difference.

But then again if it is indexed, and the criteria is on an indexed
field (unique record requested, in this case) I would think there
would not be too much difference.

I have had trouble with NULL returned values so have tended to use
dcount because it is always valid and so do not have as many times
where testing only proved that the test ran and did not actually test
all conditions.

Ron
 

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

Top