By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,397 Members | 1,425 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,397 IT Pros & Developers. It's quick & easy.

form based on a query

P: 19
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
Sep 11 '07 #1
Share this Question
Share on Google+
18 Replies


P: 35
I had this problem, what you can do is
Expand|Select|Wrap|Line Numbers
  1. dim dbs as dao.database
  2. dim results as dao.recordset
  3. set dbs = currentdb
  4. set results = dbs.OpenRecordset("SELECT * FROM query;")
  5. if results.recordcount = 0 then
  6.     msgbox("There were no records to display")
  7. else
  8.     'open form
  9. end if
  10.  
where query is the query that you form is based on.
If you put this in the Form_Load() sub it should work.
Sep 11 '07 #2

P: 35
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;"
Sep 11 '07 #3

P: 19
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
Sep 11 '07 #4

P: 35
Can you do me a favour and post the whole sub in and I'll see if I can find the problem.
Sep 11 '07 #5

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

I think I know what the problem is -

Expand|Select|Wrap|Line Numbers
  1.     Dim dbs As DAO.Database
  2.     Dim results As DAO.Recordset
  3.  
rather than
Expand|Select|Wrap|Line Numbers
  1.     Dim dbs As DOA.Database
  2.     Dim results As DOA.Recordset
  3.  
My mistake
Sep 11 '07 #6

P: 19
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
Sep 11 '07 #7

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

Expand|Select|Wrap|Line Numbers
  1. If results.recordcount = 0 Then
  2.    MsgBox ("There were no records to display")
  3. Else
  4.    'open form
  5. End If
  6.  
Sep 11 '07 #8

P: 19
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
Sep 11 '07 #9

P: 35
does it display the "There were no records to display" message or doesn't it get that far?
Sep 11 '07 #10

P: 19
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
Sep 11 '07 #11

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

Expand|Select|Wrap|Line Numbers
  1. Private Sub command_button_click()
  2. Dim dbs As DAO.Database
  3. Dim results As DAO.Recordset
  4. Set dbs = CurrentDb
  5. Set results = dbs.OpenRecordset("SELECT * FROM selcour;")
  6. If results.recordcount = 0 Then
  7. MsgBox ("There were no records to display")
  8. Else
  9. 'open form
  10. End If
  11.  
This means that if there are no records to display it won't open the form. Alternatively, you can
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Load()
  2. Dim dbs As DAO.Database
  3. Dim results As DAO.Recordset
  4. Set dbs = CurrentDb
  5. Set results = dbs.OpenRecordset("SELECT * FROM selcour;")
  6. If results.recordcount = 0 Then
  7. MsgBox ("There were no records to display")
  8. docmd.close
  9. End If
  10.  
which should close the form if there are no reocrds to display.
Sep 11 '07 #12

P: 19
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
Sep 11 '07 #13

P: 35
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.
Sep 11 '07 #14

P: 19
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
Sep 11 '07 #15

P: 35
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

Expand|Select|Wrap|Line Numbers
  1. Private Sub command_button_click()
  2.  
  3. Dim dbs As DAO.Database
  4. Dim results As DAO.Recordset
  5. dim stdocname as string
  6. dim Dim stLinkCriteria As String
  7.  
  8. Set dbs = CurrentDb
  9. Set results = dbs.OpenRecordset("SELECT * FROM selcour;")
  10.  
  11. If results.recordcount = 0 Then
  12.    MsgBox ("There were no records to display")
  13. Else
  14.    stDocName = "formname"
  15.    DoCmd.OpenForm stDocName, , , stLinkCriteria
  16. End If
  17.  
  18. End Sub
  19.  
If this still doesn't work, post your code and I'll see what else I've got wrong!!!
Sep 11 '07 #16

P: 19
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
Sep 11 '07 #17

P: 19
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
Sep 11 '07 #18

P: 3
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
Sep 12 '07 #19

Post your reply

Sign in to post your reply or Sign up for a free account.