Full Version: I want a form to open if a query returns data
UtterAccess Forums > Microsoft® Access > Access Forms
jhayes
I want a form to open if a query returns data. If the query is blank (returns nothing), I don't want the form to open.
Can anyone Help
dannyseager
erm.... 1 way might be something like this...
If isnull(dfirst("[fieldname],[queryname]","[queryname]") then
msgbox "No results were returned"
exit sub
else
docmd.openform "formname"
end if
for the field name you would need a field that always contains data if the record exists (an ID field might be your best be)
Alan_G
Another way could be along the lines of
im db as DAO.Database
Dim rs as DAO.Recordset
set db = CurrentDb()
set rs = db.OpenRecordset("NameOfYourQueryHere")
If rs.RecordCount = 0 then
Msgbox "No Records returned"
Exit Sub
Else
Docmd.Openform "YourFormNameHere"
End If
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
dannyseager
just to ads 1 thing to Access_Addict_Newbie's post, make sure you go in to the references (tools > references in the VBA editor) and tick the Microsoft DAO 3.6 object library
jhayes
Thanks
can not work the first line "If isnull(dfirst("[fieldname],[queryname]","[queryname]") then"
Sorry if I'm being thick?
dannyseager
have you changed the fieldname and queryname to the field and query names?
Can you post a stripped down version of your DB?
jhayes
If isnull(dfirst("[autonumber],[email]","[email]") then
msgbox "No results were returned"
exit sub
else
docmd.openform "calldesk"
end if
Larry Larsen
Hi
Check out this : Post : as another option.
thumbup.gif
bekibutton
Is there any way to stop it saying "The OpenForm action has been cancelled" when there are no records? It almost looks like proper English but I think it might still scare my users... frown.gif
Larry Larsen
Hi
What are you trying to achieve when you get the error message..?
thumbup.gif
bekibutton
The form shouldn't open. I just want it to bring up an error (in English, instead of VBA speak!) to tell the user that the value they entered is incorrect. I may at some point want to reopen the form to run the query again but right now I just want it to close, without the "formOpen" message.
Larry Larsen
Ok.. Can you post up your code that your using to open the form which woll show your criteria condition on the OpenForm command..
thumbup.gif
bekibutton
CODE
If Me.RecordsetClone.RecordCount = 0 Then
        MsgBox "No records for that parameter", vbInformation + vbOKOnly, "No records"
        Cancel = True

hat's the important bit frown.gif
Larry Larsen
Hi
This is a copy from the link:
CODE
Private Sub Form_Open(Cancel As Integer)
    If Me.RecordsetClone.RecordCount = 0 Then
        MsgBox "No records for that parameter", vbInformation + vbOKOnly, "No records"
        Cancel = True
    End If
End Sub

The sequence of control is "Open" > "Load"> etc...
This process looks at forms recordset (table/query) and does a valuation on the contence of the recordset and if =(0) opt's out with a message.
Question what is the record source for your form..(it has to be either a table or query)?
thumbup.gif
bekibutton
It's a query. As I say it works great and comes up with the specified message box, I just want it not to show the FormOpen dialog. Am I being too fussy?
Larry Larsen
No.. can you grab the dialog image and post it up (i'm a little puzzled)..
thumbup.gif
bekibutton
Oooh. Just went to try and make it bring up the message box and ...it doesn't. How strange! wary.gif guess I don't need to fix it now. Thanks for your help anyway frown.gif
Larry Larsen
Hi
Your welcome..
thumbup.gif
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.