Full Version: Check Exists
UtterAccess Forums > Microsoft® Access > Access Forms
mike60smart
Hi Everyone

I currently have the following Code on the On Click Event of a Command Butto n and it works just fine.

Is there any way to inform the user with a Message if No Records exist??

Any help appreciated.

Code is:-
Dim strCrit As String

If Me.txtFastener > "" Then
strCrit = strCrit & "([Fastener] = " & Chr(34) & Me.txtFastener & Chr(34) & ") AND "
End If

If Me.txtType > "" Then
strCrit = strCrit & "([FastenerType] = " & Chr(34) & Me.txtType & Chr(34) & ") AND "
End If

If Me.txtPart > "" Then
strCrit = strCrit & "([PartNumber] = " & Chr(34) & Me.txtPart & Chr(34) & ") AND "
End If

If Me.txtDia > "" Then
strCrit = strCrit & "([Diameter] = " & Chr(34) & Me.txtDia & Chr(34) & ") AND "
End If


If Len(Me.txtLgth & vbNullString) > 0 Then
strCrit = strCrit & "([Length] = " & Me.txtLgth & ") AND "
End If



If strCrit > "" Then
strCrit = Left(strCrit, Len(strCrit) - 5)

DoCmd.OpenForm "frmSearchResults", acNormal, , strCrit

End If
Bob G
how do you know there are no records ?
Larry Larsen
Hi Mike

You could validate the record number on the forms "Open" event..

eg:

[code]
Private Sub Form_Open(Cancel As Integer)

Dim rst As Object
Set rst = Me.RecordsetClone

If rst.RecordCount = 0 Then
MsgBox "There were No Records Returned.", vbInformation, " No Records Returned"
Cancel = True
End If

Set rst = Nothing

End Sub
/code]

HTH's
thumbup.gif
dmhzx
would not this work

Not sure which event of the new form but

if me.recordset.eof then
msg box
close form
end if

Maybe the on Open event? (Which can be cancelled)


You'd need to turn that into code of course.
mike60smart
Hi Larry

Many thanks the code you provided produces the required message:-

Click to view attachment

Click OK and the following error is displayed:-

Click to view attachment

When I select Debug the following line is highlighted:-

Click to view attachment

How would I modify the code so that if there are No Records retrieved and I select OK on the Message Box window it then takes me back to the Search Form ??
Larry Larsen
Hi Mike

May have to "trap" and deal with the error message..

eg:
CODE
On Error GoTo Err_Trapp

' Your code goes here <<<<<<<<<

Err_Exit:

Exit Sub

Err_Trapp:

If Err.Number = 2501 Then

Resume Next

Else

Msgbox "Error #" & Err.Number & " - " & Err.Description, VbInformation+vbOKOnly,"Error!"

Resume Err_Exit


HTH's
mike60smart
Hi Larry

Ok Now when I run the On Click I get this error:-

Click to view attachment

Where would I put the required End If?

The code is this at the Moment:-

Private Sub Form_Open(Cancel As Integer)
On Error GoTo Err_Trapp

Dim rst As Object
Set rst = Me.RecordsetClone

If rst.RecordCount = 0 Then
MsgBox "There were No Records Returned.", vbInformation, " No Records Returned"
Cancel = True
End If

Set rst = Nothing

Err_Exit:

Exit Sub

Err_Trapp:

If Err.Number = 2501 Then

Resume Next

Else

MsgBox "Error #" & Err.Number & " - " & Err.Description, vbInformation + vbOKOnly, "Error!"

Resume Err_Exit

End Sub
RAZMaddaz
Mike, you need to add another "End If" at the bottom, after the last "Resume Err_Exit".

RAZMaddaz
Larry Larsen
Hi Mike

CODE
Resume Err_Exit

End If

End Sub


Missed the End IF off the bottom..
thumbup.gif
mike60smart
OK did that

Run the code which produces the correct msg "No Records"

Click OK and get the following error :-

Click to view attachment

Click Debug and it highlights the same line of code as before ??

DoCmd.OpenForm "frmSearchResults", acNormal, , strCrit
Bob G
do you close the original search form when you click the button?

you may just need an exit sub or something like that

would need an ELSE statement after your message box
RAZMaddaz
PMFJI....

Hi Mike!!!

Try defining the Form name using Dim and use the defined name as oppose to the real name of the Form in the DoCmd.

Example:

Dim stDocName as String

Set stDocName = "frmSearchResults"

DoCmd.OpenForm stDocName, acNormal, , strCrit


RAZMaddaz
mike60smart
Hi Raz

Ok So I modified the On Click Code to be as shown below:-

But I now get the Error "Object Required"

Code is:-

Private Sub cmdSearch_Click()
Dim stDocName As String

Set stDocName = "frmSearchResults"

Dim strCrit As String
If Me.txtFastener > "" Then
strCrit = strCrit & "([Fastener] = " & Chr(34) & Me.txtFastener & Chr(34) & ") AND "
End If

If Me.txtType > "" Then
strCrit = strCrit & "([FastenerType] = " & Chr(34) & Me.txtType & Chr(34) & ") AND "
End If
If Me.txtPart > "" Then
strCrit = strCrit & "([PartNumber] = " & Chr(34) & Me.txtPart & Chr(34) & ") AND "
End If

If Me.txtDia > "" Then
strCrit = strCrit & "([Diameter] = " & Chr(34) & Me.txtDia & Chr(34) & ") AND "
End If


If Len(Me.txtLgth & vbNullString) > 0 Then
strCrit = strCrit & "([Length] = " & Me.txtLgth & ") AND "
End If

If strCrit > "" Then
strCrit = Left(strCrit, Len(strCrit) - 5)

DoCmd.Close acForm, "frmFastenerSearch"
'DoCmd.OpenForm "frmSearchResults", acNormal, , strCrit
DoCmd.OpenForm stDocName, acNormal, , strCrit


End If


End Sub
mike60smart
Hi Bob

Yes I am using this to close the Form

DoCmd.Close acForm, "frmFastenerSearch"
RAZMaddaz
Mike,

Enter the last "End If" before the DoCmd.

Like the following:

CODE
If strCrit > "" Then
strCrit = Left(strCrit, Len(strCrit) - 5)
End If

DoCmd.Close acForm, "frmFastenerSearch"
'DoCmd.OpenForm "frmSearchResults", acNormal, , strCrit
DoCmd.OpenForm stDocName, acNormal, , strCrit
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.