Default Text for an Empty returned recordset

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.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"ricky" wrote in message
...
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

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


"ricky" wrote:

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

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

"Klatuu" wrote in message
...
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


"ricky" wrote:

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

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.

"ricky" wrote:

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

"Klatuu" wrote in message
...
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


"ricky" wrote:

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

Thank you Klatuu.

Kind Regards

Ricky

"Klatuu" wrote in message
...
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.

"ricky" wrote:

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

"Klatuu" wrote in message
...
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


"ricky" wrote:

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