msg for no record found

[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

 

[Dirk Goldgar]

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

 

[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

 

[Dirk Goldgar]

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.

 

[Dan @BCBS]

Perfect
Thank you

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)

 

[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