form based on a query

[Guest]

Hello

I've got a form which is based on a query

It's opened by a command button - when the command button is clicked a tect
box prompts for the criteria "enter word"

The query searches a list of courses for any containing the "word" and then
the form opens and displays those records

So far so good - BUT
if there are no matches for the "word" - the form opens but is just a grey
screen - no boxes or anything - just blank

I've tried code on the command button and on the form itself - I'd just like
a message box saying "no records to display" and then for the form to close
and the previous screen (with the command button on it) to reappear

I've even tried putting some extra code on the SQL of the query - but having
absolutely no luck

I can get a message box to display if there is a record with the following
code:
Private Sub Form_Load()
If [course name] <> "" Then
MsgBox "is record"

End If
End Sub

and I tried
Else
MsgBox "no record"
but that didn't work

a message box on the "on error" of the form load does come up - but it comes
up whether the query returns something or not

also tried DCount <1 on the query result but that didn't work either

stumped - any help greatfully received
thanks
Shona

 

[Guest]

Hello

I've got a form which is based on a query

It's opened by a command button - when the command button is clicked a tect
box prompts for the criteria "enter word"

The query searches a list of courses for any containing the "word" and then
the form opens and displays those records

So far so good - BUT
if there are no matches for the "word" - the form opens but is just a grey
screen - no boxes or anything - just blank

I've tried code on the command button and on the form itself - I'd just like
a message box saying "no records to display" and then for the form to close
and the previous screen (with the command button on it) to reappear

I've even tried putting some extra code on the SQL of the query - but having
absolutely no luck

I can get a message box to display if there is a record with the following
code:
Private Sub Form_Load()
If [course name] <> "" Then
MsgBox "is record"

End If
End Sub

and I tried
Else
MsgBox "no record"
but that didn't work

a message box on the "on error" of the form load does come up - but it comes
up whether the query returns something or not

also tried DCount <1 on the query result but that didn't work either

stumped - any help greatfully received
thanks
Shona

Just a little clarification Shona whwt do you want with the data in the form
because if its just for the purpose of display you could use a report and use
the queries criteria for the records returned
Do you know how to create and set a custom dialog box to extract data?
*Don't know and not afraid to ask*

 

[Guest]

it is just for display - I've been trawling the internet for other instances
of this problem and found the following code:


If Me.RecordSetClone.RecordCount = 0 Then
DoCmd.Close
End If

which seems to work beautifully - so problem solved - I've got a few more
screens to do and will give your suggestion a try

many thanks
Shona

Hello

I've got a form which is based on a query

It's opened by a command button - when the command button is clicked a tect
box prompts for the criteria "enter word"

The query searches a list of courses for any containing the "word" and then
the form opens and displays those records

So far so good - BUT
if there are no matches for the "word" - the form opens but is just a grey
screen - no boxes or anything - just blank

I've tried code on the command button and on the form itself - I'd just like
a message box saying "no records to display" and then for the form to close
and the previous screen (with the command button on it) to reappear

I've even tried putting some extra code on the SQL of the query - but having
absolutely no luck

I can get a message box to display if there is a record with the following
code:
Private Sub Form_Load()
If [course name] <> "" Then
MsgBox "is record"

End If
End Sub

and I tried
Else
MsgBox "no record"
but that didn't work

a message box on the "on error" of the form load does come up - but it comes
up whether the query returns something or not

also tried DCount <1 on the query result but that didn't work either

stumped - any help greatfully received
thanks
Shona

Just a little clarification Shona whwt do you want with the data in the form
because if its just for the purpose of display you could use a report and use
the queries criteria for the records returned
Do you know how to create and set a custom dialog box to extract data?
*Don't know and not afraid to ask*

 

[Jon Lewis]

In your form's Open event:

If.Me.RecordSetClone.RecordCount = 0 Then
Cancel = True
MsgBox "No records"
End If

HTH