Stop Form Opening if their is No Data in its Recordsource?? 2

[Shokoya]

Hi Guys,

Would appreciate any advise for the following scenario;

I have a form (Form1) that gets its recordsource from a Query (Query1). If Query1 returns no data, Form1 opens blank.
What I am trying to do is stop Form1 opening if their is No Data in Query1...I know this can be donw through the NoData property of a report, but can not quite figure out how it would be done on a form....pls instruct!

[Cheers]

 

[nice95gle]

Put the following code in the form open Event

You have to add
"Microsoft ActiveX Data Objects 2.7 Library" to the references

    Dim myQuery As New ADODB.Recordset

myQuery.Open "YOUR QUERY NAME",CurrentProject.Connection, adOpenKeyset

If myQuery.RecordCount = 0 Then
  Cancel = 1
End If

 

[Shokoya]

Hi Nice,

Thanks for that....have carried out as instructed in ur thread, but the form still seems to open...used the following code;

Dim myQuery As New ADODB.Recordset

myQuery.Open "Enquiry Orders", CurrentProject.Connection, adOpenKeyset

If myQuery.RecordCount = 0 Then
Cancel = 1
End If

Added 2.7 to libray, but had to remove 2.6 because of conflict. Pls instruct

Thank again

 

[nice95gle]

Make sure the bookmark property for myQuery is not returning an error.

If the Bookmark property is returning an error chances are you are not seeing the query correctly.

add this
MsgBox (myQuery.Bookmark)

right before "If myQuery.RecordCount = 0 Then"
and see if it is generaating an error

 

[Shokoya]

Thanks again Nice....

Tried ur surgestion...the MsgBox opens with the number '512', after which the blank form is opened.

Tried to change query name to 'Enquiry_Orders' but get following error message

'Invalid SQL Statement; expected 'DELETE','INSERT','PROCEDURE','SELECT', or 'UPDATE' in regards to

myQuery.Open "Enquiry Orders", CurrentProject.Connection, adOpenKeyset line of code.

Thanx again!

 

[nice95gle]

Last thing to check is the recordcount.

replace MsgBox (myQuery.Bookmark)

with MsgBox (myQuery.Recordcount)

this should return 0 if this is not, check your query.

sorry for all of this but I am try to narrow in on the problem.

 

[mgolla]

Try changing this:
If myQuery.RecordCount = 0 Then
Cancel = 1
End If

To this:
If myQuery.RecordCount = 0 Then
DoCmd.Close
End If

HTH
Mike

 

[Shokoya]

Hi Guys,

Nice - tried ur suggestion...the MsgBox returns '4', even though the underlying query actually returns 'no records' (I re-checked this). The query consist of 3 fields;

Enquiry_Id - criteria is et to return only the records that relate to a specific Enquiry

Count_of_Order - Expression that counts the Number of Orders associated with the Enquiry.

Order Value - Sum of all the Values associated with an Enquiry.

Mgolla - made the change you suggested...the blank form still opened.

Thanks guys....

 

[nice95gle]

Yeah I figured something was wrong. Seeing that the msgbox is telling you there is 4 records something is wierd somewhere. What I would do is recreate the query (Don't copy and paste it; recreate it)and run my code against the new one and see if you still get 4 records . In addition to that try repairing and compacting your DB to clear out any records that may have been deleted and free up some space.

Just a note: If you can get pass this-> "If myQuery.RecordCount = 0 Then"
...
End if

then anything you put in between the open IF and the END IF will not work, because the Recordcount is NOT returning 0. The form will always open.

Hope this helps

 

[danvlas]

Private Sub Form_Open(Cancel As Integer)
Cancel = Me.RecordsetClone.RecordCount = 0
End Sub

Keep in mind this will raise error 2501 in the calling code, so you should trap it somehow:

On Error Resume Next
DoCmd.OpenForm "YourForm"
If Err.Number = 2501 Then
   Beep
   MsgBox "Report opening canceled. Probably no data"
End If

Good luck




Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[Shokoya]

Thanx Danvlas...that code did the trick. Posting u a star.

Also posting u a star Nice, for sticking with me on this one.

again guys

 

[jbento]

Great stuff!!!

Jerome Benton
JERPAT Web Designs

http://www.jerpat.org

GOD Is Good All The Time!!!