form based on a query

[shonam]

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

stumped - any help greatfully received
thanks
Shona

[istya]

I had this problem, what you can do is

dim dbs as dao.database
dim results as dao.recordset
set dbs = currentdb
set results = dbs.OpenRecordset("SELECT * FROM query;")
if results.recordcount = 0 then
    msgbox("There were no records to display")
else
    'open form
end if
 

where query is the query that you form is based on.
If you put this in the Form_Load() sub it should work.

[istya]

Sorry, one thing I for to say, if you use "SELECT COUNT(*) FROM query;" then recordcount will ALWAYS be 1 as you have a result - its a value of 0, but it's a result. If you use the count function, you can use result(0) instead of results.recordcount as results(0) will use the value returned by "SELECT COUNT(*) FROM query;"

[shonam]

thanks very much - I gave it a try but get
runtime error 3061
too few parameters. Expected 1
on the line:
Set results = dbs.OpenRecordset("SELECT * FROM selcour;")

not sure what it means
Shona

[istya]

Can you do me a favour and post the whole sub in and I'll see if I can find the problem.

[istya]

One other question - do you have Microsoft DAO ticked in your "Tools->References" in the VBA??

I think I know what the problem is -

    Dim dbs As DAO.Database
    Dim results As DAO.Recordset
 

rather than

    Dim dbs As DOA.Database
    Dim results As DOA.Recordset
 

My mistake

[shonam]

I noticed the doa / dao thing and changed it - and I checked that the dao is checked in references

the sub is:

Private Sub Form_Load()
Dim dbs As DAO.Database
Dim results As DAO.Recordset
Set dbs = CurrentDb
Set results = dbs.OpenRecordset("SELECT * FROM selcour;")
If results(0) Then
MsgBox ("There were no records to display")
Else
'open form
End If

should I have changed anything apart from the query name
(sorry - really rusty on Access and trying to re-learn)

thanks again
Shona

End Sub

[istya]

you want to use results.recordcount rather than results(0) as you don't have count in the recordset sql string

If results.recordcount = 0 Then
   MsgBox ("There were no records to display")
Else
   'open form
End If
 

[shonam]

Still getting a grey screen I'm afraid - code now says:

Private Sub Form_Load()
Dim dbs As DAO.Database
Dim results As DAO.Recordset
Set dbs = CurrentDb
Set results = dbs.OpenRecordset("SELECT * FROM selcour;")
If results.RecordCount = 0 Then
MsgBox ("There were no records to display")
Else
'open form
End If

End Sub

[istya]

does it display the "There were no records to display" message or doesn't it get that far?

[shonam]

if there are records then the form opens and displays them properly - if there are no records I just get the grey screen - the message box doesn't appear

[istya]

What I suggest you do then is the code for the command button rather than the form_load() sub

Private Sub command_button_click()
Dim dbs As DAO.Database
Dim results As DAO.Recordset
Set dbs = CurrentDb
Set results = dbs.OpenRecordset("SELECT * FROM selcour;")
If results.recordcount = 0 Then
MsgBox ("There were no records to display")
Else
'open form
End If
 

This means that if there are no records to display it won't open the form. Alternatively, you can

Private Sub Form_Load()
Dim dbs As DAO.Database
Dim results As DAO.Recordset
Set dbs = CurrentDb
Set results = dbs.OpenRecordset("SELECT * FROM selcour;")
If results.recordcount = 0 Then
MsgBox ("There were no records to display")
docmd.close
End If
 

which should close the form if there are no reocrds to display.

[shonam]

tried both of those but I'm afraid I get
runtime error 3061
too few parameters. Expected 1

sorry and thanks for helping - I'm way out of my depth here

[istya]

post your code and I'll have a look. Also, let me know if you want to open the form and then check or check than open the form if required.

[shonam]

I just pasted in the code you suggested
tried both ways as I don't mind whether the opens or not really - either way is fine

[istya]

If you do the working around the command button rather than form_load where formname is the name of the form that you are trying to open and command_button is the name of you button

Private Sub command_button_click()
 
Dim dbs As DAO.Database
Dim results As DAO.Recordset
dim stdocname as string
dim Dim stLinkCriteria As String
 
Set dbs = CurrentDb
Set results = dbs.OpenRecordset("SELECT * FROM selcour;")
 
If results.recordcount = 0 Then
   MsgBox ("There were no records to display")
Else
   stDocName = "formname"
   DoCmd.OpenForm stDocName, , , stLinkCriteria
End If
 
End Sub
 

If this still doesn't work, post your code and I'll see what else I've got wrong!!!

[shonam]

am getting the runtime error as soon as I press the command button - the text box which should prompt for the "word" doesn't appear

code is:

Private Sub viewtr_Click()

Dim dbs As DAO.Database
Dim results As DAO.Recordset
Dim stdocname As String
Dim stLinkCriteria As String

Set dbs = CurrentDb
Set results = dbs.OpenRecordset("SELECT * FROM selcour;")

If results.RecordCount = 0 Then
MsgBox ("There were no records to display")
Else
stdocname = "seltrain"
DoCmd.OpenForm stdocname, , , stLinkCriteria
End If

End Sub

the line:
Set results = dbs.OpenRecordset("SELECT * FROM selcour;")
is highlighted in the debug window

you are a very patient person - thank you

[shonam]

Hello again

I've been trawling the internet to see if anyone else has asked about this problem before and found the following code which goes on the Form Load event:


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

and it seems to work beautifully - even though I have no idea what it's doing

thank you so very much for all your help - I have learned a great deal this morning

very best wishes
Shona

[Annette Cleghorn]

Hi Shona

I cannot help you with you question, but would like to source some info from you - What you have described when opening your form seems to be exactly what I am trying to do (hence my posting on bound commands). but cannot get right.

How did you build and link the command button and the text box? - I would dearly like to do the same but have no idea how - And then maybe in my text box provide the option of a dropdown box to select from.

Can you PLEASE PLEASE tell me what you did?

Annette

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

stumped - any help greatfully received
thanks
Shona