Default Text for an Empty returned recordset

  • Thread starter Thread starter ricky
  • Start date Start date
R

ricky

Hi

I have a query executed from a command button. The query prompts the user
to supply a variable. If the variable returns no data, how would I place
some default text to tell the user nothing has been returned.

Kind Regards

Ricky
 
How are you executing the query? If you're using DoCmd.OpenQuery, then I
don't think there's anything you can do.
 
First, rather than have the user enter to parameter value in the query, put a
text box on your form and have them enter it there. Then, open the query as
a recordset and test the recordcount property. If it returns 0, not records
are returned.

Change the criteria in your query to reference the control the user where
the user will enter the criteria

Forms!MyFormName!txtSearchValue

Dim dbf As Database
Dim rst As Recordset
Dim qdf as Querydef
Dim lngRecords As Long

Set dbf = Currentdb
Set qdf = dbf.Querydefs("MyQueryNameHere")
qdf.Parameters(0) = Me.txtSearchValue
Set rst = qdf.OpenRecordset(dbOpenSnapshot, dbReadOnly)
lngRecords = rst.RecordCount
rst.Close
Set rst = Nothing
Set qdf = Nothing
Set dbf = Nothing
If lngRecords < 1 Then
MsgBox "No Records Found"
Else
'Do whatever you want with the query
End If
rst.MoveLast

rst.Close
set rst = nothing
set dbf = nothing
 
Hi Klatuu

Unfortunately, I have inherited this project from someone who has left the
company and lets just say VBA is not my forte. Thank you for the code
posting, I'll try it out.

Kind Regards

Ricky
 
There is one line that needs to be removed.
rst.MoveLast
I don't know how it got there. Copy/Paste can be a dangerous thing.

Post back if you need more help with it.

I also suggest you learn VBA. You can create a workable database without
it, but you will be limited in what you can do. It really isn't that hard to
do.
 
Thank you Klatuu.

Kind Regards

Ricky

Klatuu said:
There is one line that needs to be removed.
rst.MoveLast
I don't know how it got there. Copy/Paste can be a dangerous thing.

Post back if you need more help with it.

I also suggest you learn VBA. You can create a workable database without
it, but you will be limited in what you can do. It really isn't that hard to
do.
Click to expand...
 
Back
Top