Button on a Form Linked to a Query 1

[philthepowerhouse]

Hi,

Is it possible to create some code in order to have a message box appear should there be no records to display once selecting a button which brings up a search box which will run a query? If there are no results at the moment it just displays a empty form.

Many thanks for your help in advance and if you need more info just shout.

Powerhouse

 

[ZmrAbdulla]

What happened to the reply in thread
Access - Showing a message when there is no data available
thread702-1098371


________________________________________________________________________
Zameer Abdulla
Visit Me
By the time a man realizes that may be his father was right,
he usually has a son who says, "Father, you are wrong!".

 

[philthepowerhouse]

Mr Abdulla,

Lovely proverb by the way!

I wasn't sure if that code would work for the query in the same way as before it was taking information from a combo box, this time the button is taking information from a search box brought up by the query eg: [Enter Search Word] found in the criteria section under the title heading.

Hope this makes sense,

You'll never walk alone, unless you are actually on your own!!

Powerhouse

 

[ZmrAbdulla]

Thanks for the compliments.

It is same for both; It depends how do you bring the query up.
You said a search box appear first. Is that "Enter Parameter" box? If yes
You could use

Private Sub Form_Open(Cancel As Integer)
    If Me.RecordsetClone.RecordCount = 0 Then
        MsgBox "No Record to Show, Cancelled Operation"
        Cancel = True
    End If
End Sub

________________________________________________________________________
Zameer Abdulla
Visit Me
By the time a man realizes that may be his father was right,
he usually has a son who says, "Father, you are wrong!".

 

[philthepowerhouse]

The button opens a form which is linked to a query which in the query design view, criteria row under the title heading has the following:

Like "*" & [Enter a word from the title you wish to locate] & "*"

From doing this a box appears when you open the form which the query is linked to in order to type a search word into.

Its from this point that i am not sure if your code will work??? Becasuse if there are no matches a blank form appears and I want a message to say there are no records to appear instead.

Many Thanks for your patience and help with my query!!

Powerhouse

 

[ZmrAbdulla]

[ol]
[li] Create a small modal form (frmSrchCriteria)with a text box (txtSearch) and two command buttons(cmdClose,cmdOK) [/li]
[li] cmdClose to close the form [/li]

DoCmd.Close

[li] In the query criteria add Like "*" & Forms!frmSrchCriteria.txtSearch & "*" [/li]
[li] cmdOK to open the form with results [/li]

Private Sub cmdOK_Click()
    If DCount("*", "QueryName") = 0 Then
        MsgBox "No Data"
    Else
        DoCmd.OpenForm "FormName"
    End If
End Sub

[/ol]

________________________________________________________________________
Zameer Abdulla
Visit Me
By the time a man realizes that may be his father was right,
he usually has a son who says, "Father, you are wrong!".

 

[philthepowerhouse]

Thank you it works perfectly,

Once again you have sorted me out adn I thank you very much.

Many thanks,

Powerhouse

 

[philthepowerhouse]

MR Abdulla,

I have been playing around with my forms and come across an annoying problem, after the user presses OK on the frmSrchCriteria form you created for me, the user is taken to a results page, On this page I have another button which simply opens the frmSrchCriteria form again to search for another title in case the user makes a mistake, the frmSrchCriteria appears but it does not let you press OK and it will not proceed any further!! Any ideas?

As well as this everytime I go back to this form it seems to maximise the frmSrchCriteria form, is there a way of keeping it at a sensible message box size, I have tried creating a Restore macro and placing on the, 'On Activate, On open etc of frmSrchCriteria, but no joy.

I'm sure you know the simple answers.

Thank you again though,

Powerhouse

 

[ZmrAbdulla]

Change OK button click to

Private Sub cmdOK_Click()
    If DCount("*", "QueryName") = 0 Then
        MsgBox "No Data"
        Me.txtSearch.SetFocus
    Else
        DoCmd.OpenForm "FormName"
        DoCmd.Close acForm, "FrmSrchCriteria"
    End If
End Sub

Set the BorderStyle of the FrmSrchCriteria =Dialog
AutoResize=No

to keep the form not to resize.

________________________________________________________________________
Zameer Abdulla
Visit Me
By the time a man realizes that may be his father was right,
he usually has a son who says, "Father, you are wrong!".