Data not found

[dcarr]

I have developed a form with several buttons that contain underlying queries to query data in a table called tbl_Book. One of the queries searches the table for an authors name if it finds the data associated with the authors name it returns the result in a form created for that particular search. Is there any way of creating a form so that should no data based on a search be found a form display an error message or an error message is shown that reads no data found please return to main menu?
Thanks

 

[3699wat]

This code was posted earlier. Try it and let us know if it worked. You could also do a DCount to see if there are any data.

' Cancel Open if FORM has no data

Private Sub Form_Open(Cancel As Integer)
If HasRecords = False Then
Cancel = True
Exit Sub
End If
End Sub


Private Function HasRecords() As Boolean
Dim rstForm As Recordset

'see if form has any records displayed
Set rstForm = Me.RecordsetClone
HasRecords = True

If rstForm.EOF = True Then
MsgBox "Sorry, there are no records to display"
HasRecords = false
End If
rstForm.Close
set rstForm = nothing
End Function

 

[dcarr]

I have used the first piece of code you provided me with and that worked well. However, is there anywy of preventing the formopencoomand was cancelled from appearing on the screen, and also is there anyway I can prevent the form from opening and instead just display and error message? What I have used so far is displayed below:

Private Sub Form_Open(Cancel As Integer)
If HasRecords = False Then
Cancel = True
MsgBox ("No Books matching the authors surname were found"), , "Error No Books"
Exit Sub
End If
End Sub

Thanks

 

[Rich]

Private Sub Form_Open(Cancel As Integer)
On Error GoTo Macro11_Err
If (RecordsetClone.RecordCount = 0) Then
DoCmd.Close
Beep
MsgBox "There are no invoices recorded for that period.", vbInformation, "No Invoices Recorded"
End If



Macro11_Exit:
Exit Sub

Macro11_Err:
MsgBox Error$
Resume Macro11_Exit
End Sub

 

[dcarr]

Thanks rich that worked great!

 

[rpadams]

Great job, Rich -- but how does it work?

This little bit of code is exactly what I have been looking for for years. My question is -- how does it work? What is the Macro11 and the MsgBox Error$. These are totally foreign to me. I don't use macros very much (VBA instead).

Thanks again.

 

[Rich]

The Macro references are actually just error handling added by the wizard when converting the original Macro to vba, call me lazy if you like, I just haven't got around to changing it yet

 

[rpadams]

Rich - code interfers with mde

Rich. The code works great as I said, but it is causing my application to not compile / and make an mde file. I've searched many sources. One suggested changing the Error$ to "MsgBox Err.Number&""&Err.Description" The code works, but still doesn't compile. I've searched for missing references - no luck. I focusing on the "Macro11" as the problem. Do you have any ideas on how to accomplish the same thing with different code.

The actual error message I'm getting is "Compile error in hidden module (formname)" followed by "Unable to create MDE file etc."

I'd really like to include your code, but I also need to distibute my application as .mde to keep the "heroes" at my client at bay.

Any help would be greatly appreciated.

Ralph

 

[Rich]

I think your problem lies elsewhere, do you have any other error handling?
Try changing the name such as
Private Sub Form_Open(Cancel As Integer)
On Error GoTo ErrorHand_Err
If (RecordsetClone.RecordCount = 0) Then
DoCmd.Close
Beep
MsgBox "There are no invoices recorded for that period.", vbInformation, "No Invoices Recorded"
End If



ErrorHand_Exit:
Exit Sub

ErrorHand_Err:
MsgBox Error$
Resume ErrorHand_Exit
End Sub

I have had problems with Error$ in the past but it was due to the Reference problem they don't have to be listed as missing to cause problems, try searching here, I'm sure an answer has been posted for something similar

 

[rpadams]

Compile error solved

Rich --

I took your advice and went looking elsewhere for the problem. I found it in a textbox on the form that I had deleted from the form, but not the code in the module. I didn't get a runtime error because the deleted box never had any data and the extra code was ignored in the .mdb. When I tried to make the .mde, it stopped me. Guess I have to be a little more careful when I delete objects.

Thanks again -- for your time and the code.

Ralph